Specifying Configuration Files for WAL File Shipping Without Shared Storage (Sun Cluster Data Service for PostgreSQL Guide for Solaris OS)

Sun Cluster Data Service for PostgreSQL Guide for Solaris OS

Specifying Configuration Files for WAL File Shipping Without Shared Storage

You need three configuration files:

A file for the PostgreSQL primary resource
A file for the PostgreSQL standby resource
A file for the Rolechanger resource for WAL file shipping without shared storage configuration

In addition to these requirements, you also need to customize copies of resilver-step1 and resilver-step2.

The configuration files are as follows:

pgs_primary_config for the primary resource
pgs_standby_config for the standby resource
rolechg_config for the Rolechanger resource
Modified copy of the resilver-step1 script
Modified copy of the resilver-step2 script

This example shows a pgs_primary_config file, a pgs_standby_config file, and a rolechg_config file with configuration parameters are set.

The key-value pairs and explanation for a sample pgs_primary_config file are follows:

RS=postgres-prim-rs: The name of the PostgreSQL resource is postgres-prim-rs.
RG=postgres-prim-rg: The name of the resource group for the PostgreSQL resource is postgres-prim-rg.
PORT=80: The value for the dummy port for the PostgreSQL resource is 80.
LH=: SUNW.LogicalHost resource is not present in postgres-sta-rg.
HAS_RS=: SUNW. HAStoragPlus resource is not present in postgres-sta-rg.
PFILe=/postgres/pfile: The parameter file is generated in /postgres/pfile.
ZONE=: Specifies a global zone configuration.
ZONE_BT=: Specifies a global zone configuration.
PROJECT=: Specifies a global zone configuration.
USER=pgs: The name of the Solaris user who owns PostgreSQL is pgs.
PGROOT=/postgres/postgresql-8.3.1: The PostgreSQL software is installed in /postgres/postgresql-8.3.1.
PGDATA=/postgres/data: The PostgreSQL data and configuration files are installed under /postgres/data.
PGPORT=5432: The PostgreSQL database server listens on port 5432.
PGLOGFILE=/postgres/logs/scinstance1: The log file for the database server is /postgres/logs/scinstance1.
LD_LIBRARY_PATH=/usr/sfw/lib:/usr/local/lib:/usr/lib:: The libraries for the PostgreSQL server are stored in the paths of LD_LIBRARY_PATH/usr/sfw/lib:/usr/local/lib:/usr/lib directory.
ENVSCRIPT=/postgres/variables.ksh: Additional PostgreSQL variables are set in /global/ postgres/variables.ksh.
SCDB=testdb: The monitored database is testdb.
SCUSER=testusr: The user for the database monitoring is testusr.
SCTABLE=testtb1: The table testtb1 is modified to probe the condition of the database.
SCPASStestpwd: The password for the user testusr is testpwd.
NOCONRET=10: If a connection to the database testdb fails , the probe returns with return code 10.
STDBY_RS=postgres-sta-rs: The resource name of the PostgreSQL standby resource is postgres-sta-rs.
STDBY_RG=postgres-sta-rg: The resource group name of the PostgreSQL standby resource group is postgres-sta-rg.
STDBY_USER=pgs: The user who owns the PostgreSQL standby database is pgs.
STDBY_HOST=phys-node2: The name of the standby host is phys-node2.
STDBY_PARFILE=/postgres/pfile: The parameter file of the PostgreSQL standby resource is /postgres/pfile.
ROLECHG_RS=: The Rolechanger resource name has a null value because it is not needed on the primary.
SSH_PASSDIR=: The SSH_PASSDIR has a null value to indicate that the sshlkeys are not protected by a passphrase.

The key-value pairs and explanation for a pgs_standby_config file are as follows:

