Implement replication with repmgr and barman

This tutorial is part of a multipage tutorial. Read the introduction first if you haven’t done so.

The previous tutorials explained how to implement a backup with barman and how to recover your database. Review those tutorials first if you haven’t done so.

In the present tutorial we will setup replication first using repmgr then finish the setup by linking repmgr with barman.

For our purpose we will aim to have the current architecture:

Target architecture for database replication

The master server and barman server SSH connections should already have been setup in previous tutorials. In this architecture we add a slave or hot-standby server that can be used in case the master server fails.

Installation

The packages are the on the PostgreSQL apt source. For more information you can check https://repmgr.org/docs/4.1/installation-packages.html#INSTALLATION-PACKAGES-DEBIAN

Run the following command to install repmgr on server-a and server-c.

sudo apt-get install postgresql-10-repmgr

Install according to your PostgreSQL version.

Setup

On both server-a and server-c setup a repmgr user and a repmgr database on PostgreSQL.

psql -c 'CREATE USER REPLICATION repgmr';
psql -c 'CREATE DATABASE repmgr OWNER repmgr';

Note that this user MUST be a SUPERUSER or have REPLICATION permission in order for this to work.

We also need to setup a SSH connection between our new server server-c and the backup server server-b.

## On server-c
## Create a private key for postgres user 
sudo -u postgres ssh-keygen -b 2048 -t rsa -N "" -C "postgres@server-c"

Copy the public key of postgres and barman respectively to ~/.ssh/authorized_keys of barman@server-b and postgres@server-c.

You can test the connection is working by running:

## On server-b
sudo -u barman ssh postgres@10.20.0.4
## On server-c
sudo -u postgres ssh barman@10.20.0.3

Both commands should allow you to connect to the other servers. If it does not, please fix it before continuing further.

Configuration

Target replication architecture

Because both servers need to be aware of each other, on both database servers setup the repmgr configuration in /etc/repmgr.conf

On server-a:

# A unique integer greater than 0
node_id=1
# A unique name, don't set it as "slave" or "master" it will get
# confusing once there is a switchover and the master becomes slave # and the slave becomes master. Give it the current server name.
node_name=server-a
# The connection information of the current server.
# It is important to keep the connection information reachable from
# the other servers in the network, so we use the network address
# as opposed to localhost address for the connection information
conninfo='host=10.10.0.2 user=repmgr dbname=repmgr port=5432'
# Where to find the PostgreSQL data directory on this server
data_directory=/var/lib/postgresql/10/
# The IP of the barman server
barman_host=10.10.0.3
# The backup name of the current database on the barman server
barman_server=server-a
# The command to use during database restoration
# The /usr/bin/barman-wal-restore is a script on the current server # provided by repmgr package and NOT barman package
restore_command=/usr/bin/barman-wal-restore -U barman 10.10.0.3 server-a %f %p
# The bin directory of the postgresql installation
pg_bindir=/usr/lib/postgresql/10/bin
# The repmgr log file
log_file=/var/log/repmgr.log

On server-c:

# A unique integer great
node_id=2
# A unique name, don't set it as "slave" or "master" it will get
# confusing once there is a switchover and the master becomes slave # and the slave becomes master. Give it the current server name.
node_name=server-c
# The connection information of the current server.
# It is important to keep the connection information reachable from
# the other servers in the network, so we use the network address
# as opposed to localhost address for the connection information
conninfo='host=10.10.0.4 user=repmgr dbname=repmgr port=5432'
# Where to find the PostgreSQL data directory on this server
data_directory=/var/lib/postgresql/10/
# The IP of the barman server
barman_host=10.10.0.3
# The backup name of the current database on the barman server
barman_server=server-c
# The command to use during database restoration
# The /usr/bin/barman-wal-restore is a script on the current server # provided by repmgr package and NOT barman package
restore_command=/usr/bin/barman-wal-restore -U barman 10.10.0.3 server-a %f %p
# The bin directory of the postgresql installation
pg_bindir=/usr/lib/postgresql/10/bin
# The repmgr log file
log_file=/var/log/repmgr.log

For the replication to work the server-a needs to accept replication connection from server-c. The replica is requesting replication information not the other way around. So in pg_hba.conf file of the server-a database, make sure the following or equivalent lines are present:

# Make sure repmgr, from server-c, can ask server-a repmgr for
# information and update its status
host repmgr repmgr 10.20.0.4/32 trust
# Make sure replication connection is accepted from server-c
host replication repmgr 10.20.0.4/32 trust
# Make sure repmgr can connect to the local repmgr database,
# which we will later reference using its network IP, so use 
# this instead of the localhost IP.
host repmgr repmgr 10.20.0.2/32 trust

Note that like in the previous tutorial, we use trust connection for the sake of simplicity. You should alter those depending on your needs in a production environment.

Please now restart PostgreSQL and the repmgrd daemon.

sudo /etc/init.d/postgresql restart
sudo /etc/init.d/repmgrd restart

Node registration

Now that we have finished configuring both servers. We need identify which server is going to play which role.

# On server-a, as postgres user, register the database as primary
sudo -u postgres repmgr primary register

On PostgreSQL server-a you should see:

$ psql -U repmgr -c 'select node_id, node_name, type, conninfo from repmgr.nodes' -d repmgr
 node_id | node_name |  type   |              conninfo                       
---------+-----------+---------+-----------------------------------
       1 | vagrant-a | primary | host=10.20.0.2

Now for our standby server, server-c we need to do a little bit more than registering it as standby server because the replication need to catch up to where the primary server is at. This is where barman comes up.

We are going to clone the database on server-a onto server-c. Note that this will erase the data folder on server-c.

## On server-c
# First stop postgresql 
sudo /etc/init.d/postgresql stop
# Clone the server from server-a, this will use 
# the restore_command set in the repmgr configuration file
sudo -u postgres repmgr -h 10.20.0.2 -U repmgr -d repmgr -p 5432 -F standby clone
# Restart PostgreSQL
sudo /etc/init.d/postgresql restart
# Register the node as standby
sudo -u postgres repmgr standby -F register

On either server you should now have:

$ psql -U repmgr -c 'select node_id, upstream_node_id, node_name, type from repmgr.nodes' -d repmgr
node_id | upstream_node_id | node_name |  type   |          
--------+------------------+-----------+---------+
      1 |                  |  server-a | primary |
      2 |                1 |  server-c | standby |

What happened was that repmgr used the latest backup of barman to clone the database from server-a so that server-c can pick up replication from the same state as server-a.

You can try to play with test data and confirm that the data is indeed replicated. Note that you will not be able to do write operations on server-c which is now in standby mode.

In the next tutorial we will see how to setup failover and automatic failover.