In this section we assume that you have already installed Pgpool-II following an instruction described in . Alternatively you can use to create a temporary installation of Pgpool-II and PostgreSQL. In this section we are going to explain how to manage a PostgreSQL cluster with streaming replication using Pgpool-II, which is one of most common setup. Before going further, you should properly set up pgpool.conf with streaming replication mode. Sample configurations are provided with Pgpool-II, there configuration file are located at /usr/local/etc with default installation from source code. you can copy pgpool.conf.sample as pgpool.conf. cp /usr/local/etc/pgpool.conf.sample pgpool.conf If you plan to use pgpool_setup, type: pgpool_setup This will create a Pgpool-II with streaming replication mode installation, primary PostgreSQL installation, and a async standby PostgreSQL installation. From now on, we assume that you use pgpool_setup to create the installation under current directory. Please note that the current directory must be empty before executing pgpool_setup. To start the whole system, type: ./startall Once the system starts, you can check the cluster status by issuing a pseudo SQL command called "show pool_nodes" to any of databases. pgpool_setup automatically creates "test" database. We use the database. Note that the port number is 11000, which is the default port number assigned to Pgpool-II by pgpool_setup. $ psql -p 11000 -c "show pool_nodes" test node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | false | 0 | 2019-01-31 10:23:09 1 | /tmp | 11003 | up | 0.500000 | standby | 0 | true | 0 | 2019-01-31 10:23:09 (2 rows) The result shows that the "status" column is "up", which means the PostgreSQL is up and running, which is good. Let's test the replication functionality using a benchmark tool pgbench, which comes with the standard PostgreSQL installation. Type following to create the benchmark tables. $ pgbench -i -p 11000 test To see if the replication works correctly, directly connect to the primary and the standby server to see if they return identical results. $ psql -p 11002 test \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+--------- public | pgbench_accounts | table | t-ishii public | pgbench_branches | table | t-ishii public | pgbench_history | table | t-ishii public | pgbench_tellers | table | t-ishii (4 rows) \q $ psql -p 11003 test \dt List of relations Schema | Name | Type | Owner --------+------------------+-------+--------- public | pgbench_accounts | table | t-ishii public | pgbench_branches | table | t-ishii public | pgbench_history | table | t-ishii public | pgbench_tellers | table | t-ishii (4 rows) The primary server (port 11002) and the standby server (port 11003) return identical results. Next, let's run pgbench for a while and check to results. $ pgbench -p 11000 -T 10 test starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 10 s number of transactions actually processed: 4276 latency average = 2.339 ms tps = 427.492167 (including connections establishing) tps = 427.739078 (excluding connections establishing) $ psql -p 11002 -c "SELECT sum(abalance) FROM pgbench_accounts" test sum -------- 216117 (1 row) $ psql -p 11003 -c "SELECT sum(abalance) FROM pgbench_accounts" test sum -------- 216117 (1 row) Again, the results are identical. Pgpool-II allows read query load balancing. It is enabled by default. To see the effect, let's use pgbench -S command. $ ./shutdownall $ ./startall $ pgbench -p 11000 -c 10 -j 10 -S -T 60 test starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 10 duration: 60 s number of transactions actually processed: 1086766 latency average = 0.552 ms tps = 18112.487043 (including connections establishing) tps = 18125.572952 (excluding connections establishing) $ psql -p 11000 -c "show pool_nodes" test node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 537644 | false | 0 | 2019-01-31 11:51:58 1 | /tmp | 11003 | up | 0.500000 | standby | 548582 | true | 0 | 2019-01-31 11:51:58 (2 rows) "select_cnt" column shows how many SELECT are dispatched to each node. Since with the default configuration, Pgpool-II tries to dispatch equal number of SELECT, the column shows almost same numbers. Pgpool-II offers more sophisticated strategy for load balancing. See for more details. Pgpool-II allows an automatic fail over when PostgreSQL server goes down. In this case Pgpool-II sets the status of the server to "down" and continue the database operation using remaining servers. $ pg_ctl -D data1 stop waiting for server to shut down.... done server stopped $ psql -p 11000 -c "show pool_nodes" test node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 4276 | true | 0 | 2019-01-31 12:00:09 1 | /tmp | 11003 | down | 0.500000 | standby | 1 | false | 0 | 2019-01-31 12:03:07 (2 rows) The standby node was shut down by pg_ctl command. Pgpool-II detects it and detaches the standby node. "show pool_nodes" command shows that the standby node is in down status. You can continue to use the cluster without the standby node: $ psql -p 11000 -c "SELECT sum(abalance) FROM pgbench_accounts" test sum -------- 216117 (1 row) What happens if the primary server goes down? In this case, one of remaining standby server is promoted to new primary server. For this testing, we start from the state in which both nodes are up. $ psql -p 11000 -c "show pool_nodes" test node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | false | 0 | 2019-01-31 12:04:58 1 | /tmp | 11003 | up | 0.500000 | standby | 0 | true | 0 | 2019-01-31 12:04:58 (2 rows) $ pg_ctl -D data0 stop waiting for server to shut down.... done server stopped $ psql -p 11000 -c "show pool_nodes" test node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | /tmp | 11002 | down | 0.500000 | standby | 0 | false | 0 | 2019-01-31 12:05:20 1 | /tmp | 11003 | up | 0.500000 | primary | 0 | true | 0 | 2019-01-31 12:05:20 (2 rows) Now the primary node is changed from 0 to 1. What happens inside? When the node 0 goes down, Pgpool-II detects it and executes failover_command defined in pgpool.conf. Here is the content of the file. #! /bin/sh # Execute command by failover. # special values: %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new main node id # %M = old main node id # %H = new main node host name # %P = old primary node id # %R = new main database cluster path # %r = new main port number # %% = '%' character failed_node_id=$1 failed_host_name=$2 failed_port=$3 failed_db_cluster=$4 new_main_id=$5 old_main_id=$6 new_main_host_name=$7 old_primary_node_id=$8 new_main_port_number=$9 new_main_db_cluster=${10} mydir=/home/t-ishii/tmp/Tutorial log=$mydir/log/failover.log pg_ctl=/usr/local/pgsql/bin/pg_ctl cluster0=$mydir/data0 cluster1=$mydir/data1 date >> $log echo "failed_node_id $failed_node_id failed_host_name $failed_host_name failed_port $failed_port failed_db_cluster $failed_db_cluster new_main_id $new_main_id old_main_id $old_main_id new_main_host_name $new_main_host_name old_primary_node_id $old_primary_node_id new_main_port_number $new_main_port_number new_main_db_cluster $new_main_db_cluster" >> $log if [ a"$failed_node_id" = a"$old_primary_node_id" ];then # main failed ! new_primary_db_cluster=${mydir}/data"$new_main_id" echo $pg_ctl -D $new_primary_db_cluster promote >>$log # let standby take over $pg_ctl -D $new_primary_db_cluster promote >>$log # let standby take over sleep 2 fi The script receives necessary information as parameters from Pgpool-II. If the primary server goes down, it executes "pg_ctl -D data1 promote", which should promote the standby server to a new primary server. Pgpool-II allows to recover a downed node by technique called "Online Recovery". This copies data from the primary node to a standby node so that it sync with the primary. This may take long time and database may be updated during the process. That's no problem because in the streaming configuration, the standby will receive WAL log and applies it to catch up the primary. To test online recovery, let's start with previous cluster, where node 0 is in down state. $ pcp_recovery_node -p 11001 -n 0 Password: pcp_recovery_node -- Command Successful $ psql -p 11000 -c "show pool_nodes" test node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | standby | 0 | false | 0 | 2019-01-31 12:06:48 1 | /tmp | 11003 | up | 0.500000 | primary | 0 | true | 0 | 2019-01-31 12:05:20 (2 rows) is one of control commands coming with Pgpool-II installation. The argument -p is to specify the port number assigned to the command, which is 11001 set by pgpool_setup. The argument -n is to specify the node id to be recovered. After executing the command, node 0 returned to "up" status. The script executed by pcp_recovery_node is specified as "recovery_1st_stage_command" in pgpool.conf. Here is the file installed by pgpool_setup. #! /bin/sh psql=/usr/local/pgsql/bin/psql DATADIR_BASE=/home/t-ishii/tmp/Tutorial PGSUPERUSER=t-ishii main_db_cluster=$1 recovery_node_host_name=$2 DEST_CLUSTER=$3 PORT=$4 recovery_node=$5 pg_rewind_failed="true" log=$DATADIR_BASE/log/recovery.log echo >> $log date >> $log if [ $pg_rewind_failed = "true" ];then $psql -p $PORT -c "SELECT pg_start_backup('Streaming Replication', true)" postgres echo "source: $main_db_cluster dest: $DEST_CLUSTER" >> $log rsync -C -a -c --delete --exclude postgresql.conf --exclude postmaster.pid \ --exclude postmaster.opts --exclude pg_log \ --exclude recovery.conf --exclude recovery.done \ --exclude pg_xlog \ $main_db_cluster/ $DEST_CLUSTER/ rm -fr $DEST_CLUSTER/pg_xlog mkdir $DEST_CLUSTER/pg_xlog chmod 700 $DEST_CLUSTER/pg_xlog rm $DEST_CLUSTER/recovery.done fi cat > $DEST_CLUSTER/recovery.conf $lt;$lt;REOF standby_mode = 'on' primary_conninfo = 'port=$PORT user=$PGSUPERUSER' recovery_target_timeline='latest' restore_command = 'cp $DATADIR_BASE/archivedir/%f "%p" 2> /dev/null' REOF if [ $pg_rewind_failed = "true" ];then $psql -p $PORT -c "SELECT pg_stop_backup()" postgres fi if [ $pg_rewind_failed = "false" ];then cp /tmp/postgresql.conf $DEST_CLUSTER/ fi Pgpool-II is a proxy server sitting between clients and PostgreSQL. Pgpool-II understands the wire level protocol used by PostgreSQL called "frontend and backend protocol". For more details of the protocol, see the PostgreSQL manual. No modified PostgreSQL is required to use Pgpool-II (more precisely, you will need a few extensions to use full functions of Pgpool-II). So Pgpool-II can cope with variety of PostgreSQL versions. In theory, even the earliest version of PostgreSQL can be used with Pgpool-II. Same thing can be said to client side. As long as it follows the protocol, Pgpool-II happily accept connections from it, no matter what kind of languages or drivers it uses. Pgpool-II consists of multiple process. There is a main process, which is the parent process of all other process. It is responsible for forking child process each of which accepts connections from clients. There are some worker process those are forked from the main process as well, which is responsible for detecting streaming replication delay. There is also a special process called "pcp process", which is solely used for management of Pgpool-II itself. Pgpool-II has a built-in high availability function called "watchdog". Watchdog consists of some process. For more details of watchdog, see .