Configure PostgreSQL replication and failover with repmgr:
OS: RHEL 9
PostgreSQL version: 15.7
Repmgr version: 15
Primary server IP: 172.31.87.240
Standby server IP: 172.31.36.56
On Primary server:
Step1) Install PostgreSQL
# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-
x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql
# Install PostgreSQL:
sudo dnf install -y postgresql15-server
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
Step2) Install repmgr
sudo yum install repmgr_15* -y
Step3) Configure below parameter in $PGDATA/postgresql.conf file
vim $PGDATA/postgresql.conf
listen_addresses = '*'
wal_level = replica
archive_mode = on
archive_command = '/bin/true'
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB
hot_standby = on
shared_preload_libraries = 'repmgr'
save&exit
Step4) Restart and check the PostgreSQL services
sudo systemctl restart postgresql-15
sudo systemctl status postgresql-15
Step5) Create user and database for repmgr
CREATE USER repmgr WITH SUPERUSER;
CREATE DATABASE repmgr WITH OWNER repmgr;
Step6) Allow database connectivity for repmgr user
# Edit $PGDATA/pg_hba.conf file
vim $PGDATA/pg_hba.conf file
#######################################Primary_server#######################
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 172.31.87.240/24 trust
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 172.31.87.240/24 trust
###################################Standby_server###########################
host repmgr repmgr 172.31.36.56/32 trust
host replication repmgr 172.31.36.56/32 trust
save&exit
#Reload configurations
SELECT pg_reload_conf();
#Check Connectivity
psql -d repmgr -U repmgr -h 172.31.87.240
Step7) Create a repmgr.conf on primary server with the following entries:
vim /var/lib/pgsql/repmgr.conf
cluster='failovertest'
node_id=1
node_name=node1
conninfo='host=172.31.87.240 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/pgsql/15/data/'
failover=automatic
promote_command='/usr/pgsql-15/bin/repmgr standby promote -f /var/lib/pgsql/repmgr.conf --
log-to-file'
follow_command='/usr/pgsql-12/bin/repmgr standby follow -f /var/lib/pgsql/repmgr.conf --log-
to-file --upstream-node-id=%n'
save&exit
Step8) Register the primary server with repmgr
#Register the primary server
/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr.conf primary register
#Check the status of the cluster
/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr.conf cluster show
On Standby server:
Step9) Install PostgreSQL
# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-
x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql
# Install PostgreSQL:
sudo dnf install -y postgresql15-server
Note: The above step of initialization of the cluster is not needed on the standby server.
Step10) Install repmgr
sudo yum install repmgr_15* -y
Step11) Create a repmgr.conf on standby server with the following entries
vim /var/lib/pgsql/repmgr.conf
node_id=2
node_name=node2
conninfo='host=172.31.36.56 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/pgsql/15/data'
failover=automatic
promote_command='/usr/pgsql-15/bin/repmgr standby promote -f /var/lib/pgsql/repmgr.conf --
log-to-file'
follow_command='/usr/pgsql-15/bin/repmgr standby follow -f /var/lib/pgsql/repmgr.conf --log-
to-file --upstream-node-id=%n'
save&exit
Step12) Perform the dry run and test if our configuration is correct
/usr/pgsql-15/bin/repmgr -h 172.31.87.240 -U repmgr -d repmgr -f /var/lib/pgsql/repmgr.conf
standby clone --dry-run
Step13) If there is no problem, start cloning
/usr/pgsql-15/bin/repmgr -h 172.31.87.240 -U repmgr -d repmgr -f /var/lib/pgsql/repmgr.conf
standby clone
Step14) Start and check the PostgreSQL services
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
sudo systemctl status postgresql-15
Step15) Register the standby server with repmgr
#Register the standby server
/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr.conf standby register
#Check the status of the cluster
/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr.conf cluster show
Step16) Check the Replication is working fine or not
On Primary server:
#check replication status
SELECT * FROM pg_stat_replication;
#Create test table and insert some data
CREATE TABLE test_table(id int,name varchar);
INSERT INTO test_table(id,name) VALUES (1,'naveen'),(2,'Ram');
SELECT * FROM test_table;
On Standby server:
#Check wal receiver status
SELECT * FROM pg_stat_wal_receiver;
#Check test table
SELECT * FROM test_table;
Step17) To enable the automatic failover, start the repmgrd daemon process on Master and
slave server
#Start the repmgrd daemon process on both servers
/usr/pgsql-15/bin/repmgrd -f /var/lib/pgsql/repmgr.conf > /var/lib/pgsql/repmgr.log 2>&1 &
#Check the status of repmgrd daemon process
/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr.conf daemon status
