PostgreSQL Streaming Replication With pgpool-II Part2

Government | Commercial

KNOWLEDGE BASE

PostgreSQL 9.0 Streaming replication setting with pgpool-II: Installing pgpool-II Part2

In this configuration you need to install to-be-released-as-3.1 version of pgpool-II. Here is the tar ball.

Execute the following steps as “postgres” user. “install-functions.sh” can be downloaded here.

Replace “/downloaded /directory/” with actual directory where you downloaded the scripts.

$ tar xfz /downloaded/dir/pgpool-II-3.1.0-alpha1.tar.gz 
$ cd pgpool-II-3.1-alpha 
$ ./configure $ make 
$ sudo make install 
$ cp /downloaded/dir/install-functions.sh . 
$ sh install-functions.sh

Next you need to install pgpool-II configuration files. The main configuration file is pgpool.conf
The other one is the pcp.conf.

You will need to execute followings as root.

# cp /downloaded/dir/pgpool.conf /usr/local/etc 
# chown apache /usr/local/etc/pgpool.conf 
# cp /downloaded/dir/pcp.conf /usr/local/etc 
# chown apache /usr/local/etc/pcp.conf

The reason why we execute chown is becuase pgpoolAdmin needs to modify those files. pgpoolAdmin is a PHP script thus executed by Apache.

If you have no plan to use pgpoolAdmin(that measn you want to use pcp command line admin tools only) you do not need to execute chown.

The initial password for “postgres” in the pcp.conf is “pgpoolAdmin”. It’s strongly recommended to change the password immediately after finishing the installation.

The new password string can be obtained using pg_md5 command.

pg_md5 -p password: <your password>

Install basebackup.sh and pgpool_remote_start necessary for online recovery.

Note that in pgpool_remote_start the path to pg_ctl command is specified. You might want to change it to an appropreate path according to your PostgreSQL installation.

#! /bin/sh 
# 
# Start PostgreSQL on the recovery target node 
# 
if [ $# -ne 2 ] 
then
     echo "pgpool_remote_start remote_host remote_datadir"     
     exit 1 
fi

DEST=$1
DESTDIR=$2 
PGCTL=/usr/local/pgsql/bin/pg_ctl  

$PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &
$ cp /downloaded/dir/baseback.sh /home/postgres/data 
$ chmod 755 basebackup.sh 
$ cp /path/dir/pgpool_remote_start /home/postgres/data 
$ chmod 755 pgpool_remote_start

Install failover.sh for automatic failover.

$ sudo cp /path/dir/failover.sh /usr/local/etc 
$ chmod 755 failover.sh

Create neccessary directories. Execute followings as root. The reason why we use chown is, pgpool-II is started by pgpoolAdmin. If you do not have a plan to use pgpoolAdmin, you need to change “apache” to the user you want to invoke pgpool-II.

# mkdir /var/run/pgpool 
# chown apache /var/run/pgpool 
# mkdir /var/log/pgpool 
# chown apache /var/log/pgpool 
# mkdir /var/log/pgpool/trigger 
# chmod 777 /var/log/pgpool/trigger

Create apache user.

$ createuser apache 
Shall the new role be a superuser? (y/n) n 
Shall the new role be allowed to create databases? (y/n) n 
Shall the new role be allowed to create more new roles? (y/n) n

Next, we will be doing part 3: Installing pgpoolAdmin Part3