Streaming replication is a common PostgreSQL high-availability setup, consisting of a primary server (read-write) and one or more standby replicas (read-only). However, a key limitation of streaming replication is that it doesn’t automatically failover to a replica if the primary server goes down, requiring manual intervention from a DBA to promote the replica to read-write mode. This also means the setup needs constant monitoring.
This is where repmgr comes into play, addressing this critical issue by automating failover to a replica in the event of a primary server failure, eliminating the need for DBA intervention

Installing repmgr and PostgreSQL

The setup below will be performed on two VMs running Ubuntu 22, with PostgreSQL 16 being used for the configuration.

hostname	ip address
postgresql-db01	10.217.10.6
postgresql-db02	10.217.10.7

Installing PostgreSQL 16

Preform the below steps in both DB servers

1. Import the repository signing key:

sudo apt install curl ca-certificates sudo install -d /usr/share/postgresql-common/pgdg sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc –fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

2. Create the repository configuration file:

sudo sh -c ‘echo “deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’

3. Update the package lists:

sudo apt update

4. Install PostgreSQL 12 

sudo apt -y install postgresql-16

5. Start the services 

sudo systemctl enable –now [email protected] sudo systemctl status [email protected]

installing repmgr

install repmgr on both primary and secondary server 

sudo apt install repmgr

configuring primary server

On the primary server, some parameters need to be adjusted in the postgresql.conf file, which can be found in /etc/postgresql/16/main. Below are the parameters that need to be edited

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = ‘*’ max_wal_senders = 10 max_replication_slots = 10 wal_level = ‘hot_standby’ or ‘replica’ or ‘logical’ hot_standby = on archive_mode = on archive_command = ‘/bin/true’ shared_preload_libraries = ‘repmgr’

On the primary server, create another superuser called repmgr and create a database assigned to the repmgr user. This database will be used by repmgr for storing statistics.

create user repmgr; ALTER USER repmgr WITH SUPERUSER; create database repmgr with owner repmgr;

next update the pg_hba.conf file with connection allowed in both ipv4 and replication section as follow 

sudo vi /etc/postgresql/16/main/pg_hba.conf

local replication repmgr trust host replication repmgr 127.0.0.1/32 trust host replication repmgr 10.217.10.0/24 trust local repmgr repmgr trust host repmgr repmgr 127.0.0.1/32 trust host repmgr repmgr 10.217.10.0/24 trust

Restart PostgreSQL services to get the all configuration loaded 

sudo systemctl restart [email protected]

Create a repmgr.conf on the master server with the following entire you can place the file in /etc directory 

sudo nano /etc/repmgr.conf

cluster=’failovertest’ node_id=1 node_name=node1 conninfo=’host=10.217.10.6 user=repmgr dbname=repmgr connect_timeout=2′ data_directory=’/var/lib/postgresql/16/main/’ failover=automatic promote_command=’/usr/bin/repmgr standby promote -f /etc/repmgr.conf –log-to-file’ follow_command=’/usr/bin/repmgr standby follow -f /etc/repmgr.conf –log-to-file –upstream-node-id=%n’

“Now we will start by registering the primary server with repmgr using the following command:

repmgr -f /etc/repmgr.conf primary register

Check the status of the cluster by running the following command

repmgr -f /etc/repmgr.conf cluster show

Configuring replica server server

1. Create the repmgr.conf file and populate it with the following parameters. I have placed the file in the /etc/ directory.

sudo nano /etc/repmgr.conf

node_id=2 node_name=node2 conninfo=’host=10.217.10.7 user=repmgr dbname=repmgr connect_timeout=2′ data_directory=’/var/lib/postgresql/16/main/’ failover=automatic promote_command=’/usr/bin/repmgr standby promote -f /etc/repmgr.conf –log-to-file’ follow_command=’/usr/bin/repmgr standby follow -f /etc/repmgr.conf –log-to-file –upstream-node-id=%n

2 . Stop PostgreSQL services in replica server and go to the data directory and remove all the files 

sudo systemctl stop [email protected] sudo -i cd /var/lib/postgresql/16/main rm -rf *

3. now we can perform dry run which test our standby server configuration before we can add it t the cluster 

sudo su – postgres repmgr -h 10.217.10.6 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone –dry-run

4. Start cloning of the data directory from primary server by running the below command 

repmgr -h 10.217.10.6 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone

5. start PostgreSQL 16 services 

systemctl start [email protected]

6. Update the postgresql.conf and pg_hba.conf files to match the configuration parameters used on the primary server.
7. Register the standby server to the cluster 

8. check the replication by creating testdb in primary and check weather it get replicated to the standby server 

sudo -u postgres psql create database testdb;

I would like to extend my thanks to EDB for their excellent guide. You can view it yourself by visiting the following URL

https://www.enterprisedb.com/postgres-tutorials/how-implement-repmgr-postgresql-automatic-failover