Installing Pgpool-II on Debian/Ubuntu

First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu.

This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG.

Prerequisites

This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers. 

For testing purpose,we set trust authentication in pg_hba.conf. In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings. 

Install Pgpool-II

To use the PostgreSQL apt repository, follow the steps below.

Create the repository configuration file:

# echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Import the repository signing key:

# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package lists:

# apt-get update

Install the latest version of Pgpool-II. The current latest version is 4.3.1. Specify the specific version of PostgreSQL which you are using, e.g. postgresql-14-pgpool2:

# apt-get -y install pgpool2 libpgpool2 postgresql-14-pgpool2

Configure Pgpool-II 

Edit /etc/pgpool2/pgpool.conf and add your PostgreSQL servers configurations:

backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'

backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/standby'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'

sr_check_user = 'postgres'
health_check_period = 10
health_check_user = 'postgres'

failover_command = '/etc/pgpool2/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S' 

Next, let's setup automatic failover. The sample scripts are installed under /usr/share/doc/pgpool2/examples/scripts/ directory. 

Copy the sample failover script: 

# cp -p /usr/share/doc/pgpool2/examples/scripts/failover.sh.sample /etc/pgpool2/failover.sh

Basically, it should work if you replace PGHOME using your PostgreSQL installation directory. If you installed PostgreSQL using PostgreSQL APT repository, PostgreSQL should be installed under /usr/lib/postgresql/14 directory.

PGHOME=/usr/lib/postgresql/14

To run the failover script, you must allow passwordless SSH to all PostgreSQL servers. 

Because in this tutorial the PostgreSQL servers and Pgpool-II are running on the local machine, run the following commands to allow passwordless SSH to localhost.

# mkdir .ssh 
# chmod 700 .ssh
# cd ~/.ssh
# ssh-keygen -t rsa -f id_rsa_pgpool
# ssh-copy-id -i id_rsa_pgpool.pub postgres@localhost

# su - postgres
$ cd ~/.ssh
$ ssh-keygen -t rsa -f id_rsa_pgpool
$ ssh-copy-id -i id_rsa_pgpool.pub postgres@localhost

When starting Pgpool-II, if the pgpool_status file exists, Pgpool-II will read the PostgreSQL servers status (up/down) from the pgpool_status file. If you want to ignore the pgpool_status file at startup, modify ExecStart in systemd unit file. 

Here we use systemctl edit pgpool2 command to modify the unit file and add the following configuration:

[Service]
ExecStart=
ExecStart=/usr/sbin/pgpool -n -D

The settings above are for test purpose only. In a production environment, you need to configure advanced settings (e.g. watchdog, logging and online recovery).

Start Pgpool-II

Start Pgpool-II service using the following command:

# systemctl restart pgpool2

To verify the configuration, use the psql to connect to Pgpool-II and verify the PostgreSQL status.

# psql -p 9999 -h localhost -U postgres 
node_id | hostname  | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+-----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0       | localhost | 5432 | up     | up        | 0.500000  | primary | primary | 0          | false             | 0                 |                   |                        | 2022-03-20 01:22:03
1       | localhost | 5433 | up     | up        | 0.500000  | standby | standby | 0          | true              | 0                 | streaming         | async                  | 2022-03-20 01:22:03
(2 rows)

We can see that Pgpool-II can successfully connect to the PostgreSQL database servers.

Verify automatic failover

If the primary goes down, Pgpool-II can detect the failure of PostgreSQL and automatically perform failover. We Stop the primary PostgreSQL server.

# su - postgres -c '/usr/lib/postgresql/14/bin/pg_ctl stop -D /var/lib/postgresql/main'

Pgpool-II will detect the failure and automatically promote a standby server to primary.

# psql -p 9999 -h localhost -U postgres 
node_id | hostname  | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+-----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0       | localhost | 5432 | down   | down      | 0.500000  | standby | unknown | 0          | false             | 0                 |                   |                        | 2022-03-20 02:00:55
1       | localhost | 5433 | up     | up        | 0.500000  | primary | primary | 0          | true              | 0                 |                   |                        | 2022-03-20 02:00:55
(2 rows)