This is a step by step config of pg-poolII managing two instances of postgreSQL instance.
The setup is basically run to provide data to a database server application automatically
when one posgreSQL instance goes down.
The flow diagram will assist us understand the config process.
port 5432 is assigned to the primary server while port 5433 is assigned to the standby
server. After installation of posgre Sql, the clusters are contained at.
/home/postgres/data and that destined for the standby instance will reside at
$ initdb -D /home/postgres/data $ initdb -D /home/postgres/standby.
Add the following data to the respective configuration files of the instances.
hot_standby = on wal_level = hot_standby max_wal_senders = 1 log_filename = '%A.log' log_line_prefix = '%p %t ' log_truncate_on_rotation = on
Add following to /home/postgres/standby/postgresql.conf because the standby
instance will run at port 5433.
# sudo vim /home/postgres/standby/postgresql.conf port = 5433
Put pg_hba.conf to /home/postgres/data.
# cp /....../....../pg_hba.conf /home/postgres/data
Start PostgreSQL primary server.
# pg_ctl -D /home/postgres/data start
# tar xfz /some/where/pgpool-II-3.1.1.tar.gz # cd pgpool-II-3.1.1 # ./configure # make # sudo make install # cp /....../....../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 /....../....../pgpool.conf /usr/local/etc # chown apache.apache /usr/local/etc/pgpool.conf # cp /....../....../pcp.conf /usr/local/etc # chown apache.apache /usr/local/etc/pcp.conf
The chown command is used to make PHP script execution process to be owned by the
user apache. After the process it is recommended to change the password immediately.
Install basebackup.sh and pgpool_remote_start, neccessary for online recovery.
# cp /....../....../basebackup.sh /home/postgres/data # chmod 755 basebackup.sh # cp /....../....../pgpool_remote_start
Install failover.sh for automatic failover.
# sudo cp /....../....../failover.sh /usr/local/etc # chmod 755 failover.sh
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 for primary and standby clusters.
# 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
pgpoolAdmin is a management tool for pgpool-II written in PHP and runs on PHP 4.2 or
higher. They should run together on the machine that pgpoolII is installed. It also needs
PostgreSQL extention. If you plan to build PHP, please include –with-pgsql option
# cd /var/www/html/pgpoolAdmin-3.1.1 # mkdir templates_c # chmod 777 templates_c # chown apache conf/pgmgt.conf.php # chmod 644 conf/pgmgt.conf.php
Once you are prepared, you can access the pgpoolIIAdmin web components via your web
Starting standby server
To start standby server, just click “Recovery” button. On the pgpoolII admin interface “basebackup.sh” will be executed and the standby server is automatically started. As you can see, port 5432 PostgreSQL goes down and 5433 PostgreSQL takes over the primary role. When pgpool-II finds that primary is going down it executes failover script(failover.sh). The script creates trigger file as /var/log/pgpool/trigger/trigger1. Standby server finds the file and decides to promote to primary. If you click the “Recovery” button of 5432 PostgreSQL, the former primary server will be recovered as standby server.