test

Sun Cluster Data Service for PostgreSQL Guide for Solaris OS

Installing and Configuring PostgreSQL on Shared Storage in the Global Zone

The tasks you must perform to install and configure PostgreSQL in the global zone are as follows:

ProcedureExample: Preparing the Cluster for PostgreSQL

  1. 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

  2. Install the following utility software on both nodes:

    • readline utility

    • rsync utility

    • gmake utility

    • Your C compiler

  3. 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
    Note –

    For a local zone, perform the steps in the local zones on each node.

  4. Ensure that the PostgreSQL users can login to each other's profile using ssh without a password prompt.

ProcedureExample: Configuring Cluster Resources for PostgreSQL

  1. Register the necessary data types on both nodes.


    phys-schost-1# clresourcetype register SUNW.gds

  2. 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

  3. Create the logical host.


    phys-schost-1# clreslogicalhostname create -g ROLECHG-RG ha-host-1

  4. 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

ProcedureExample: Modifying the PostgreSQL Configuration File

  1. 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.

  2. 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

  3. 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=
    Note –

    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
    Note –

    For a local zone installation, use the zone name zone 2 in the variable STDBY_HOST instead of phys-schost-2.

  4. Save and close the files.

ProcedureExample: Building and Installing the PostgreSQL Software on Shared Storage

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.

  1. 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

  2. Set up the build environment by performing the following steps.

    1. Create a build directory.


      phys-schost-1$ mkdir build


      phys-schost-1$ cd build

    2. 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

  3. 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

  4. Build the PostgreSQL binaries.


    phys-schost-1$ gmake

  5. Run the PostgreSQL regression tests.


    phys-schost-1$ gmake check

  6. Install the PostgreSQL binaries.


    phys-schost-1$ gmake install

  7. Install the utilities, including pg_standby.


    phys-schost-1$ cd contrib


    phys-schost-1$ gmake install


    phys-schost-1$ cd ..

  8. Clean the distribution.


    phys-schost-1$ gmake clean

ProcedureExample: Enabling the PostgreSQL Software to Run in the Cluster

  1. Create the directories for the databases, WAL archives, configurations, utilities, and the log file.

    Note –

    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

  2. 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

  3. Start the database.


    phys-schost-1$ ./bin/postmaster -D /postgres/postgresql-8.3.1

  4. Prepare the Sun Cluster specific test database.

    Note –

    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

  5. Stop the postmaster.


    phys-schost-1$ ./bin/pg_ctl -D /postgres/data stop

  6. 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
    Note –

    If your target environment is in a zone, use zone 2 instead of phys-schost-2.

  7. Protect the PostgreSQL configuration files.

    Note –

    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

  8. 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

  9. 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'
    Note –

    If you are in a zone environment , replace phys-schost-2 by zone 2 in the example.

  10. 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'
    Note –

    If you are in a zone environment, replace phys-schost-1 by zone 1 in the example.

  11. Exit from the postgres user ID.


    phys-schost-1# exit

  12. 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

  13. Add the following line to the postgresql.conf file in the PGDATA directory on both nodes and zones.


    listen_addresses = 'localhost, ha-host1'

  14. 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

  15. 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

  16. 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

  17. 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 ##########

  18. 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 ##########