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
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 --versionwget -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=1
on 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=yesREPMGRD_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.