Administering PostgreSQL on Solaris

Upgrading From Older Version Of PostgreSQL

Managing PostgreSQL Services on Solaris

PostgreSQL and SMF integration

PostgreSQL has been integrated with SMF in Solaris Express Developer Edition (SXDE)and Solaris 10 8/07 or later. If you are using Solaris 10 11/06 or earlier and want to SMF enable PostgreSQL, see refer to this How-to Guide

As of Solaris 10 8/07 and SXDE 1/08, Postgres 8.1 and 8.2 are shipped with the operating system, and there are two corresponding SMF services called postgresql:version_81 and postgresql:version_82 respectively. These services can be configured to use any directories for executables and data, but by default these properties are set to:

• Postgres 8.1:
  • Executable = /usr/bin
  • Data = /var/lib/pgsql/data

• Postgres 8.2
  • Executable = /usr/postgres/8.2/bin
  • Data = /var/postgres/8.2/data

For instructions on how to change the above properties, refer to the next section.

Running/Configuring PostgreSQL using SMF Commands

PostgreSQL can easily be started using the SMF command after Solaris is installed. By default, the services are disabled. To see the current service status, run svcs postgresql from the command prompt.

To start Postgres, follow the steps below:

PostgreSQL 8.1:

1. As root, make sure the default data directory is owned by the user postgres and su to postgres:
   # chown postgres:postgres /var/lib/pgsql/data
   # su - postgres
2. Create PostgreSQL DB cluster:
   $ /usr/bin/initdb -D /var/lib/pgsql/data
3. As root, use the SMF's svcadm command to start PostgreSQL:
   # /usr/sbin/svcadm enable postgresql:version_81

PostgreSQL 8.2:

1. As root, su to postgres:
   # su - postgres
2. Create PostgreSQL DB cluster:
   $ /usr/postgres/8.2/bin/initdb -D /var/postgres/8.2/data
3. As root, use the SMF's svcadm command to start PostgreSQL:
   # /usr/sbin/svcadm enable postgresql:version_82

The binaries for PostgreSQL 8.1 and 8.2 are located in /usr/bin and /usr/postgres/8.2/bin, respectively. To use 8.2, make sure to add /usr/postgres/8.2/bin to PATH.

It is possible to run both PostgreSQL 8.1 and 8.2 servers at the same time as long as the port numbers are different.

The data directory used above are the defaults, but you can put the database anywhere. To change the SMF property for data directory, do the followings:

1. Display the current properties
   # svcprop -p postgresql version_82
2. Change the value of the data property
   # svccfg -s postgresql:version_82 setprop postgresql/data="/pgdata"
   # svcadm refresh postgresql:version_82
3. Check to make the new value is correct
   # svcprop -p postgresql version_82

To change the property for the Postgres executables, you can follow the same steps above except use the postgresql/bin property name instead.

See postgres_82 man page (e.g. run "man postgres_82" from the command prompt) for more information.

Running PostgreSQL in Solaris Containers

A Solaris Container is the combination of a Solaris Zone and Resource Management facility. Sometimes the terms 'Container' and 'Zone' are used interchangeably. For more info, check out the Zones Community on OpenSolaris.org.

Setting up Solaris Container to run Postgres is quite simple, but before delving into the specifics, there are several options to consider:

1. Install PostgreSQL in the global zone and run different PostgreSQL instances in different sparse root zones. The PostgreSQL binary will be shared by all zones, but not the data. This method will simplify PostgreSQL upgrade as all zones will automatically see the same binary.
2. Install PostgreSQL in a whole root zone so different zones will have their own binary. In this approach, you can run different versions of PostgreSQL in different zones.
3. A combination of 1 & 2. Install PostgreSQL in the global zone, create some sparse root zones to run some instances of PostgreSQL using the shared binary, and create some whole root zones to run their own copy of PostgreSQL.

The above scenarios apply only if Solaris packages are used. If you build your own Postgres binary, you can still run different versions even in sparse root zones by just placing the binary in the private filesystem.

This blog entry has a step by step example demonstrating the first approach, with PostgreSQL binary installed in the global zone and using a sparse root zone to run Postgres. By default, the installed Postgres binary is shared with all zones.