 Print-friendly Version
PostgreSQL in the OpenSolaris OSZdenek Kotala, July 2007 Abstract: This article describes key features of PostgreSQL 8.2, which have been available in OpenSolaris since build 66. Contents
About PostgreSQLPostgreSQL is an open source relational database with a long history of development. It is a direct successor of the Ingres database, which is "mother" of many commercial databases (for example, Sybase). Both Ingres (started in early 1970s) and Postgres (started in 1986) were developed as research projects at University of California, Berkeley and were led by professor Michael Stonebracker. The Postgres project officially ended after release 4 in 1993. Fortunately, the BSD license allowed a resumption of the development work on Postgres. In 1994, two U.C. Berkeley graduate students, Andrew Yu and Jolly Chen, replaced the old QUEL system with a new SQL interpreter and they released Postgres95. A new era started in 1996 when the complete source code was released on the Internet with a newly established CVS repository, and the project was named as PostgreSQL. During 10 years of Internet community development, PostgreSQL was pushed forward from version 6.0 to 8.2, and many new features and improvements were added. Today, PostgreSQL is one of the most advanced open source databases with the following key features:
PostgreSQL also supports many procedural languages, including the Java programming language, Perl, Python, Ruby, Tool Command Language (Tcl), C/C++ and PgPL/SQL, which is similar to Oracle's PL/SQL language. In addition, many library interfaces are available for languages such as Java (Java Database Connectivity or JDBC interface), Perl, Python, Ruby, C, C++, and PHP. There is also an implementation of the Open DataBase Connectivity (ODBC) interface. Integration With the Solaris OSSun Microsystems announced support of PostgreSQL in November 2005. The first integrated version of PostgreSQL was 8.1.3, and it was shipped with the Solaris 10 6/06 OS. A few weeks before, it was also integrated with the OpenSolaris OS. In the Solaris OS, PostgreSQL is shipped with the JDBC driver and Tcl library. PostgreSQL 8.2 was released in December 2006. Since build 56 of the OpenSolaris OS, PostgreSQL has been integrated with other Solaris features, such as Service Management Facility (SMF), dynamic tracing (DTrace), and Role Based Access Control (RBAC). The SMF manifest also supports version 8.1. PostgreSQL is divided into several packages. The following list contains the packages related to version 8.1. (The packages that contain the source code are not in the list.)
PostgreSQL is compiled with the following features:
The following options are passed to the configuration script: --enable-nls --with-tcl --with-perl --with-python --with-pam --with-openssl --with-krb5 --enable-thread-safety All features excluding Kerberos v5 are present in the Solaris 10 OS and the OpenSolaris OS. The Solaris 10 OS is scheduled to support Kerberos v5 in an upcoming update. The packages install files into the commonly used directories, such as Currently, there are a couple of other integration issues. The 8.1 packages have some misplaced files. For example, the time zone files are stored in the client package instead of the server package. There is a problem with patches also, because the patches grow rapidly in size. The name of the documentation directory contains a minor version number, so each patch must be delivered with the complete set of documentation. In PostgreSQL 8.2, many issues have been fixed and integrated with Solaris features. The following list shows the PostgreSQL 8.2 packages:
These packages store all the files in the Note: PostgreSQL 8.1 uses its own timezone files, and it is strongly recommended to update to the latest version to keep timezone files actual. PostgreSQL 8.2 uses the timezone files of the Solaris OS (this is different from the PostgreSQL community release). SMF is the replacement of the old PostgreSQL uses SMF to manage the PostgreSQL server life similar to many other Solaris services. The
If the package is successfully installed and the manifests are imported, the following services related to PostgreSQL are available: bash-3.00$ svcs -a | grep postgresql disabled 8:45:32 svc:/application/database/postgresql:version_81 disabled 8:45:32 svc:/application/database/postgresql:version_82 SMF offers two pre-configured instances of PostgreSQL. One is for PostgreSQL version 8.1 and a second is for version 8.2.
They are both disabled by default. The first task of the PostgreSQL administrator is to create an appropriate database cluster
using the bash-3.00$ svccfg svc:> select postgresql:version_82 svc:/application/database/postgresql:version_82> listprop postgresql application postgresql/bin astring /usr/postgres/8.2/bin postgresql/data astring /var/postgres/8.2/data postgresql/log astring server.log postgresql/value_authorization astring solaris.smf.value.postgres Once the database cluster is created, you can start the PostgreSQL server using the following command: svcadm enable postgresql:version_82 Verify the status using the following command: bash-3.00$ svcs -x postgresql:version_82 svc:/application/database/postgresql:version_82 (PostgreSQL RDBMS) State: online since Thu Feb 10 09:05:20 2007 See: postgres_82(1) See: http://postgresql.org See: /var/svc/log/application-database-postgresql:version_82.log Impact: None. If the database cluster is not initialized or any other problem occurs during server startup, the following message appears: bash-3.00$ svcadm enable postgresql:version_81 bash-3.00$ svcs -x postgresql:version_81 svc:/application/database/postgresql:version_81 (PostgreSQL RDBMS) State: maintenance since Thu Feb 15 12:01:10 2007 Reason: Start method exited with $SMF_EXIT_ERR_CONFIG. See: http://sun.com/msg/SMF-8000-KS See: postgres_82(1) See: http://postgresql.org See: /var/svc/log/application-database-postgresql:version_81.log Impact: This service is not running. In addition, the log file contains an appropriate error message:
[ Feb 10 12:01:09 Enabled. ]
[ Feb 10 12:01:09 Executing start method
("/lib/svc/method/postgresql start") ]
postgresql/data directory /var/lib/pgsql/data is not
a valid PostgreSQL data directory
postgresql/data directory /var/lib/pgsql/data may not have
been initialized
To initialize the postgresql/data directory /var/lib/pgsql/data
run 'initdb /var/lib/pgsql/data'
[ Feb 10 12:01:10 Method "start" exited with status 96 ]
RBAC was introduced in the Solaris 8 OS. Simply put, RBAC is a colorized view on the classic black and white UNIX security model. RBAC enables an
administrator to give some superprivilege rights to non-root users. This mechanism is implemented in the Solaris kernel, and now it is possible to see
more than 60 special privileges supported by the kernel (see RBAC uses roles and profiles to set up user permissions. Since OpenSolaris build 56, PostgreSQL integration has used RBAC to allow more users to administer the database without sharing the postgres user password (excluding some special cases). This support is planned for an upcoming update of the Solaris 10 OS. The Postgres Administration profile is defined as follows: prof_attr:Postgres Administration:::: auths=solaris.smf.manage.postgres,solaris.smf.value.postgres Users with this profile are able to use and configure Postgres SMF. However, for complete administration, the administrator
should be able to run some special commands, as a user who has the Postgres Administration:solaris:cmd::: /usr/postgres/8.2/bin/initdb:uid=postgres Postgres Administration:solaris:cmd::: /usr/postgres/8.2/bin/ipcclean:uid=postgres Postgres Administration:solaris:cmd::: /usr/postgres/8.2/bin/pg_controldata:uid=postgres Postgres Administration:solaris:cmd::: /usr/postgres/8.2/bin/pg_ctl:uid=postgres Postgres Administration:solaris:cmd::: /usr/postgres/8.2/bin/pg_resetxlog:uid=postgres Postgres Administration:solaris:cmd::: /usr/postgres/8.2/bin/postgres:uid=postgres Postgres Administration:solaris:cmd::: /usr/postgres/8.2/bin/postmaster:uid=postgres As mentioned earlier, the Solaris OS adds the new postgres:x:90:90:PostgreSQL Reserved UID:/:/usr/bin/pfksh This user is set up as postgres::::type=role;profiles=Postgres Administration,All This user/role is the owner of all PostgreSQL processes and the data directory. Only the users who have been assigned
this role are allowed to assume this role by the Initial SetupThe following steps describe how to perform the initial setup: 1. Set up the password for the To access the Postgres files, it is necessary to set up the password for -bash-3.00# passwd postgres New Password: Re-enter new Password: passwd: password successfully changed for postgres 2. Add the role and profile to database administrators: At least one user must have been assigned the role -bash-3.00# usermod -P "Postgres Administration" -R postgres zdenek 3. Initialize, configure, and start the PostgreSQL server: This last step can be performed by any user with role bash-3.00$ pfexec /usr/postgres/8.2/bin/initdb /var/postgres/8.2/data bash-3.00$ su - postgres Password: $ vi /var/postgres/8.2/data/postgresql.conf bash-3.00$ svcadm enable postgresql:version_82 DTraceDTrace is a dynamic tracing framework that helps developers and systems administrators trace and analyze the Solaris kernel and user-space applications. It is also very useful for performance tuning. PostgreSQL 8.2 also connects to many applications that contain DTrace probes. The following probes are integrated inside the PostgreSQL server: MODULE FUNCTION NAME postgres LockAcquire lock-endwait postgres LockAcquire lock-startwait postgres LWLockAcquire lwlock-acquire postgres LWLockConditionalAcquire lwlock-condacquire postgres LWLockConditionalAcquire lwlock-condacquire-fail postgres LWLockAcquire lwlock-endwait postgres LWLockRelease lwlock-release postgres LWLockAcquire lwlock-startwait postgres AbortTransaction transaction-abort postgres CommitTransaction transaction-commit postgres StartTransaction transaction-start These probes might be very helpful for locating performance bottlenecks in applications. Robert Lor, who integrated DTrace into PostgreSQL, also prepared a group of useful scripts. For example, the following script shows information about transaction processing time:
#!/usr/sbin/dtrace -qs
/*
* Usage : txn_time.d <pid>
* Description: Measure processing time for each transaction
* Author : Robert Lor
*/
postgresql*:::transaction-start
{
self->ts= timestamp;
}
postgresql*:::transaction-commit
/self->ts/
{
@commit[pid, arg0] = sum(timestamp - self->ts);
self->ts=0;
}
postgresql*:::transaction-abort
/self->ts/
{
@abort[pid, arg0] = sum(timestamp - self->ts);
self->ts=0;
}
tick-1msec
{
trunc(@commit, 20);
trunc(@abort, 20);
}
Note: The Abort and Commit transaction probes are local (static) functions. The Solaris 10 11/06 release, also known as Update 3, and earlier versions do not support these kind of probes, and the PostgreSQL compilation with DTrace is not possible without a function definition change
(remove the keyword Solaris Cluster Software (Formerly Sun Cluster Software)High availability is very important for many database applications. Solaris Cluster 3.2 software provides data services for PostgreSQL. PostgreSQL can be run only as a failover service. Running it as a multiple master or a scalable service is not possible, because PostgreSQL cannot be run on multiple nodes in parallel. One possible configuration for the web service using Apache web server and PostgreSQL is explained as follows. PostgreSQL is configured as the back end on one node. Both nodes have access to shared disks, and each node has exclusive locks on its physical disk. PostgreSQL runs on one of the nodes and Apache runs on the second node. If either nodes fails, the other node takes over the responsibility of running the services and getting the disk access also. TuningMany common tuning parameters and useful settings are covered in the PostgreSQL documentation. There are only a few parameters that
are related to the Solaris OS. The Solaris System V IPC resources setting is enough, and it is not necessary to change the default settings.
PostgreSQL, like other databases, generates heavy I/O load and uses synchronous writes. The best option for ResourcesThe following resources contain useful information:
Additional Related Links
Unless otherwise licensed, code in all technical manuals herein (including articles, FAQs, samples) is provided under this License. |
 |
 | |