RS=postgres-sta-rs: The name of the PostgreSQL resource is postgres-sta-rs.
RG=postgres-sta-rg: The name of the resource group for the PostgreSQL resource is postgres-sta-rg.
PORT=80: The value for the dummy port for the PostgreSQL resource is 80.
LH=postgres-sta-rg: SUNW.LogicalHost resource is not present in postgres-sta-rg.
HAS_RS=: SUNW. HAStoragPlus resource is not present in postgres-sta-rg.
PFILe=/postgres/pfile: The parameter file is generated in /postgres/pfile.
ZONE=: The null value indicates that it is a global zone configuration.
ZONE_BT=: The null value indicates that it is a global zone configuration.
PROJECT=: The null value indicates that it is a global zone configuration.
USER=pgs: The name of the Solaris user who owns PostgreSQL is pgs.
PGROOT=/postgres/postgresql-8.3.1: The PostgreSQL software is installed in /postgres/postgresql-8.3.1.
PGDATA=/postgres/data: The PostgreSQL data and configuration files are installed under /postgres/data.
PGPORT=5432: The PostgreSQL database server listens on port 5432.
PGLOGFILE=/postgres/logs/scinstance1: The log file for the database server is /postgres/logs/scinstance1.
LD_LIBRARY_PATH=/usr/sfw/lib:/usr/local/lib:/usr/lib:: The libraries for the PostgreSQL server are stored in the paths of the LD_LIBRARY_PATH/usr/sfw/lib: /usr/local/lib: /usr/lib: directory.
ENVSCRIPT=/postgres/variables.ksh: Additional PostgreSQL variables are set in /global/ postgres/variables.ksh.
SCDB=testdb: The monitored database is testdb.
SCUSER=testusr: The user for the database monitoring is testusr.
SCTABLE=testtb1: The table testtb1 is modified to probe the condition of the database.
SCPASS=testpwd: The password for the user testusr is testpwd.
NOCONRET=10: If a connection to the database testdb fails, the probe returns with return code 10.
STDBY_RS=: The value for the STDBY_RS is not required in a standby configuration.
STDBY_RG=: The value for STDBY_RG is not required in a standby configuration.
STDBY_USER=: The value for STDBY_USER is not required in a standby configuration.
STDBY_HOST=: The value for STDBY_HOST is not required in a standby configuration.
STDBY_PARFILE=: The value for STDBY_PARFILE is not required in a standby configuration.
ROLECHG_RS=rolechg-rs: The Rolechanger resource is rolechg-rs.
SSH_PASSDIR=: The SSH_PASSDIR has a null value , which means that the sshlkeys are not protected by a passphrase.

The key-value pairs and explanation for configuration file rolechg-config are as follows:

RS=rolechg-rs: The name of the Rolechanger resource is rolechg-rs.
RG=rolechg-rg: The name of the resource group for the PostgreSQL resource is rolechg-rg.
PORT=5432: The value of the dummy port for the PostgreSQL resource is 5432.
LH=pgs-1h-1: The resource name for the SUNW.LogicalHost resource is pgs-1h-1.
HAS_RS=: SUNW. HAStoragPlus resource or other dependencies are not present.
STDBY_RS=postgres-sta-rs: The name of the PostgreSQL standby resource is postgres-sta-rs.
PRI_RS=postgres-pri-rs: The name of the PostgreSQL primary resource is postgres-prim-rs.
STDBY_HOST=phys-node: The physical node name of the standby is phys-node2.
STDBY_PFILE=/postgres/pfile: The parameter file on the standby is /postgres/pfile.
TRIGGER=/postgres/data/failover: The trigger file on which the pg_standby utility reacts is phys-node2.
WAIT=30: After the resource is started, Rolechanger waits for 30 seconds until it touches the trigger file.

Modifications in a copy of resilver-step1

SOURCE_DATA=/postgres/data: PGDATA of the standby is in /postgres/data.
TARGET_DATA=/postgres/data: PGDATA of the primary is in /postgrs/data.
TARGET=phys-node1: Specifies the name of the target node. The usual name is the name of the designated primary node.
PGS_BASE=/pgs/postgres-8.3: Specifies the PostgreSQL base directory, where the PostgreSQL binaries are located.
PRI_GRP=primary-rg: Specifies the resource group that contains the cluster resource of the designated primary.
STDBY_RS=standby-rs: Specifies the resource group that contains the cluster resource of the designated standby.
PGPORT=5432: Specifies the database port.
ROLECHG_GRP=rolechg-rg: Specifies the resource group that contains the Rolechanger resource.
RSYNC=“/usr/local/bin/rsync –rysnc-path=/usr/local/bin/rysnc —rav”: Specifies the absolute path to the RSYNC command, including the necessary options.
SSH_PASSPHRASE=false: Specifies whether your passphrase is secure.

Modifications in a copy of resilver-step2

SOURCE=phys-node2: The source node is phys-node2.
SOURCE_DATA=/postgres/data: PGDATA of the standby is in /postgres/data.
TARGET_DATA=/postgres/data: PGDATA of the primary is in /postgrs/data.
TARGET=phys-node1: The target node is phys-node1.
PGS_BASE=/user/postgres/8.3: Specifies the PostgreSQL base directory, where the PostgreSQL binaries are located.
PRI_GRP=primary-rg: Specifies the resource group which contains the cluster resource of the designated primary.
STDBY_GRP=postgres-sta-rg: The resource group for the standby resource is postgres-sta-rg.
STDBY_RS=standby-rs: Specifies the resource group which contains the cluster resource of the designated standby.
PGPORT=5432: Specifies the database port.
ROLECHG_GRP=rolechg-rg: The resource group for the Rolechanger resource group is rolechg-rg.
PRI_NODE=phys-node1: The primary node is the global zone of phys-node1.
RSYNC=“/usr/local/bin/rsync –rysync-path=/usr/local/bin/rysync -rav”: Specifies the absolute path to the RSYNC command including the necessary options.
SSH_PASSPHRASE=false: Specifies whether your passphrase is secure.