How to setup a PostgreSQL cluster with repmgr

Victor Boissiere
Mar 31, 2018 · 4 min read

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

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

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 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 with and check that you can still run the command .

If it fails then you can check the log file for errors in

Last thing is to allow other entry connections by updating your 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 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 file. Use and on the first server and and 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 update the file and restart repmgrd with

REPMGRD_ENABLED=yesREPMGRD_CONF="/etc/repmgr.conf"

You can check that repmgrd is running with . If it is not the case then run 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 .

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 .

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

Then stop postgresql on your master node with . 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 . 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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

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