Postgres automatic and (mostly) transparent) failover with pgpool

I have been working a lot with pgpool recently and I wanted to document my experiments in case it is helpful for others.

In this post I will document step by step how to install postgres/pgpool and how to configure pgpool to automate the failover. I will then install a graphical interface on top of pgpool (that I developed).

Intro

This post is intended as a learning material, it lists step by step how to install postgres/pgpool/repmgr. Since everything can be done at the command line, I will later expand on this set-up to do automated testing of pgpool watchdog. Please note that in my current company I deployed postgres and pgpool as docker images, either in a docker swarm or directly on the servers (i.e. using docker run to start the containers). This is all in the github repo https://github.com/saule1508/pgcluster. If you start a learning journey with pgpool and postgres, using docker can be of good help because it makes experimenting and testing very easy (you can quickly start from scratch).

I will put more emphasis on pgpool concepts or configurations that I found difficult to grasp (sometimes because they are poorly documented). For postgres and repmgr and will essentially list all the steps required to set it up but it is obviously very much recommanded to read the respective docs.

The end result is a set-up composed of 3 Centos 7.6 servers: one primary database and two stand-by databases, with one pgpool on each node made high-available with the watch-dog mode of pgpool. I use repmgr (https://repmgr.org/), not to automate the failover but because it brings well tested scripts on top of postgres replication.

NB: I am using libvirt to provision the VM (I provision one server then clone it), but you can use vagrant, virtual box, VMWare or provision the centos guests manually. If you have a linux host available then I suggest to use libvirt, I documented here a way to provision a centos guest from the command line in a few minutes: Centos guest VM with cloud-init, this is just great.

The end-result will be similar to this:

  • diagram

  • graphical interface

pgpool: some facts

Before building the set-up, here are some considerations on Pgpool. Pgpool is hard to learn because it can do a lot of things and the documentation is overwhelming and not always clear. There are 3 main functions that are useful, the other are more esoteric or for historic reasons (like query result caching, master-master replication,…). Pgpool has a good community and once we know it we like it

Connection caching

If you think about traditionnal connection pooling in the java world, where the webcontainer manages the connection pool and java threads borrow one connection from the pool for the time of a transaction, then you will be confused by Pgpool. Pgpool does connection caching. When application connects to Pgpool, Pgpool uses one of its child process, then create a connection to postgres and associate the postgres connection to the child process. When the application disconnects from pgpool, the postgres connection is cleaned-up (to release locks for ex.) and it is kept open, so that it remains in the cache for the next time.

If you have modern applications using connection pooling and if you have a lot of different databases or user name, then connection caching of pgpool might not be interesting and it can be disabled (parameter connection_cache=’off’)

Automated failover and (mostly) transparent

Postgres has a strong replication mechanisme (hot standby) in which a primary database streams its transaction logs (wal in postgres terminology, write-ahead-logs) and one or more standby databases receive those write-ahead-log and apply them continuously so that all changes done on the primary are replicated on the standbys. The primary database is open read-write, the stand-by databases are opened read-only. But postgres does not offer any automated way to promote a stand-by database in case of failure of the primary. Furthermore, if a stand-by database is manually promoted and become the new read-write database, all applications connected to the database must now be made aware that they must connect to a new instance.

Pgpool solve those problems, it is used on top of the postgres databases, acting as a proxy, it uses the following mechanisms to detect a failure and trigger a failover:

  • health checks: pgpool connects (with user health_check_user) regularly (param health_check_period) to the databases and so is aware when a primary (or a standby) fails. After a retry period (health_check_retry_interval and health_check_retries) it will trigger a failover, which concretely mean calling a script with a number of arguments.
  • Alternatively it can use the parameter failover_on_backend_error. This parameter should be off if you use health check. What failover_on_backend error means is that when Pgpool receives an error on one of the connections, it will immediately trigger a failover. So there is no retry. Furthermore is there is no activity then the failure will not be detected and the failover will not occur.

Transparent failover is achieved because clients are connecting to pgpool and not directly to postgres: if a failover happens, then pgpool knows that the primary database has changed and it will destroy then re-create all connections to postgres without any configuration needed on the client side. Of course it is not completly transparent, any statement (query or update) that was being executed will return an error.

But if pgpool is a proxy between the applications and postgres, a failure of pgpool itself would result in a lost of availability. In other words pgpool itself has become the single point of failure and of course it has a very nice mechanism that makes itself HA: the watchdog mode

pgpool watchdog mode

  • watchdog adds high availability by coordinating multiple pgpool nodes (one leaded elected via consensus). The leader acquires the VIP (acquiring the VIP is done via a script using ip and arp)
  • pgpool nodes communicates via tcp port 9000 (ADD_NODE command, etc.)
  • watchdog uses a heartbeat mechanism (optionally it can use a query mechanism instead): a “lifecheck process” sends and receives data over UDP (port 9694) to check the availability of other nodes

pgpool management protocol

pcp exposes its function through pcp (pgpool control protocol) on tcp port 9898. This requires a user (choose postgres) and a password (the combination must be stored in a file /home/postgres/.pcppass), so that the linux user postgres can use pcp_attach_node, pcp_recovery_node, etc…

1. Create first server with centos 7, postgres, repmgr and pgpool

The installation of the centos 7 guest is described in the link http://saule1508.github.io/libvirt-centos-cloud-image.

It needs a file system /u01 for the database (the DB will be in /u01/pg11/data), a file system /u02 for the archived write ahead logs (wal) and the backups (/u02/archive and /u02/backup). I create a user postgres with uid 50010 (arbitrary id, does not matter but it is better to have the same id on all servers). The user must be created before the rpm is installed.

1.2 Postgres

# as user root
MAJORVER=11
MINORVER=2
yum update -y
# install some useful packages
yum install -y epel-release libxslt sudo openssh-server openssh-clients jq passwd rsync iproute python-setuptools hostname inotify-tools yum-utils which sudo vi firewalld
# create user postgres before installing postgres rpm, because I want to fix the uid
useradd -u 50010 postgres
# set a password
passwd postgres
# So that postgres can become root without password, I add it in sudoers 
echo "postgres ALL=(ALL) NOPASSWD:ALL" > /etc/sudoers.d/postgres
# install postgres release rpm, this will add a file /etc/yum.repos.d/pgdg-11-centos.repo
yum install -y https://download.postgresql.org/pub/repos/yum/${MAJORVER}/redhat/rhel-7-x86_64/pgdg-centos${MAJORVER}-${MAJORVER}-${MINORVER}.noarch.rpm
# with the repo added (repo is called pgdg11), ww can install postgres
# NB: postgres is also available from Centos base repos, but we want the latest version (11)
yum install -y postgresql${MAJORVER}-${MAJORVER}.${MINORVER} postgresql${MAJORVER}-server-${MAJORVER}.${MINORVER}  postgresql${MAJORVER}-contrib-${MAJORVER}.${MINORVER}
# verify 
yum list installed postgresql*

I want to store the postgres database in /u01/pg11/data (non-default location), I want to have the archived wal in /u02/archive and the backup in /u02/backup

mkdir -p /u01/pg${MAJORVER}/data /u02/archive /u02/backup
chown postgres:postgres /u01/pg${MAJORVER}/data /u02/archive /u02/backup
chmod 700 /u01/pg${MAJORVER}/data /u02/archive

The environment variable PGDATA is important: it points to the database location. We need to change it from the rpm default (which is /var/lib/pgsql/11/data) to the new location /u01/pg11/data.

export PGDATA=/u01/pg${MAJORVER}/data
# add the binaries in the path of all users
echo "export PATH=\$PATH:/usr/pgsql-${MAJORVER}/bin" >  /etc/profile.d/postgres.sh
# source /etc/profile in bashrc of user postgres, make sure that PGDATA is defined and also PGVER so that we 
# can use PGVER in later scripts
echo "[ -f /etc/profile ] && source /etc/profile" >> /home/postgres/.bashrc 
echo "export PGDATA=/u01/pg${MAJORVER}/data" >> /home/postgres/.bashrc
echo "export PGVER=${MAJORVER}" >> /home/postgres/.bashrc

We need a systemd unit file for postgres, so that it is started automatically when we boot the server (see https://www.postgresql.org/docs/11/server-start.html)

cat <<EOF > /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/usr/pgsql-11/bin/postgres -D /u01/pg11/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target
EOF

enable the unit but don’t start it yet

sudo systemctl enable postgresql

Now we can init the database, as user postgres

su - postgres
# check that PGDATA and the PATH are correct
echo $PGDATA
echo $PATH
pg_ctl -D ${PGDATA} initdb -o "--auth=trust --encoding=UTF8 --locale='en_US.UTF8'"

postgres configuration parameters are in $PGDATA/postgres.conf, I prefer not to change this file but include an additional file from the config.d directory and override some of the defaults parameter. I add the line “include_dir=’conf.d’” at the end of postgresql.conf and then I add custom configurations in conf.d

# as user postgres
mkdir $PGDATA/conf.d
echo "include_dir = 'conf.d'" >> $PGDATA/postgresql.conf
# now let's add some config in this conf.d directory
cat <<EOF > $PGDATA/conf.d/custom.conf
log_destination = 'syslog,csvlog'
logging_collector = on
# better to put the logs outside PGDATA so they are not included in the base_backup
log_directory = '/var/log/postgres'
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
# These are relevant when logging to syslog (if wanted, change log_destination to 'csvlog,syslog')
log_min_duration_statement=-1
log_duration = on
log_line_prefix='%m %c %u - ' 
log_statement = 'all'
log_connections = on
log_disconnections = on
log_checkpoints = on
log_timezone = 'Europe/Brussels'
# up to 30% of RAM. Too high is not good.
shared_buffers = 512MB
#checkpoint at least every 15min
checkpoint_timeout = 15min 
#if possible, be more restrictive
listen_addresses='*'
#for standby
max_replication_slots = 5
archive_mode = on
archive_command = '/opt/postgres/scripts/archive.sh  %p %f'
# archive_command = '/bin/true'
wal_level = replica
max_wal_senders = 5
hot_standby = on
hot_standby_feedback = on
# for pg_rewind
wal_log_hints=true
EOF

I prefer to put the logs outside PGDATA, otherwise a backup might become very big just because of the logs. So we must create the directory

sudo mkdir /var/log/postgres
sudo chown postgres:postgres /var/log/postgres

As you can see I use the script /opt/postgres/scripts/archive.sh as archive command, this needs to be created

sudo mkdir -p /opt/postgres/scripts
sudo chown -R postgres:postgres /opt/postgres

content of script /opt/postgres/scripts/archive.sh

#!/bin/bash

LOGFILE=/var/log/postgres/archive.log
if [ ! -f $LOGFILE ] ; then
 touch $LOGFILE
fi
echo "archiving $1 to /u02/archive/$2"
cp $1 /u02/archive/$2
exit $?

make sure it is executable

chmod +x /opt/postgres/scripts/archive.sh

⚠️: When archive mode is on, you must clean the directory where archived wal’s are copied. Usually via a backup script that takes a backup and then remove archived wal older than the backup

Now let’s create a database and a user

sudo systemctl start postgresql
# verify
sudo systemctl status postgresql

psql -U postgres postgres

at the psql prompt, create a database (my database is called critlib), then quit

create database critlib encoding 'UTF8'  LC_COLLATE='en_US.UTF8';
\q

now reconnect to the newly create database and create two users (cl_owner and cl_user)

psql -U postgres critlib
create user cl_owner nosuperuser nocreatedb login password 'cl_owner';
create schema cl_owner authorization cl_owner;
create user cl_user nosuperuser nocreatedb login password 'cl_user';
grant usage on schema cl_owner to cl_user;
alter default privileges in schema cl_owner grant select,insert,update,delete on tables to cl_user;
alter role cl_user set search_path to "$user",cl_owner,public;
\q

Let’s open the firewall port 5432

# as root
firewall-cmd --add-port 5432/tcp --permanent
systemctl restart firewalld

Before cloning the server (we need 3 servers) I will install the packages repmgr and pgpool. After that we can clone the server (it is a VM !), set-up the streaming replication with repmgr and then configure pgpool

1.2 repmgr

Repmgr is a nice open-source tool made by 2ndquadrant. It is not mandatory to use it and at first it maybe worth to set-up the streaming replication using standard postgres commands, for learning purpose. But otherwise it is really worth using repmgr because it brings very well tested and documented scripts. Note that repmgr can also be used to automate the failover (via the repmgrd daemon process) but we don’t want that because we will use pgpool.

MAJORVER=11
REPMGRVER=4.2
curl https://dl.2ndquadrant.com/default/release/get/${MAJORVER}/rpm | bash
yum install -y --enablerepo=2ndquadrant-dl-default-release-pg${MAJORVER} --disablerepo=pgdg${MAJORVER} repmgr${MAJORVER}-${REPMGRVER}
mkdir /var/log/repmgr && chown postgres:postgres /var/log/repmgr

set ownership of /etc/repmgr to postgres

chown -R postgres:postgres /etc/repmgr

1.3 install pgpool

This is just about installing the rpm, the configuration will be done later. The configuration has to be done on each server but we can already install pgpool before cloning the server. Adding pgpool on Centos is easy: install the pgpool release rpm (it will create a repo file in /etc/yum.repo.d) and then install pgpool itself via yum.

export PGPOOLMAJOR=4.0
export PGPOOLVER=4.0.3
export PGVER=11

yum install -y http://www.pgpool.net/yum/rpms/${PGPOOLMAJOR}/redhat/rhel-7-x86_64/pgpool-II-release-${PGPOOLMAJOR}-1.noarch.rpm
yum install --disablerepo=pgdg11 --enablerepo=pgpool40 -y pgpool-II-pg11-${PGPOOLVER} pgpool-II-pg11-extensions-${PGPOOLVER} pgpool-II-pg11-debuginfo-${PGPOOLVER}
``

I prefer to have pgpool running as user postgres, so I will override the systemd unit file that was installed by the rpm

```bash
# as user root
mkdir /etc/systemd/system/pgpool.service.d
cat <<EOF > /etc/systemd/system/pgpool.service.d/override.conf
[Service]
User=postgres
Group=postgres
EOF

Since pgpool connects to the various postgres servers via ssh, I add a few config to the ssh client to make ssh connections easier

cat <<EOF > /etc/ssh/ssh_config
StrictHostKeyChecking no 
UserKnownHostsFile /dev/null 
EOF

Since I will use postgres user to start pgpool, I will change the ownership of /opt/pgpool-II

sudo chown postgres:postgres -R /etc/pgpool-II

I also need to create a directory for the pid file and the socket file

sudo mkdir /var/run/pgpool
sudo chown postgres:postgres /var/run/pgpool

1.4 Clone the servers

Now that I have pg01 ready, I will clone it to pg02 and pg03

I am using virsh (KVM) to manage my guests, those are the commands I used to clone my VM (the VM was created with this procedure http://saule1508.github.io/libvirt-centos-cloud-image)

Note: this is mostly for my own record, your way of cloning a VM might be different of course or you might have physical servers or you might use ansible to automate the provisioning,…

# on my host
virsh shutdown pg01
sudo mkdir /u01/virt/{pg02,pg03}
sudo chown pierre:pierre /u01/virt/pg02 /u01/virt/pg03
virt-clone -o pg01 -n pg02 --file /u01/virt/pg02/pg02.qcow2 --file /u01/virt/pg02/pg02-disk1.qcow2
virt-clone -o pg01 -n pg03 --file /u01/virt/pg03/pg03.qcow2 --file /u01/virt/pg03/pg03-disk1.qcow2

Now start the VM, get into it and change its IP address. To have a DNS, I also add an entry in /etc/hosts of the KVM host

# /etc/hosts of the KVM server, add one entry per vm so that they can speak to each other via dns
# added
192.168.122.10 pg01.localnet
192.168.122.11 pg02.localnet
192.168.122.13 pg03.localnet

when doing so, one must stop and start the default network then restart libvirtd

virsh net-destroy default
virsh net-start default
sudo systemctl restart libvirtd

Connect to each VM via ssh, change the IP address and the hostname (hostname-ctl set-hostname)

2. Set-up streaming replication with repmgr

Now I have 3 centos 7 VM with postgres, repmgr and pgpool installed. The 3 VM can resolve each other names via DNS but alternatively we could put 3 entries in the /etc/hosts file on each VM

Host IP
pg01.localnet192.168.122.10
pg02.localnet192.168.122.11
pg03.localnet192.168.122.12

We will use repmgr to set-up the 3 postgres instances in a primary - standby configuration where pg01 will be the primary. pg01 will be streaming his write ahead logs (wal) to pg02 and pg03.

First we want to set-up ssh keys so that each servers can connect to each other with the user postgres. On each server, generate a ssh keys pairt for user postgres and transfer the public key to both other servers.

# on pg01 as user postgres. Keep the default (no passphrase)
ss-keygen -t rsa
ssh-copy-id postgres@pg01.localnet
ssh-copy-id postgres@pg02.localnet
ssh-copy-id postgres@pg03.localnet

From pg01 postgres user must be able to connect to pg02 and pg03 but also to pg01 (itself) via ssh without a password. This is because pgpool - running on those servers - will connect via ssh to any of the postgres server to perform some operation (standby promote, standby follow, node recovery)

do the same on server pg02 and on server pg03

2.1 primary database on pg01

For the streaming replication we will be using the user repmgr with password rep123. Let’s create it.

create the user repmgr

# on pg01 as user postgres
psql <<-EOF
  create user repmgr with superuser login password 'rep123' ;
  alter user repmgr set search_path to repmgr,"\$user",public;
  \q
EOF

create a database called repmgr

# on pg01 as user postgres
psql --command "create database repmgr with owner=repmgr ENCODING='UTF8' LC_COLLATE='en_US.UTF8';"

We want to connect with repmgr without password, that’s what the pgpass hidden file is for.

# on pg01 as user postgres
echo "*:*:repmgr:repmgr:rep123" > /home/postgres/.pgpass
echo "*:*:replication:repmgr:rep123" >> /home/postgres/.pgpass
chmod 600 /home/postgres/.pgpass
scp /home/postgres/.pgpass pg02.localnet:/home/postgres/.pgpass 
scp /home/postgres/.pgpass pg03.localnet:/home/postgres/.pgpass 

Add entries in $PGDATA/pg_hba.conf for repmgr

# on pg01 as user postgres
cat <<EOF >> $PGDATA/pg_hba.conf
# replication manager
local  replication   repmgr                      trust
host   replication   repmgr      127.0.0.1/32    trust
host   replication   repmgr      0.0.0.0/0       md5
local   repmgr        repmgr                     trust
host    repmgr        repmgr      127.0.0.1/32   trust
host    repmgr        repmgr      127.0.0.1/32   trust
host    repmgr        repmgr      0.0.0.0/0      md5
host    all           all         0.0.0.0/0      md5
EOF
sudo systemctl restart postgresql

create the repmgr configuration file

# on pg01 as user postgres
cat <<EOF > /etc/repmgr/11/repmgr.conf
node_id=1
node_name=pg01.localnet
conninfo='host=pg01.localnet dbname=repmgr user=repmgr password=rep123 connect_timeout=2'
data_directory='/u01/pg11/data'
use_replication_slots=yes
# event_notification_command='/opt/postgres/scripts/repmgrd_event.sh %n "%e" %s "%t" "%d" %p %c %a'
reconnect_attempts=10
reconnect_interval=1

restore_command = 'cp /u02/archive/%f %p'

log_facility=STDERR
failover=manual
monitor_interval_secs=5

pg_bindir='/usr/pgsql-11/bin'

service_start_command = 'sudo systemctl start postgresql'
service_stop_command = 'sudo systemctl stop postgresql'
service_restart_command = 'sudo systemctl restart postgresql'
service_reload_command = 'pg_ctl reload'

promote_command='repmgr -f /etc/repmgr/11/repmgr.conf standby promote'
follow_command='repmgr -f /etc/repmgr/11/repmgr.conf standby follow -W --upstream-node-id=%n'
EOF

Now register this database as being the primary database

# on pg01 as user postgres
repmgr -f /etc/repmgr/11/repmgr.conf -v master register

repmgr maintains information about the cluster topology in a table called nodes

psql -U repmgr repmgr -c "select * from nodes;"

We can now enable the systemd unit postgresql, so that postgres will be started on next boot

# on pg01
sudo systemctl enable postgresql

2.2. Standby database on pg02

Now we can go to the first standby, pg02, and set-it up as a standby

# on pg02 as user postgres
cat <<EOF > /etc/repmgr/11/repmgr.conf
node_id=2
node_name=pg02.localnet
conninfo='host=pg02.localnet dbname=repmgr user=repmgr password=rep123 connect_timeout=2'
data_directory='/u01/pg11/data'
use_replication_slots=yes
# event_notification_command='/opt/postgres/scripts/repmgrd_event.sh %n "%e" %s "%t" "%d" %p %c %a'
reconnect_attempts=10
reconnect_interval=1

restore_command = 'cp /u02/archive/%f %p'

log_facility=STDERR
failover=manual
monitor_interval_secs=5

pg_bindir='/usr/pgsql-11/bin'

service_start_command = 'sudo systemctl start postgresql'
service_stop_command = 'sudo systemctl stop postgresql'
service_restart_command = 'sudo systemctl restart postgresql'
service_reload_command = 'pg_ctl reload'

promote_command='repmgr -f /etc/repmgr/11/repmgr.conf standby promote'
follow_command='repmgr -f /etc/repmgr/11/repmgr.conf standby follow -W --upstream-node-id=%n'
EOF

let’s wipe out the database and the archived wal

# make sure postgres is not running !
sudo systemctl stop postgresql
# on pg02 as user postgres
rm -rf /u01/pg11/data/*
rm -rf /u02/archive/*

And now let’s set-up this server as a standby

repmgr -h pg01.localnet -U repmgr -d repmgr -D /u01/pg11/data -f /etc/repmgr/11/repmgr.conf standby clone
sudo systemctl start postgresql
repmgr -f /etc/repmgr/11/repmgr.conf standby register --force

if it does not work, check that you can connect to pg01.localnet (maybe you forgot the firewall ?): psql -h pg01.localnet -U repmgr repmgr should work

we can enable the unit postgresql so that postgres will start automatically on next reboot

sudo systemctl enable postgresql

The nodes table should now have two records

psql -U repmgr -c "select * from nodes;"

2.3. Standby database on pg03

Let us do the same on the third standby, pg03, and set-it up as a standby

# on pg03 as user postgres
cat <<EOF > /etc/repmgr/11/repmgr.conf
node_id=3
node_name=pg03.localnet
conninfo='host=pg03.localnet dbname=repmgr user=repmgr password=rep123 connect_timeout=2'
data_directory='/u01/pg11/data'
use_replication_slots=yes
# event_notification_command='/opt/postgres/scripts/repmgrd_event.sh %n "%e" %s "%t" "%d" %p %c %a'
reconnect_attempts=10
reconnect_interval=1

restore_command = 'cp /u02/archive/%f %p'

log_facility=STDERR
failover=manual
monitor_interval_secs=5

pg_bindir='/usr/pgsql-11/bin'

service_start_command = 'sudo systemctl start postgresql'
service_stop_command = 'sudo systemctl stop postgresql'
service_restart_command = 'sudo systemctl restart postgresql'
service_reload_command = 'pg_ctl reload'

promote_command='repmgr -f /etc/repmgr/11/repmgr.conf standby promote'
follow_command='repmgr -f /etc/repmgr/11/repmgr.conf standby follow -W --upstream-node-id=%n'
EOF

let’s wipe out the database and the archived wal

# make sure postgres is not running !
sudo systemctl stop postgresql
# on pg03 as user postgres
rm -rf /u01/pg11/data/*
rm -rf /u02/archive/*

And now let’s set-up this server as a standby

repmgr -h pg01.localnet -U repmgr -d repmgr -D /u01/pg11/data -f /etc/repmgr/11/repmgr.conf standby clone
sudo systemctl start postgresql
repmgr -f /etc/repmgr/11/repmgr.conf standby register --force

we can enable the unit postgresql so that postgres will start automatically on next reboot

sudo systemctl enable postgresql

Check the repmgr tables nodes

psql -U repmgr repmgr -c "select * from nodes;"

2.4. Test streaming replication

# on pg01 as user postgres
# check the nodes table, it contains meta-data for repmgr 
psql -U repmgr repmgr -c "select * from nodes;"
# create a test table
psql -U repmgr repmgr -c "create table test(c1 int, message varchar(120)); insert into test values(1,'this is a test');"
# check on pg02 if I can see the table and if it is read-only
psql -h pg02.localnet -U repmgr repmgr <<EOF
select * from test;
drop table test;
EOF
# it will say: ERROR:  cannot execute DROP TABLE in a read-only transaction
# check on pg03 if I can see the table and if it is read-only
psql -h pg03.localnet -U repmgr repmgr <<EOF
select * from test;
drop table test;
EOF
# drop the table on pg01
psql -U repmgr repmgr -c "drop table test;"

Experiment a bit with basis operations: failover, switch-over, following a new master, rejoining a failed primary, etc. repmgr has scripts for all of that.

3. pgpool

Now it is time to configure pgpool on our 3 servers. pgpool can be used as a connection cache but - more importantly - it is used to automate the failover of postgres and to make the failover (almost) transparent to client applications.

Remember that postgres is made HA (High Available) via the streaming replication and that pgpool is made HA via a Virtual IP (VIP) managed by the watchdog mode.

The following line muse be added to the postgres config (not sure why and if it is still needed)

echo "pgpool.pg_ctl='/usr/pgsql-11/bin/pg_ctl'" >> $PGDATA/conf.d/custom.conf

3.1. pgool_hba and pool_passwd files

similar to the postgres host based authentification mechanism (pg_hba), pgpool has a pool_hba.conf file.

cat <<EOF > /etc/pgpool-II/pool_hba.conf
local   all         all                               trust
# IPv4 local connections:
host     all         all         0.0.0.0/0             md5
EOF
scp /etc/pgpool-II/pool_hba.conf postgres@pg02.localnet:/etc/pgpool-II/
scp /etc/pgpool-II/pool_hba.conf postgres@pg03.localnet:/etc/pgpool-II/

Because I use md5, I will need to have the file pool_passwd containing the md5 hashed password http://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-POOL-PASSWD

# first dump the info into a temp file
psql -c "select rolname,rolpassword from pg_authid;" > /tmp/users.tmp
touch /etc/pgpool-II/pool_passwd
# then go through the file to remove/add the entry in pool_passwd file
cat /tmp/users.tmp | awk 'BEGIN {FS="|"}{print $1" "$2}' | grep md5 | while read f1 f2
do
 echo "setting passwd of $f1 in /etc/pgpool-II/pool_passwd"
 # delete the line if exits
 sed -i -e "/^${f1}:/d" /etc/pgpool-II/pool_passwd
 echo $f1:$f2 >> /etc/pgpool-II/pool_passwd
done
scp /etc/pgpool-II/pool_passwd pg02:/etc/pgpool-II/pool_passwd
scp /etc/pgpool-II/pool_passwd pg03:/etc/pgpool-II/pool_passwd

⚠️ Everytime a new postgres user is added or her password is changed this small procedure must be re-ran

3.2. Setting up pcp.conf file

pcp (pgpool control protocol) is an administrative interface to pgpool, it enables you to interact with pgpool via port 9898 (default). The file pcp.conf stores a username and a md5 password to authentificate, the file .pcppass enables a user to use pcp commands without password. Note that this user/password is not related to a postgres user, it is just a pcp user that can speak with pgpool via the pcp protocol. http://www.pgpool.net/docs/latest/en/html/configuring-pcp-conf.html

I will use the user postgres with password secret

# user postgres on pg01
echo "postgres:$(pg_md5 secret)" >> /etc/pgpool-II/pcp.conf
scp /etc/pgpool-II/pcp.conf pg02:/etc/pgpool-II/pcp.conf
scp /etc/pgpool-II/pcp.conf pg03:/etc/pgpool-II/pcp.conf
echo "*:*:postgres:secret" > /home/postgres/.pcppass 
chown postgres:postgres /home/postgres/.pcppass 
chmod 600 /home/postgres/.pcppass
scp /home/postgres/.pcppass pg02:/home/postgres/.pcppass
scp /home/postgres/.pcppass pg03:/home/postgres/.pcppass

because of this .pcppass we will be able to use pcp without password when logged in as unix user postgres.

In watchdog mode, pgpool will need to execute the ip and the arping commands with user postgres, so we set the sticky bit on those two utilities.

chmod 4755 /usr/sbin/ip /usr/sbin/arping

3.3. Pgpool configuration file: pgpool.conf

The 3 servers used in the config are pg01.localnet, pg02.localnet and pg03.localnet. We also need a VIP (virtual IP, also called delegate_ip in the context of pgpool). In my case the VIP will be 192.168.122.50.

This is the config I use for the first server, I will explain it below. Take care that because I do a cat redirected to the config, I have to escape the dollar signs in the config. Pay attention to if_up_cmd below for example, I escaped the $ but in the file it must of course not be escaped.

# as user postgres on pg01
CONFIG_FILE=/etc/pgpool-II/pgpool.conf
cat <<EOF > $CONFIG_FILE
listen_addresses = '*'
port = 9999
socket_dir = '/var/run/pgpool'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/pgpool'
listen_backlog_multiplier = 2
serialize_accept = off
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off
num_init_children = 100
max_pool = 5
# - Life time -
child_life_time = 300
child_max_connections = 0
connection_life_time = 600
client_idle_limit = 0

log_destination='stderr'
debug_level = 0

pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'

connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
replication_mode = off
load_balance_mode = off
master_slave_mode = on
master_slave_sub_mode = 'stream'

backend_hostname0 = 'pg01.localnet'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/u01/pg11/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'pg02.localnet'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/u01/pg11/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'pg03.localnet'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/u01/pg11/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

# this is about checking the postgres streaming replication
sr_check_period = 10
sr_check_user = 'repmgr'
sr_check_password = 'rep123'
sr_check_database = 'repmgr'
delay_threshold = 10000000

# this is about automatic failover
failover_command = '/opt/pgpool/scripts/failover.sh  %d %h %P %m %H %R'
# not used, just echo something
failback_command = 'echo failback %d %h %p %D %m %H %M %P'
failover_on_backend_error = 'off'
search_primary_node_timeout = 300
# Mandatory in a 3 nodes set-up
follow_master_command = '/opt/pgpool/scripts/follow_master.sh %d %h %m %p %H %M %P'

# grace period before triggering a failover
health_check_period = 40
health_check_timeout = 10
health_check_user = 'hcuser'
health_check_password = 'hcuser'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 1
connect_timeout = 10000

#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
recovery_user = 'repmgr'
recovery_password = 'rep123'
recovery_1st_stage_command = 'pgpool_recovery.sh'
recovery_2nd_stage_command = 'echo recovery_2nd_stage_command'
recovery_timeout = 90
client_idle_limit_in_recovery = 0

#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = on
# trusted_servers = 'www.google.com,pg02.localnet,pg03.localnet' (not needed with a 3 nodes cluster ?)
ping_path = '/bin'

wd_hostname = pg01.localnet
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/var/run/pgpool'

delegate_IP = '192.168.122.99'
if_cmd_path = '/opt/pgpool/scripts'
if_up_cmd = 'ip_w.sh addr add \$_IP_\$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip_w.sh addr del \$_IP_\$/24 dev eth0'
arping_path = '/opt/pgpool/scripts'
arping_cmd = 'arping_w.sh -U \$_IP_\$ -I eth0 -w 1'
# - Behaivor on escalation Setting -

heartbeat_destination0 = 'pg01.localnet'
heartbeat_destination_port0 = 9694
heartbeat_destination1 = 'pg02.localnet'
heartbeat_destination_port1 = 9694
heartbeat_destination2 = 'pg03.localnet'
heartbeat_destination_port2 = 9694

other_pgpool_hostname0 = 'pg02.localnet'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

other_pgpool_hostname1 = 'pg03.localnet'
other_pgpool_port1 = 9999
other_wd_port1 = 9000
EOF

Let’s copy the file to pg02.localnet and pg03.localnet and make the necessary adaptations in the sections

scp /etc/pgpool-II/pgpool.conf pg02.localnet:/etc/pgpool-II/pgpool.conf

on pg02.localnet, changes

wd_hostname = pg02.localnet

other_pgpool_hostname0 = 'pg01.localnet'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

other_pgpool_hostname1 = 'pg03.localnet'
other_pgpool_port1 = 9999
other_wd_port1 = 9000

And the same to pg03.localnet

⚠️ when there is an issue with the watchdog of pgpool the reason is very often that the config is not correct, for example one forgot to adapt the other_pgpool_hostname section on each node.

3.4. Some pgpool parameters explained:

  • params related to streaming replication.

I prefer to set load_balance to ‘off’ but it might be a useful feature in some cases. By setting master_slave_mode on and sub mode to stream we are telling pgpool that we use standard postgres streaming replication mechanism.

load_balance_mode = off
master_slave_mode = on
master_slave_sub_mode = 'stream'

The backend section describes the 3 postgres

backend_hostname0 = 'pg01.localnet'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/u01/pg11/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
etc... for pg02 and pg03
  • num_init_children and max_pool relate to the connection caching functionality of pgpool.

When pgpool starts it will create “num_init_children” client processes that will all be in a state “waiting for a connection”. When a client application connects to pgpool, pgpool will use one of the child process that is free and establish a connection to postgres. When the client disconnects then pgpool will keep the connection to postgres open (connection caching) so that next time a client connect to pgpool the connection to postgres can be re-used and we avoid the cost of creating a new connection. We can disable this connection caching if we prefer to have pooling at the application level.

  • parameters related to automatic failover

failover_command is the script that will be executed when pgpool detects (via health check mechanism in my case) that the primary database is down. I don’t know what the failback_command is supposed to do, I just don’t use it. The follow_master_command is executed on all standby nodes after the failover_command was executed and a new primary database has been detected. I wil show the corresponding scripts later in this document.

failover_command = '/opt/pgpool/scripts/failover.sh  %d %h %P %m %H %R'
failback_command = 'echo failback %d %h %p %D %m %H %M %P'
search_primary_node_timeout = 300
follow_master_command = '/opt/pgpool/scripts/follow_master.sh %d %h %m %p %H %M %P'

The first reason why people need pgpool is because pgpool can automate the failover and make it transparent to client applications. There are basically two mechanisms to trigger a failover. Either via failover_on_backend_error or via the health checks. I prefer to set failover_on_backend_error to off and to use the health check mechanism to trigger the failover. The health check mechanism means that pgpool will regularly connect to postgres, if a connection fails (either to a primary or to a standby) then after the number of retries it will trigger the failover.

failover_on_backend_error = 'off'
health_check_period = 40
health_check_timeout = 10
health_check_user = 'hcuser'
health_check_password = 'hcuser'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 1

If you set failover_on_backend_error to ‘on’, then pgpool will trigger the failover when one of the child process detects that postgres is terminated. In this case there is no retry. Note also that in this case, as long as no application connect to pgpool or perform a query on the database, no failover will be triggered.

Do not confuse the health check with the streaming replication check (the streaming replication check look at the delay in replication, if the delay is too big a standby will ne be used for read load balancing and will not be considered for promotion). For some strange reason pgpool uses two different users for these.

  • parameters related to watch-dog

The watch-dog mechanism is a very cool functionality of pgpool: multiple instances of pgpool (3 in our case) are in an active-passive set-up, on startup the cluster elects one leader and the leader acquires the VIP (delegate_ip). The 3 nodes are monitoring each other and if the primary fails then a new leader will be elected and the VIP will be moved to this new leader.

Only the leader pgpool node will execute the failover, follow_master, etc. commands

The watchdog mode must be enabled via use_watchdog (‘on’)

The nodes monitor each other via the heartbeat mechanism, sending a packet on UDP port 9694 to the other nodes

heartbeat_destination0 = 'pg01.localnet'
heartbeat_destination_port0 = 9694
heartbeat_destination1 = 'pg02.localnet'
heartbeat_destination_port1 = 9694
heartbeat_destination2 = 'pg03.localnet'
heartbeat_destination_port2 = 9694

The VIP is acquired via a script that must be installed on the host.

if_cmd_path = '/opt/pgpool/scripts'
if_up_cmd = 'ip_w.sh addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip_w.sh addr del $_IP_$/24 dev eth0'
arping_path = '/opt/pgpool/scripts'
arping_cmd = 'arping_w.sh -U $_IP_$ -I eth0 -w 1'

In the command above, pgpool will replace $IP$ with the value of the delegate_ip parameter

delegate_ip = 192.168.122.99

each node must be aware of the others, so on pg01

wd_hostname = pg01
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/var/run/pgpool'

other_pgpool_hostname0 = 'pg02.localnet'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

other_pgpool_hostname1 = 'pg03.localnet'
other_pgpool_port1 = 9999
other_wd_port1 = 9000
  • log destination

I use the default log_destination = ‘stderr’ and I look at the logs via journalctl

sudo journalctl --unit pgpool

3.5. pgpool preparation

we need to create the postgres user for the health check

# on pg01 as user postgres
psql -c "create user hcuser with login password 'hcuser';"

We need to open a few firewall ports, for pgpool (9999 and 9898 for pcp) and for the watch-dog functionality

# on all 3 servers
# port for postgres (was already done)
sudo firewall-cmd --add-port 9898/tcp --permanent
# ports for pgpool and for pcp
sudo firewall-cmd --add-port 9898/tcp --permanent
sudo firewall-cmd --add-port 9999/tcp --permanent
sudo firewall-cmd --add-port 9000/tcp --permanent
sudo firewall-cmd --add-port 9694/udp --permanent
sudo systemctl restart firewalld

Directory for pid file

# on all servers
sudo chown postgres:postgres /var/run/pgpool
sudo mkdir /var/log/pgpool
sudo chown postgres:postgres /var/log/pgpool

pgpool scripts will logs into /var/log/pgpool.

If you want also logs of pgpool itself, then use as log_destination = ‘stderr,syslog’ and set syslog_facility to LOCAL1, and configure syslog. Add to /etc/rsyslog.conf

local1.*    /var/log/pgpool/pgpool.log

then restart the daemon

sudo systemctl restart rsyslog

nb: not sure this is working though, not tested.

3.5. Pgpool Scripts

sudo mkdir -p /opt/pgpool/scripts
sudo chown postgres:postgres /opt/pgpool/scripts
  • failover script

Copy and paste the following in /opt/pgpoo/scripts/failover.sh

#!/bin/bash

LOGFILE=/var/log/pgpool/failover.log
if [ ! -f $LOGFILE ] ; then
 > $LOGFILE
fi
# we need this, otherwise it is not set
PGVER=${PGVER:-11}

#
#failover_command = '/scripts/failover.sh  %d %h %P %m %H %R'
               # Executes this command at failover
               # Special values:
                    #   %d = node id
                    #   %h = host name
                    #   %p = port number
                    #   %D = database cluster path
                    #   %m = new master node id
                    #   %H = hostname of the new master node
                    #   %M = old master node id
                    #   %P = old primary node id
#

FALLING_NODE=$1            # %d
FALLING_HOST=$2            # %h
OLD_PRIMARY_ID=$3          # %P
NEW_PRIMARY_ID=$4          # %m
NEW_PRIMARY_HOST=$5        # %H
NEW_MASTER_PGDATA=$6       # %R
(
date
echo "FALLING_NODE: $FALLING_NODE"
echo "FALLING_HOST: $FALLING_HOST"
echo "OLD_PRIMARY_ID: $OLD_PRIMARY_ID"
echo "NEW_PRIMARY_ID: $NEW_PRIMARY_ID"
echo "NEW_PRIMARY_HOST: $NEW_PRIMARY_HOST"
echo "NEW_MASTER_PGDATA: $NEW_MASTER_PGDATA"

ssh_options="ssh -p 22 -n -T -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no"
set -x

if [ $FALLING_NODE = $OLD_PRIMARY_ID ] ; then
  $ssh_options postgres@${NEW_PRIMARY_HOST} "/usr/pgsql-${PGVER}/bin/repmgr --log-to-file -f /etc/repmgr/${PGVER}/repmgr.conf standby promote -v "
fi
exit 0;

make it executable

  • follow_master

Copy the following in /opt/pgpool/scripts/follow_master.sh

#!/bin/bash
LOGFILE=/var/log/pgpool/follow_master.log
if [ ! -f $LOGFILE ] ; then
 > $LOGFILE
fi
PGVER=${PGVER:-11}

echo "executing follow_master.sh at `date`"  | tee -a $LOGFILE

NODEID=$1
HOSTNAME=$2
NEW_MASTER_ID=$3
PORT_NUMBER=$4
NEW_MASTER_HOST=$5
OLD_MASTER_ID=$6
OLD_PRIMARY_ID=$7
PGDATA=${PGDATA:-/u01/pg${PGVER}/data}
(
echo NODEID=${NODEID}
echo HOSTNAME=${HOSTNAME}
echo NEW_MASTER_ID=${NEW_MASTER_ID}
echo PORT_NUMBER=${PORT_NUMBER}
echo NEW_MASTER_HOST=${NEW_MASTER_HOST}
echo OLD_MASTER_ID=${OLD_MASTER_ID}
echo OLD_PRIMARY_ID=${OLD_PRIMARY_ID}
echo PGDATA=${PGDATA}
if [ $NODEID -eq $OLD_PRIMARY_ID ] ; then
  echo "Do nothing as this is the failed master. We could prevent failed master to restart here, so that we can investigate the issue" | tee -a $LOGFILE
else
  ssh_options="ssh -p 22 -n -T -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no"
  set -x
  # if this node is not currently standby then it might be an old master that went back up after a failover occured
  # if this is the case we cannot do the follow master command on this node, we should leave it alone
  in_reco=$( $ssh_options postgres@${HOSTNAME} 'psql -t -c "select pg_is_in_recovery();"' | head -1 | awk '{print $1}' )
  echo "pg_is_in_recovery on $HOSTNAME is $in_reco " | tee -a $LOGFILE
  if [ "a${in_reco}" != "at" ] ; then
    echo "node $HOSTNAME is not in recovery, probably a degenerated master, skip it" | tee -a $LOGFILE
    exit 0
  fi
  $ssh_options postgres@${HOSTNAME} "/usr/pgsql-${PGVER}/bin/repmgr --log-to-file -f /etc/repmgr/${PGVER}/repmgr.conf -h ${NEW_MASTER_HOST} -D ${PGDATA} -U repmgr -d repmgr standby follow -v "
  # TODO: we should check if the standby follow worked or not, if not we should then do a standby clone command
  echo "Sleep 10"
  sleep 10
  echo "Attach node ${NODEID}"
  pcp_attach_node -h localhost -p 9898 -w ${NODEID}
fi
) 2>&1 | tee -a $LOGFILE
  • pgpool_recovery script

the script pgpool_recovery.sh must be installed in $PGDATA, must be owned by postgres and have execute permission. This script will be called via the postgres extension pgpool_recovery, so we must first install this extension.

# on the primary db only
psql -c "create extension pgpool_recovery;" -d template1
psql -c "create extension pgpool_adm;"

pgpool_adm is another extension that you may want to install

create the file $PGDATA/pgpool_recovery.sh (in my case $PGDATA is /u01/pg11/data). I also install this script in /opt/pgpool/scripts. to keep a copy.

#!/bin/bash

# This script erase an existing replica and re-base it based on
# the current primary node. Parameters are position-based and include:
#
# 1 - Path to primary database directory.
# 2 - Host name of new node.
# 3 - Path to replica database directory
#
# Be sure to set up public SSH keys and authorized_keys files.
# this script must be in PGDATA

PGVER=${PGVER:-11}
ARCHIVE_DIR=/u02/archive

LOGFILE=/var/log/pgool/pgpool_recovery.log
if [ ! -f $LOGFILE ] ; then
 touch $LOGFILE
fi

log_info(){
 echo $( date +"%Y-%m-%d %H:%M:%S.%6N" ) - INFO - $1 | tee -a $LOGFILE
}

log_error(){
 echo $( date +"%Y-%m-%d %H:%M:%S.%6N" ) - ERROR - $1 | tee -a $LOGFILE
}

log_info "executing pgpool_recovery at `date` on `hostname`"


PATH=$PATH:/usr/pgsql-${PGVER}/bin

if [ $# -lt 3 ]; then
    echo "Create a replica PostgreSQL from the primary within pgpool."
    echo
    echo "Usage: $0 PRIMARY_PATH HOST_NAME COPY_PATH"
    echo
    exit 1
fi
# to do is hostname -i always OK ? Find other way to extract the host. maybe from repmgr.conf ?
primary_host=$(hostname -i) # not working on SCM
replica_host=$2
replica_path=$3
log_info "primary_host: ${primary_host}"
log_info "replica_host: ${replica_host}"
log_info "replica_path: ${replica_path}"
ssh_copy="ssh -p 22 postgres@$replica_host -T -n -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no"
log_info "Stopping postgres on ${replica_host}"
$ssh_copy "sudo systemctl stop postgresql"
log_info sleeping 10
sleep 10
log_info "delete database directory on ${replica_host}"
$ssh_copy "rm -Rf $replica_path/* $ARCHIVE_DIR/*"
log_info "let us use repmgr on the replica host to force it to sync again"
$ssh_copy "/usr/pgsql-${PGVER}/bin/repmgr -h ${primary_host} --username=repmgr -d repmgr -D ${replica_path} -f /etc/repmgr/${PGVER}/repmgr.conf standby clone -v"
log_info "Start database on ${replica_host} "
$ssh_copy "sudo systemctl start postgresql"
log_info sleeping 20
sleep 20
log_info "Register standby database"
$ssh_copy "/usr/pgsql-${PGVER}/bin/repmgr -f /etc/repmgr/${PGVER}/repmgr.conf standby register -F -v"

Do not forget to install it in $PGDATA and to set execute permission

chmod 774 /opt/pgpool/scripts/pgpool_recovery.sh
cp /opt/pgpool/scripts/pgpool_recovery.sh $PGDATA/pgpool_recovery.sh
scp /opt/pgpool/scripts/pgpool_recovery.sh pg02.localnet:$PGDATA/
scp /opt/pgpool/scripts/pgpool_recovery.sh pg03.localnet:$PGDATA/

for the watchdog mode, we need two additional scripts

  • ip scripts

Create the file /opt/pgool/scripts/ip_w.sh

#!/bin/bash
echo "Exec ip with params $@ at `date`"
sudo /usr/sbin/ip $@
exit $?

Create the file /opt/pgpool/scripts/arping_w.sh

#!/bin/bash
echo "Exec arping with params $@ at `date`"
sudo /usr/sbin/arping $@
exit $?

Now set execute mode on all scripts and copy them over to pg02 and pg03

chmod 774 /opt/pgpool/scripts/*
scp /opt/pgpool/scripts/* pg02.localnet:/opt/pgpool/scripts/
scp /opt/pgpool/scripts/* pg03.localnet:/opt/pgpool/scripts/

4. Test the set-up

Before starting pgpool on nodes pg01.localnet, pg02.localnet and pg03.localnet let’s do final checks

  • check streaming replication
# on pg01
 repmgr -f /etc/repmgr/11/repmgr.conf cluster show
  • enable postgressql and pgpool services on all servers
sudo systemctl enable postgresql
sudo systemctl enable pgpool
  • check firewall on all servers
sudo firewall-cmd --list-all
  • check ssh connectivity between all servers

remember that pg01 will connect to itself via ssh also, and pg02/3 also

start pgpool on pg01.localnet, pg02.localnet and pg03.localnet

sudo systemctl start pgpool

as soon as there is a quorum (two nodes), pgpool will elect a leader and it will acquire the VIP

Since pgpool is logging to stderr, we can see the logs with journalctl

sudo journalctl --unit pgpool -f

Have a good look at the logs, it is a bit verbose but very relevant. Once you start pgpool on pg02.localnet and pg03.localnet, you should see in the log that a master was elected and that the VIP was acquired. The VIP will be on the master node, in my case pg01.localnet (it started first)

get info about the pgpool cluster

pcp_watchdog_info -h 192.168.122.99 -p 9898 -w -v

the -w flag is to avoid password prompt, it requires to have the pcp password for postgres in /home/postgres/.pcppass (the password md5 is defined in /etc/pgpool-II/pcp.conf)

# result of ip addr
[postgres@pg01 pgpool-II]$ ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:00:b5:58:ec brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.10/24 brd 192.168.122.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.122.99/24 scope global secondary eth0:0
       valid_lft forever preferred_lft forever
    inet6 fe80::5054:ff:feb5:58ec/64 scope link
       valid_lft forever preferred_lft forever

the important info is that IP 192.168.122.99 was set-up on eth0 with label eth0:0

4.1. failover pgpool

before testing the failover, make sure that all nodes can connect to each other via ssh without a password prompt

# on pg01, pg02 and pg03
ssh -p 22 postgres@pg01.localnet -T -n -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no "repmgr -f /etc/repmgr/${PGVER}/repmgr.conf --help"
ssh -p 22 postgres@pg02.localnet -T -n -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no "repmgr -f /etc/repmgr/${PGVER}/repmgr.conf --help"
ssh -p 22 postgres@pg03.localnet -T -n -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no "repmgr -f /etc/repmgr/${PGVER}/repmgr.conf --help"

to test a failover of pgpool we need to stop pgpool on the master and see if a new master is elected and if the VIP is moved to the new master. Follow the logs during this operation because it gives all relevant information.

sudo systemctl stop pgpool

Check that the VIP was moved.

restart pgpool

sudo systemctl start pgpool

4.2. failover postgres

Let’s see the state of pgpool

psql -h 192.168.122.99 -p 9999 -U repmgr -c "show pool_nodes;"

let’s see also the replication stats on the primary and on the standby’s

# pg01 is the primary
psql -h pg01.localnet -p 5432 -U repmgr -c "select * from pg_stat_replication;"
psql -h pg02.localnet -p 5432 -U repmgr -c "select * from pg_stat_wal_receiver;"
psql -h pg03.localnet -p 5432 -U repmgr -c "select * from pg_stat_wal_receiver;"

since pg01.localnet is the primary, I will stop it and pgpool should promote pg02 to primary (failover script) and pg03 should now follow the new primary.

# on pg01
sudo systemctl stop postgresql

Check again pgpool status

psql -h 192.168.122.99 -p 9999 -U repmgr -c "show pool_nodes;"

check the log files failover.log and follow_master.log in /var/log/pgool on the pgpool master node

check with select * from pg_stat_wal_receiver; that the second stand-by follows the new primary and check with select * from pg_stat_replication; that the primary streams to the standby

4.3. recover failed node

There are different ways to rebase the failed primary as a standby

  • using repmgr

assuming the new primary is pg02.localnet, executing the following so that pg01.localnet rejoin the cluster as a standby. Note that it will not always work

repmgr node rejoin -d 'host=pg02.localnet dbname=repmgr user=repmgr password=rep123 connect_timeout=2'

and then re-attach the node to pgpool

pcp_attach_node -h 192.168.122.99 -p 9898 -w 0

But the safest way is to rebuild it, using pcp_recovery_node. pgpool will use the parameters recovery_user and recovery_password to connect to the database and execute “select pg_recovery(…)”. The function pgpool_recovery was created when we installed the pgpool extension.

In our case we had

recovery_user = 'repmgr'
recovery_password = 'rep123'
pcp_recovery_node -h 192.168.122.99 -p 9898 -w 0

and then re-attach the node

pcp_attach_node -h 192.168.122.99 -p 9898 -w 0

4.4. Automatic recovery of a failed master or standby

In my company they wanted that a failed master (or a failed standby) that comes back to life would be automatically put back in the cluster as a standby. For example assuming this scenario:

  • pg01 is the primary
  • the server pg01 is powered off
  • pg02 becomes the primary, pg03 follows pg02
  • the server pg01 is powered on
  • postgres on pg01 comes back, it is in read-write mode, however because it is detached from pgpool we are protected from a split-brain

we want that pg01 is automatically recoverd and becomes a standby of pg02 and rejoin the pgpool cluster

Another scenario is when a standby server is rebooted: pgpool will detach the standby database but when the server comes back pgpool will not re-attach it.

My solution was to schedule a script via cron. The script is the following. Take care that it must be adapted on pg02 and on pg03, the variable PG_NODE_ID is 0 on pg01, 1 on pg02 and 2 on pg03. Adapt also the var DELEGATE_IP (192.168.122.99 for me)

# content of /opt/postgres/scripts/recover_failed_node.sh (can be scheduled via cron)
#!/bin/bash
# this script will re-attach a failed standby database
# or recover a failed primary database
# it requires that pgpool is available and that the database on this node is running
# this script might be called when the postgres container is starting but then it must do so
# when both pgpool and the database is running. Since the db is started with supervisor, this would
# require to lauch the script in the background after the start of postgres
# the script can also be started manually or via cron

# Created by argbash-init v2.6.1
# ARG_OPTIONAL_BOOLEAN([auto-recover-standby],[],[reattach a standby to pgpool if possible],[on])
# ARG_OPTIONAL_BOOLEAN([auto-recover-primary],[],[recover the degenerated master],[off])
# ARG_OPTIONAL_SINGLE([lock-timeout-minutes],[],[minutes after which a lock will be ignored (optional)],[120])
# ARG_HELP([<The general help message of my script>])
# ARGBASH_GO()
# needed because of Argbash --> m4_ignore([
### START OF CODE GENERATED BY Argbash v2.6.1 one line above ###
# Argbash is a bash code generator used to get arguments parsing right.
# Argbash is FREE SOFTWARE, see https://argbash.io for more info

die()
{
	local _ret=$2
	test -n "$_ret" || _ret=1
	test "$_PRINT_HELP" = yes && print_help >&2
	echo "$1" >&2
	exit ${_ret}
}

begins_with_short_option()
{
	local first_option all_short_options
	all_short_options='h'
	first_option="${1:0:1}"
	test "$all_short_options" = "${all_short_options/$first_option/}" && return 1 || return 0
}



# THE DEFAULTS INITIALIZATION - OPTIONALS
_arg_auto_recover_standby="on"
_arg_auto_recover_primary="off"
_arg_lock_timeout_minutes="120"

print_help ()
{
	printf '%s\n' "<The general help message of my script>"
	printf 'Usage: %s [--(no-)auto-recover-standby] [--(no-)auto-recover-primary] [--lock-timeout-minutes <arg>] [-h|--help]\n' "$0"
	printf '\t%s\n' "--auto-recover-standby,--no-auto-recover-standby: reattach a standby to pgpool if possible (on by default)"
	printf '\t%s\n' "--auto-recover-primary,--no-auto-recover-primary: recover the degenerated master (off by default)"
	printf '\t%s\n' "--lock-timeout-minutes: minutes after which a lock will be ignored (optional) (default: '120')"
	printf '\t%s\n' "-h,--help: Prints help"
}

parse_commandline ()
{
	while test $# -gt 0
	do
		_key="$1"
		case "$_key" in
			--no-auto-recover-standby|--auto-recover-standby)
				_arg_auto_recover_standby="on"
				test "${1:0:5}" = "--no-" && _arg_auto_recover_standby="off"
				;;
			--no-auto-recover-primary|--auto-recover-primary)
				_arg_auto_recover_primary="on"
				test "${1:0:5}" = "--no-" && _arg_auto_recover_primary="off"
				;;
			--lock-timeout-minutes)
				test $# -lt 2 && die "Missing value for the optional argument '$_key'." 1
				_arg_lock_timeout_minutes="$2"
				shift
				;;
			--lock-timeout-minutes=*)
				_arg_lock_timeout_minutes="${_key##--lock-timeout-minutes=}"
				;;
			-h|--help)
				print_help
				exit 0
				;;
			-h*)
				print_help
				exit 0
				;;
			*)
				_PRINT_HELP=yes die "FATAL ERROR: Got an unexpected argument '$1'" 1
				;;
		esac
		shift
	done
}

parse_commandline "$@"

# OTHER STUFF GENERATED BY Argbash

### END OF CODE GENERATED BY Argbash (sortof) ### ])
# [ <-- needed because of Argbash


printf "'%s' is %s\\n" 'auto-recover-standby' "$_arg_auto_recover_standby"
printf "'%s' is %s\\n" 'auto-recover-primary' "$_arg_auto_recover_primary"
printf "'%s' is %s\\n" 'lock-timeout-minutes' "$_arg_lock_timeout_minutes"

PIDFILE=/home/postgres/recover_failed_node.pid

trap cleanup EXIT

DELEGATE_IP=192.168.122.99
### adapt on each node ###
PGP_NODE_ID=0
PGP_STATUS_WAITING=1
PGP_STATUS_UP=2
PGP_STATUS_DOWN=3

LOGFILENAME="${PROGNAME%.*}.log"

LOGFILE=/var/log/postgres/${LOGFILENAME}

if [ ! -f $LOGFILE ] ; then
 touch $LOGFILE
fi

log_info(){
 echo $( date +"%Y-%m-%d %H:%M:%S.%6N" ) - INFO - $1 | tee -a $LOGFILE
}

log_error(){
 echo $( date +"%Y-%m-%d %H:%M:%S.%6N" ) - ERROR - $1 | tee -a $LOGFILE
}

cleanup(){
  # remove pid file but only if it is mine, dont remove if another process was running
  if [ -f $PIDFILE ] ; then
    MYPID=$$
    STOREDPID=$(cat $PIDFILE)
    if [ "${MYPID}" == "${STOREDPID}" ] ; then
      rm -f $PIDFILE
    fi
  fi 
  if [ -z $INSERTED_ID ] ; then
    return
  fi
  log_info "delete from recover_failed with id $INSERTED_ID"
  psql -U repmgr -h $DELEGATE_IP -p 9999 repmgr -t -c "delete from recover_failed_lock where id=${INSERTED_ID};"
}

# test if there is lock in the recover_failed_lock table
# return 0 if there is no lock, 1 if there is one
is_recovery_locked(){
  # create table if not exists
  psql -U repmgr -h $DELEGATE_IP -p 9999 repmgr -c "create table if not exists recover_failed_lock(id serial,ts timestamp with time zone default current_timestamp,node varchar(10) not null,message varchar(120));"
  #clean-up old records
  psql -U repmgr -h $DELEGATE_IP -p 9999 repmgr -c "delete from recover_failed_lock where ts < current_timestamp - INTERVAL '1 day';"
  # check if there is already an operation in progress
  str=$(psql -U repmgr -h $DELEGATE_IP -p 9999 repmgr -c "select ts,node from recover_failed_lock where ts > current_timestamp - INTERVAL '${_arg_lock_timeout_minutes} min';")
  if [ $? -ne 0 ] ; then
    log_error "psql error when selecting from recover_failed_lock table"
    exit 1
  fi 
  echo $str | grep "(0 rows)"
  if [ $? -eq 0 ] ; then
    return 0
  fi
  log_info "there is a lock record in recover_failed_lock : $str"
  return 1
}

# take a lock on the the recovery operation
# by inserting a record in table recover_failed_lock
# fails if there is already a recovery running (if an old record still exist)
# exit -1 : error 
#  return 0: cannot acquire a lock because an operation is already in progress
  
lock_recovery(){
  MSG=$1
  # create table if not exists
  psql -U repmgr -h $DELEGATE_IP -p 9999 repmgr -c "create table if not exists recover_failed_lock(id serial,ts timestamp with time zone default current_timestamp,node varchar(10) not null,message varchar(120));"
  if [ $? -ne 0 ] ; then
    log_error "Cannot create table recover_failed_lock table"
    exit 1
  fi
  is_recovery_locked
  if [ $? -eq 1 ] ; then
    return 0
  fi
  str=$(psql -U repmgr -h $DELEGATE_IP -p 9999 repmgr -t -c "insert into recover_failed_lock (node,message) values ('${NODE_NAME}','${MSG}') returning id;")
  if [ $? -ne 0 ] ; then
    log_info "cannot insert into recover_failed_lock"
    exit 1
  fi
  INSERTED_ID=$(echo $str | awk '{print $1}')
  log_info "inserted lock in recover_failed_log with id $INSERTED_ID"
  return $INSERTED_ID
}


pg_is_in_recovery(){
  psql -t -c "select pg_is_in_recovery();" | head -1 | awk '{print $1}'
}

check_is_streaming_from(){
  PRIMARY=$1
  # first check if is_pg_in_recovery is t
  in_reco=$( pg_is_in_recovery )
  if [ "a${in_reco}" != "at" ] ; then
    return 0
  fi
  psql -t -c "select * from pg_stat_wal_receiver;" > /tmp/stat_wal_receiver.tmp
  # check that status is streamin
  status=$( cat /tmp/stat_wal_receiver.tmp | head -1 | cut -f2 -d"|" | sed -e "s/ //g" )
  if [ "a${status}" != "astreaming" ] ; then
    log_info "status is not streaming"
    return 0
  fi
  #check that is recovering from primary
  conninfo=$( cat /tmp/stat_wal_receiver.tmp | head -1 | cut -f14 -d"|" )
  echo $conninfo | grep "host=${PRIMARY}"
  if [ $? -eq 1 ] ; then
    log_info "not streaming from $PRIMARY"
    return 0
  fi
  return 1
}

# arg: 1 message
recover_failed_master(){
  # try to acquire a lock
  MSG=$1
  lock_recovery "$MSG"
  LOCK_ID=$?
  if [ ${LOCK_ID} -eq 0 ] ; then
    log_info "cannot acquire a lock, probably an old operation is in progress ?"
    return 99
  fi
  log_info "acquired lock $LOCK_ID"
  #echo "First try node rejoin"
  #echo "todo"
  log_info "Do pcp_recovery_node of $PGP_NODE_ID"
  pcp_recovery_node -h $DELEGATE_IP -p 9898 -w $PGP_NODE_ID
  ret=$?
  cleanup
  return $ret
}

recover_standby(){
  #dont do it if there is a lock on recover_failed_lock
  is_recovery_locked
  if [ $? -eq 1 ] ; then
    return 0
  fi
  if [ "$_arg_auto_recover_standby" == "on" ] ; then
    log_info "attach node back since it is in recovery streaming from $PRIMARY_NODE_ID"
    pcp_attach_node -h $DELEGATE_IP -p 9898 -w ${PGP_NODE_ID}
    if [ $? -eq 0 ] ; then
      log_info "OK attached node $node back since it is in recovery and streaming from $PRIMARY_NODE_ID"
      exit 0
    fi
    log_error "attach node failed for node $node"
    exit 1
  else
    log_info "auto_recover_standby is off, do nothing"
    exit 0
  fi
}

if [ -f $PIDFILE ] ; then
  PID=$(cat $PIDFILE)
  ps -p $PID > /dev/null 2>&1
  if [ $? -eq 0 ] ; then
    log_info "script already running with PID $PID"
    exit 0
  else
    log_info "PID file is there but script is not running, clean-up $PIDFILE"
    rm -f $PIDFILE
  fi
fi
echo $$ > $PIDFILE
if [ $? -ne 0 ] ; then
  log_error "Could not create PID file"
  exit 1
fi
log_info "Create $PIDFILE with value $$"

str=$( pcp_node_info -h $DELEGATE_IP -p 9898 -w $PGP_NODE_ID )
if [ $? -ne 0 ] ; then
  log_error "pgpool cannot be accessed"
  rm -f $PIDFILE
  exit 1
fi

read node port status weight status_name role date_status time_status <<< $str
if [ $status -ne $PGP_STATUS_DOWN ] ; then
  log_info "pgpool status for node $node is $status_name and role $role, nothing to do"
  rm -f $PIDFILE
  exit 0
fi
log_info "Node $node is down (role is $role)"
# status down, the node is detached
# get the primary from pool_nodes
psql -h $DELEGATE_IP -p 9999 -U repmgr -c "show pool_nodes;" > /tmp/pool_nodes.log
if [ $? -ne 0 ] ; then
  log_error "cannot connect to postgres via pgpool"
  rm -f $PIDFILE
  exit 1
fi
PRIMARY_NODE_ID=$( cat /tmp/pool_nodes.log | grep primary | grep -v down | cut -f1 -d"|" | sed -e "s/ //g")
PRIMARY_HOST=$( cat /tmp/pool_nodes.log | grep primary | grep -v down | cut -f2 -d"|" | sed -e "s/ //g")
log_info "Primary node is $PRIMARY_HOST"

# check if this node is a failed master (degenerated master)
# if yes then pcp_recovery_node or node rejoin is needed
if [ $role == "primary" ] ; then
  # this should never happen !!
  log_info "This node is a primary and it is down: recovery needed"
  # sanity check
  if [ $PRIMARY_NODE_ID -ne $PGP_NODE_ID ] ; then
     log_error "Unpextected state, this node $PGP_NODE_ID is a primary according to pcp_node_info but pool_nodes said $PRIMARY_NODE_ID is master"
     rm -f $PIDFILE
     exit 1
  fi
  if [ "$_arg_auto_recover_primary" == "on" ] ; then
    recover_failed_master "primary node reported as down in pgpool"
    ret=$?
    rm -f $PIDFILE
    exit $ret
  else
    log_info "auto_recover_primary is off, do nothing"
    rm -f $PIDFILE
    exit 0
  fi
fi

log_info "This node is a standby and it is down: check if it can be re-attached"
log_info "Check if the DB is running, if not do not start it but exit with error"
pg_ctl status
if [ $? -ne 0 ] ; then
  log_error "the DB is not running"
  rm -f $PIDFILE
  exit 1
fi
check_is_streaming_from $PRIMARY_HOST
res=$?
if [ $res -eq 1 ] ; then
  recover_standby
  ret=$?
  rm -f $PIDFILE
  exit $ret
fi
if [ "$_arg_auto_recover_primary" == "on" ] ; then
  log_info "node is standby in pgpool but it is not streaming from the primary, probably a degenerated master. Lets do pcp_recovery_node"
  recover_failed_master "standby node not streaming from the primary"
  ret=$?
  rm -f $PIDFILE
  exit $ret
else
  log_info "node is supposed to be a standby but it is not streaming from the primary, however auto_recovery_primary is off so do nothing"
fi
rm -f $PIDFILE
exit 0

pgpool graphical interface

I developed a web based application on top of pgpool and postgres in order to visualize the cluster (state of the watchdog and state of the replication). For now the easiest way to use it is to clone the repo from github, build a docker image containing the app and then run the docker image either on one of the node or on another server (it needs access to the posgres servers via ssh)

I will go through the process to build the docker image and start it on my host. Note that depending on how you install docker (from docker-ce or from centos), you might have to use sudo to run docker commands as non-root user. Note that it requires a version of docker that supports multi-stage build, so go for docker-ce https://docs.docker.com/install/linux/docker-ce/centos/

# clone the github project 
git clone git@github.com:saule1508/pgcluster.git
# get into the directory containing the app
cd pgcluster/manager
# get into the build directory
cd build
#  NB: the user must be in the group docker, otherwise you need to add a sudo in front of the docker commands
#  in the script build.bash
./build.bash

At the end of the script a docker image should be created, it should say “Successfully tagged manager:" in my case it is manager:0.8.0

The container needs an environment variable PG_BACKEND_NODE_LIST which is a csv list of the backends. It also needs the password for the user repmgr. So in this case:

PG_BACKEND_NODE_LIST: 0:pg01.localnet:5432:1:/u01/pg11/data:ALLOW_TO_FAILOVER,1:pg02.localnet:5432:1:/u01/pg11/data:ALLOW_TO_FAILOVER,2:pg03.localnet:5432:1:/u01/pg11/data:ALLOW_TO_FAILOVER
REPMGRPWD: rep123

the docker container should also be capable to connect to the 3 postgres nodes via ssh, without a password: to make this possible we will need to copy over the public key stored in the image to the 3 servers (the public key must be added to /root/.ssh/authorized_keys on the backends, we can use ssh-copy-id for that)

start the container, in order to copy the public key

docker run -ti --net host --entrypoint bash manager:0.8.0
# now we are inside the container, check that you can ping the backends
ping pg01.localnet
ping pg02.localnet
ping pg03.localnet
# copy the public key 
ssh-copy-id postgres@pg01.localnet
ssh-copy-id postgres@pg02.localnet
ssh-copy-id postgres@pg03.localnet
# exit the container
exit

The tool executes a script called /opt/postgres/scripts/checks.sh on the backend, so we must install it on the 3 servers

#!/bin/bash
repmgr node check | grep -v "^Node" | while read line
do
  ck=$(echo $line | sed -e "s/\t//" -e "s/ /_/g" | cut -f1 -d":")
  res=$(echo $line | cut -f2- -d":")
  echo repmgr,$ck,$res
done
df -k ${PGDATA} | grep -v "^Filesystem" | awk '{print "disk,"$NF","$5","$2","$3}'
df -k /backup | grep -v "^Filesystem" | awk '{print "disk,"$NF","$5","$2","$3}'
df -k /archive | grep -v "^Filesystem" | awk '{print "disk,"$NF","$5","$2","$3}'

And now we can run the container. It will expose the port 8080, so we must open this port on the firewall

firewall-cmd --add-port 8080/tcp --permanent
systemctl restart firewalld
systemctl restart docker
docker run -d --net host \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -e PG_BACKEND_NODE_LIST=0:pg01.localnet:5432:1:/u01/pg11/data:ALLOW_TO_FAILOVER,1:pg02.localnet:5432:1:/u01/pg11/data:ALLOW_TO_FAILOVER,2:pg03.localnet:5432:1:/u01/pg11/data:ALLOW_TO_FAILOVER \
  -e REPMGRPWD:rep123 \
  -e DBHOST=192.168.122.99  \
  -e SCRIPTSDIR=/opt/postgres/scripts -e SSHPORT=22 -e BACKUPDIR=/u02/backup \
  --name pgpoolmanager manager:0.8.0
Written on March 14, 2019