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.

Image for post
Image for post

Vagrant for local testing

Create a Vagrantfile and run vagrant up

Create two servers

Configure DNS / Hosts

For this demo, the IP addresses used are

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

Modify the hosts file using sudo vim /etc/hosts server1 server2

Postgresql & repmgr installation

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 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
# Paste ssh key 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 = ',,'
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 trust
host replication repmgr trust
host repmgr repmgr trust
host replication repmgr 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

conninfo='host=server1 user=repmgr dbname=repmgr connect_timeout=2'
promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'

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


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

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
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.


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
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.


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

Written by

DevOps engineer & SRE

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store