This appendix presents an example of how to install and configure the PostgreSQL application and data service in the global zone using WAL file shipping as a replacement for shared storage. It is a two-node cluster configuration. If you need to install the application in any other configuration, refer to the general-purpose procedures given in other sections of this manual. For information about PostgreSQL WAL file shipping installation in a non-global zone, see the notes in this document.
This example uses a two-node cluster with the following node names:
phys-schost-1 (a physical node) or zone 1 on phys-schost-1
phys-schost-2 (a physical node) or zone 2 on phys-schost-2
This configuration also uses the logical host name ha-host-1.
This deployment example uses the following software products and versions:
Solaris 10 6/06 OS for SPARC or x86 platforms
Sun Cluster 3.2 core software
Sun Cluster Data Service for PostgreSQL
PostgreSQL version 8.3.1 source files
readline utility
gmake utility
Your preferred text editor
Your preferred C compiler
This example assumes that you have already installed and established your cluster. It illustrates installation and configuration of the data service application only.
The steps for installing PostgreSQL in a cluster that runs on Solaris 9 OS are identical to the steps given in this example.
The instructions in this example make the following assumptions:
Shell environment. All commands and the environment setup given in this example are for the Korn shell environment. If you use a different shell, replace any Korn shell-specific information or instructions with the appropriate information for your preferred shell environment.
User login. Unless otherwise specified, perform all procedures as superuser or assume a role that provides solaris.cluster.admin, solaris.cluster.modify, and solaris.cluster.read RBAC authorizations.
The tasks you must perform to install and configure PostgreSQL in the global zone are as follows:
Example: Building and Installing the PostgreSQL Software on Shared Storage
Example: Enabling the PostgreSQL Software to Run in the Cluster
Install and configure cluster as instructed in Sun Cluster Software Installation Guide for Solaris OS.
Install the following cluster software components on both nodes:
Sun Cluster core software
Sun Cluster data service for PostgreSQL
Install the following utility software on both nodes:
readline utility
rsync utility
gmake utility
Your C compiler
Beginning from the node that owns the file system, add the postgres users.
phys-schost-1# groupadd -g 1000 postgres |
phys-schost-2# groupadd -g 1000 postgres |
phys-schost-1# useradd -g 1000 -d /global/mnt3/postgres -m -s /bin/ksh pgs |
phys-schost-2# useradd -g 1000 -d /global/mnt3/postgres -m -s /bin/ksh pgs |
For a local zone, perform the steps in the local zones on each node.
Ensure that the PostgreSQL users can login to each other's profile using ssh without a password prompt.
Register the necessary data types on both nodes.
phys-schost-1# clresourcetype register SUNW.gds |
Create the PostgreSQL resource group.
phys-schost-1# clresourcegroup create -n phys-schost-1 POSTGRES-PRIM-RG |
phys-schost-1# clresourcegroup create -n phys-schost-2 POSTGRES-STA-RG |
phys-schost-1# clresourcegroup create - ROLECHG-RG |
phys-schost-1# clresourcegroup create set -p \ Auto_start_on_new_cluster=false ROLECHG-RG |
Create the logical host.
phys-schost-1# clreslogicalhostname create -g ROLECHG-RG ha-host-1 |
Enable the resource groups.
phys-schost-1# clresourcegroup online -eM ROLECHG-RG |
phys-schost-1# clresourcegroup online -M POSTGRES-PRIM-RG |
phys-schost-1# clresourcegroup online -M POSTGRES-STA-RG |
Modify the PGROOT and PD_LIBRARY_PATH environment variables according to the needs of your build.
The databases are stored under /postgres/data. The log file is stored under /postgres/logs/sclog.
phys-schost-1# PGROOT=/postgres/postresql-8.3.1 |
phys-schost-1# LD_LIBRARY_PATH=/postgres/postgresql-8.3.1:/usr/sfw/lib: \ /usr/local/lib:/usr/lib:/opt/csw/lib |
phys-schost-1# export PGROOT |
phys-schost-1# export LD_LIBRARY_PATH |
If you are installing the software in the default directory, set PGROOT to /usr/local/pgsql and LD_LIBRARY_PATH to /usr/local/pgsql/lib:/usr/sfw/lib:/usr/local/lib:/usr/lib:/opt/csw/lib.
Copy the PostgreSQL configuration files from the agent directory to its deployment location.
phys-schost-1# cp /opt/SUNWscPostgreSQL/util/pgs_config /postgres/pgs_config_pri |
phys-schost-1# cp /opt/SUNWscPostgreSQL/rolechg/util/rolchg_config /postgres \ /rolechg_config |
phys-schost-2# cp /opt/SUNWscPostgreSQL/util/pgs_config /postgres/pgs_config_sta |
Add the cluster information to the configuration files.
The following list shows the relevant file entries for pgs_config_pri and the values to assign to each entry.
RS=PRIM-RS RG=POSTGRES-PRIM-RG PORT=5432 LH= HAS_RS= PFILE=PRIM-RS-pfile USER=pgs PGROOT=/usr/local/pgsql PGROOT=/postgres/postgresql-8.3.1 PGPORT=5432 PGHOST=pgsql-port PGLOGFILE=/postgres/logs/sclog # LD_LIBRARY_PATH=/usr/local/pgsql/lib:/usr/sfw/lib:/usr/local/lib:/usr/lib:/opt/csw/lib LD_LIBRARY_PATH=/postgres/postgresql-8.3.1/lib:/usr/sfw/lib/opt/csw/lib STDBY_RS=STA-RS STDBY_RG=POSTGRES-STA-RG STDBY_USER=pgs STDBY_HOST=phys-schost-2 STDBY_PARFILE=/postgres/STA-RS-pfile STDBY_PING= ROLECHG_RS= SSH_PASSDIR=
For a local zone installation, use zone 2 as the zone name in the variable STDBY_HOST instead of phys-schost-2.
The following list shows the relevant file entries for pgs_config_sta and the values to assign to each entry.
RS=STA-RS RG=POSTGRES-STA-RG PORT=5432 LH= HAS_RS= PFILE=postgres/STA-RS-pfile USER=pgs PGROOT=/usr/local/pgsql PGROOT=/postgres/postgresql-8.3.1 PGPORT=5432 PGLOGFILE=/postgres/logs/sclog # LD_LIBRARY_PATH=/usr/local/pgsql/lib:/usr/sfw/lib:/usr/local/lib:/usr/lib:/opt/csw/lib LD_LIBRARY_PATH=/postgres/postgresql-8.3.1/lib:/usr/sfw/lib/opt/csw/lib STDBY_RS= STDBY_RG= STDBY_USER= STDBY_HOST= STDBY_PARFILE= STDBY_PING= ROLECHG_RS=ROLECHG-RS SSH_PASSDIR=
The following listing shows the relevant file entries for rolechg_config and the values you need to assign to each entry.
RS=ROLECHG-RS RG=ROLECHG-RG PORT=5432 LH=ha-host1 FILE=postgres/STA-RS-pfile HAS_RS= STDBY_RS=STA-RS PRI_RS=PRIM-RS STDBY_HOST=phys-schost-2 STDBY_PFILE=/postgres/STA-RS-pfile TRIGGER=/postgres/data/failover WAIT=20
For a local zone installation, use the zone name zone 2 in the variable STDBY_HOST instead of phys-schost-2.
Save and close the files.
These steps illustrate how to install the PostgreSQL software. You can build and install the PostgreSQL binaries in the default directory /usr/local/pgsql. Perform the following steps on both hosts.
Log in as the PostgreSQL user to the target environment, either in the global or the local zone according to your installation.
phys-schost-1# su - postgres |
Set up the build environment by performing the following steps.
Create a build directory.
phys-schost-1$ mkdir build |
phys-schost-1$ cd build |
Add the C compiler to your PATH and set the LD_LIBRARY_PATH.
phys-schost-1$ PATH=$PATH:/usr/local/bin:/usr/sfw/bin:/usr/ccs/bin |
phys-schost-1$ LD_LIBRARY_PATH=/postgres/postgresql-8.3.1: \ /usr/sfw/lib:/usr/local/lib:/usr/lib:/opt/csw/lib |
phys-schost-1$ export PATH LD_LIBRARY_PATH |
Install the source and configure the build.
phys-schost-1$ gzcat /tmp/postgresql-8.3.1.tag.gz | tar xvf |
phys-schost-1$ cd /postgres/build/postgresql-8.3.1 |
phys-schost-1$ ./configure --prefix=/postgres/postgresql-8.3.1 |
Build the PostgreSQL binaries.
phys-schost-1$ gmake |
Run the PostgreSQL regression tests.
phys-schost-1$ gmake check |
Install the PostgreSQL binaries.
phys-schost-1$ gmake install |
Install the utilities, including pg_standby.
phys-schost-1$ cd contrib |
phys-schost-1$ gmake install |
phys-schost-1$ cd .. |
Clean the distribution.
phys-schost-1$ gmake clean |
Create the directories for the databases, WAL archives, configurations, utilities, and the log file.
Perform the following steps in your target environment either in the global or in the local zone unless it is specified otherwise.
phys-schost-1$ mkdir /postgres/data |
phys-schost-1$ mkdir /postgres/logs |
phys-schost-1$ mkdir /postgres/83_walarchives |
phys-schost-2$ mkdir /postgres/data |
phys-schost-2$ mkdir /postgres/utilities |
phys-schost-2$ mkdir /postgres/log |
phys-schost-2$ mkdir /postgres/83_walarchives |
Change to the PostgreSQL root directory and initialize the data cluster.
phys-schost-1$ cd /postgres/postgresql-8.3.1 |
phys-schost-1$ ./bin/initdb -D /postgres/data |
phys-schost-2$ cd /postgres/postgresql-8.3.1 |
phys-schost-2$ ./bin/initdb -D /postgres/data |
Start the database.
phys-schost-1$ ./bin/postmaster -D /postgres/postgresql-8.3.1 |
Prepare the Sun Cluster specific test database.
If you are in a local zone, ensure that you have access to a copy of your configuration file.
phys-schost-1$ ksh /opt/SUNWscPostgreSQL/util/pgs_db_prep -f /postgres/pgs_config_pri |
Stop the postmaster.
phys-schost-1$ ./bin/pg_ctl -D /postgres/data stop |
Copy the PGDATA directory to the standby.
phys-schost-1$ cd /postgres |
phys-schost-1$ /usr/local/bin/rysync -arv ./data phys-schost-2:/postgres |
If your target environment is in a zone, use zone 2 instead of phys-schost-2.
Protect the PostgreSQL configuration files.
The PostgreSQL configuration files are overwritten during resilvering. You need to move the configuration files to prevent them from being overwritten.
phys-schost-1$ cd /postgres |
phys-schost-1$ mkdir config |
phys-schost-1$ cd data |
phys-schost-1$ mv postgresql.conf ../config |
phys-schost-1$ ln -s ../config/postgresql.conf ./postgresql.conf |
phys-schost-1$ touch ../config/recovery.conf |
phys-schost-1$ ln -s ../config/recovery.conf ./recovery.done |
phys-schost-2$ cd /postgres |
phys-schost-2$ mkdir config |
phys-schost-2$ cd data |
phys-schost-2$ mv postgresql.conf ../config |
phys-schost-2$ ln -s ../config/postgresql.conf ./postgresql.conf |
phys-schost-2$ touch ../config/recovery.conf |
phys-schost-2$ ln -s ../config/recovery.conf ./recovery.conf |
Provide the contents for the PostgreSQL recovery file.
phys-schost-1$ echo restore_command = 'cp /pgs/83_walarchives/%f %p' \ > /postgresql/data/recovery.done |
phys-schost-2$ echo restore_command = '/postgres/postgres-8.3.1/bin \ /pg_standby -k 10 -t /postgres/data/failover /postgres/83_walarchives %f %p' \ /postgresql/data/recovery.conf |
Configure the archive command in the postgresql.conf file on phys-schost-1 or zone1 by providing the following content.
archive_command = '/usr/local/bin/rsync -arv %p \ phys-schost-2:/postgres/83_walarchives/%f </dev/null' |
If you are in a zone environment , replace phys-schost-2 by zone 2 in the example.
Configure the archive command in the postgresql.conf file on phys-schost-2 or zone2 by providing the following content.
archive_command = '/usr/local/bin/rsync -arv %p \ phys-schost-1:/postgres/83_walarchives/%f </dev/null' |
If you are in a zone environment, replace phys-schost-1 by zone 1 in the example.
Exit from the postgres user ID.
phys-schost-1# exit |
Run the pgs_register script in the global zone to register the resources.
phys-schost-1# ksh /opt/SUNWscPostgreSQL/util/pgs_register -f /postgres/pgs_config_pri |
phys-schost-2# ksh /opt/SUNWscPostgreSQL/util/pgs_register -f /postgres/pgs_config_sta |
phys-schost-1# ksh /opt/SUNWscPostgreSQL/rolechg/util/rolechg-register -f \ /postgres/rolechg_config |
Add the following line to the postgresql.conf file in the PGDATA directory on both nodes and zones.
listen_addresses = 'localhost, ha-host1' |
Add the following line to the pg_hba.conf file in the PGDATA directory on both nodes and zones.
host all all 0.0.0.0/0 password |
Enable the resources in the global zone.
phys-schost-1# clresource enable STA-RS |
phys-schost-1# clresource enable PRIM-RS |
phys-schost-1# clresource enable ROLECHG-RS |
Copy the resilver scripts by performing the following steps in your target environment, either in the global or local zone.
phys-schost-2# cp /opt//SUNWscPostgreSQL/rolechg/util/resilver-step1 \ /postgres/utilities |
phys-schost-2# cp /opt//SUNWscPostgreSQL/rolechg/util/resilver-step2 \ /postgres/utilities |
phys-schost-2# chown -R postgres:postgres /postgres/utilities |
Modify the following variables in your copy of the resilver-step1 script.
##### Customize the following variables########## SOURCE_DATA=/postgres/data TARGET_DATA=/postgres/data TARGET=phys-schost-1 PGS_BASE=/postgres/postgresql-8.3.1 PRI_GRP=POSTGRES-PRI-RG STDBY_GRP=POSTGRES-STA-RG STDBY_RS=STA-RS PGPORT=5432 ROLECHG_GRP=ROLECHG-RG RESYNC=/usr/local/bin/rsync -rav SSH_PASSPHRASE=false ##### End of customizations ##########
Modify the following variables in your copy of the resilver-step2 script.
##### Customize the following variables########## SOURCE=phys-schost-2 SOURCE_DATA=/postgres/data TARGET_DATA=/postgres/data TARGET=phys-schost-1 PGS_BASE=/postgres/postgresql-8.3.1 PRI_GRP=POSTGRES-PRI-RG STDBY_GRP=POSTGRES-STA-RG STDBY_RS=STA-RS PGPORT=5432 ROLECHG_GRP=ROLECHG-RG PRI_NODE=phys-schost-1 RESYNC=/usr/local/bin/rsync -rav SSH_PASSPHRASE=false ##### End of customizations ##########