How to setup a PostgreSQL cluster with repmgr

In this story I will walk you through a PostgreSQL master/slave cluster using repmgr. It also uses repmgrd for an automatic failover mechanism.

Vagrant for local testing

In order to test all the commands with two local machines I am using Vagrant. However, you can also use your own public / private cloud servers.

Create a Vagrantfile and run vagrant up

Create two servers

Configure DNS / Hosts

First you should configure either a private DNS server for example Route53 if you are on AWS or you can just modify your hosts file.

For this demo, the IP addresses used are

• 10.0.15.21 for server1 (master node)
• 10.0.15.22 for server2 (standby node)

Modify the hosts file using sudo vim /etc/hosts

10.0.15.21 server1
10.0.15.21 server2

Postgresql & repmgr installation

On both servers, install PostgreSQL and repmgr.

sudo apt-get install -y postgresql
# Check your postgresql version and install repmgr
psql --version
wget -qO - | sudo apt-key add -
sudo add-apt-repository 'deb http://packages.2ndquadrant.com/repmgr3/apt/ xenial-2ndquadrant main'
sudo apt update
sudo apt-get install postgresql-9.5-repmgr

I use 2ndquadrant package to be able to install a more recent version of repmgr which fixes some issue with automatic failover.

Then with the postgres account, create a specific user and database for repmgr.

sudo -i -u postgres
createuser --replication --createdb --createrole --superuser repmgr
psql -c 'ALTER USER repmgr SET search_path TO repmgr_test, "$user", public;'
createdb repmgr --owner=repmgr

Now we should configure the ssh access between each servers. This will only be used for new standby node registration to clone the master data (first initialisation).

# Generate id_rsa ssh key
ssh-keygen
# Paste ssh key id_rsa.pub on other servers
vim .ssh/authorized_keys

Then tests ssh connection. You should be able to log in on each other server.

# On server1
ssh server2
# On server2
ssh server1

Update your postgresql configuration file in /etc/postgresql/9.5/main/postgresql.conf with the following

wal_keep_segments = 5000
hot_standby = on
archive_mode = on
# Update your ips according to your cluster hosts
listen_addresses = '127.0.0.1,10.0.15.21,10.0.15.22'
max_wal_senders = 18
wal_level = hot_standby
hot_standby = on
shared_preload_libraries = 'repmgr_funcs'

Restart postgresql withsudo service postgresql restart and check that you can still run the command psql .

If it fails then you can check the log file for errors in /var/log/postgresql/postgresql-9.5-main.log

Last thing is to allow other entry connections by updating your /etc/postgresql/9.5/main/pg_hba.conf by adding the following lines:

host    repmgr repmgr 10.0.15.21/32 trust
host    replication repmgr 10.0.15.21/32 trust
host    repmgr repmgr 10.0.15.21/32 trust
host    replication repmgr 10.0.15.22/32 trust

Restart postgresql sudo service postgresql restart and test the connection on each servers with the following commands:

psql 'host=server1 dbname=repmgr user=repmgr'
psql 'host=server2 dbname=repmgr user=repmgr'

Now each servers can communicate between each other.

Now its time to create our repmgr cluster.

Cluster creation

Create a /etc/repmgr.conf file. Use server1 and node=1on the first server and server2 and node=2 on your second server.

cluster=cluster
node=1
node_name=server1
conninfo='host=server1 user=repmgr dbname=repmgr connect_timeout=2'
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
logfile='/var/log/postgresql/repmgr.log'
loglevel=NOTICE
reconnect_attempts=4
reconnect_interval=5

In order to use automatic failover with repmgrd update the /etc/default/repmgrd file and restart repmgrd with sudo service repmgrd restart

REPMGRD_ENABLED=yes
REPMGRD_CONF="/etc/repmgr.conf"

You can check that repmgrd is running with ps aux | grep repmgrd . If it is not the case then run repmgrd manually to look for errors.

There is also a small issue with repmgrd which does not load the right path for the pg_ctl postgresql command. To fix it create a symbolink link.

sudo ln -s /usr/lib/postgresql/9.5/bin/pg_ctl /usr/bin/pg_ctl

and restart repmgrd sudo service repmgrd restart .

Cluster registration

Now it’s time to register our master node in repmgr on server1.

repmgr primary register

Then you can check your new cluster with repmgr cluster show .

Then on server2, you need to configure it as a standby server.

sudo -i -u repmgr
rm -rf /var/lib/postgresql/9.5/main
repmgr -h server1 -U repmgr -d repmgr standby clone
exit
sudo service postgresql start
sudo -i -u repmgr
repmgr standby register

If everything went well congratulations, you have your postgresql cluster running! 🎉

You can check the cluster status with repmgr cluster show and you should see your master node as well as your new standby node.

Next step is to check if the failover is working when the master node is unavailable.

Failover

In order to test the failover and that repmgrd is working, on your standby server (server2), watch the repmgr.log file with sudo tail -f /var/log/postgresql/repmgr.conf .

Then stop postgresql on your master node with sudo service postgresql stop . You should see in the log that the standby server has lost the master connection and is trying to reconnect 4 times. Then one of the standby nodes will be promoted to master and other standby nodes (if any) will follow the new master.

How to bring back the old master node ?

First stop the postgresql service (it should be done already as the service is unavailable) and execute the following command:

sudo -i -u repmgr
rm -rf /var/lib/postgresql/9.5/main
repmgr -h server2 -U repmgr -d repmgr standby clone
exit
sudo service postgresql start
sudo -i -u repmgr
repmgr standby register

Here server2 is the new master host for our demo. Change it accordingly to your host.

Check the new status using repmgr cluster show . You should have the following output:

The old master is now a standby node which follows the new master.

Conclusion

This is a long tutorial as the process is not that easy but configuring a postgresql cluster without repmgr is way more difficult.

If you have any questions or some part seems confusing do not hesitate to post a comment below.