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

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

Postgresql & repmgr installation

On both servers, install PostgreSQL and repmgr.

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.

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

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

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

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:

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

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.

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

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.

and restart repmgrd sudo service repmgrd restart .

Cluster registration

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

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

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

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:

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.

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