Postgresql High Availability Setup Using repmgr With Witness

PostgreSQL offers a foundation for implementing high availability (HA) through its support for physical and logical replication, configuration options, and consistent physical backups with point-in-time recovery (PITR). Despite these features, PostgreSQL does not provide a comprehensive, built-in HA solution. As a result, database administrators (DBAs) must adopt a modular approach, leveraging tools like the repmgr extension to achieve HA with PostgreSQL.

PostgreSQL’s streaming replication is based on transferring Write-Ahead Log (WAL) files from the primary to the target database. This replication method follows a master-slave configuration, where the master is the primary instance responsible for managing the primary database and its operations.

Repmgr (Replication Manager) is an open-source utility designed to handle PostgreSQL replication and failover. This blog post will cover the steps required to set up and configure a PostgreSQL cluster for automated failover using repmgr, highlighting the necessary components and best practices for achieving high availability in a PostgreSQL environment.

The following software must be installed on all three nodes, i.e. master, standby, and witness server:

• PostgreSQL
• Repmgr

Configure the Primary Server:

Install PostgreSQL on Primary server: Install PostgreSQL 15 on Linux server.

DATA Directory & Database Initialization (Create Cluster):

Create Data Directory:

mkdir -p /var/lib/pgsql/data  

Create User:

useradd postgres

Ownership Change:

chown -R postgres:postgres /usr/local/pgsql/
chown -R postgres:postgres /var/lib/pgsql/15/data/

Install repmgr on the Primary server

Install repmgr using commands with root user

sudo yum install repmgr_15*  

Configure Primary PG server: On the primary server, a PostgreSQL instance must be initialized and running. The following replication settings may need to be adjusted:

Go to the postgresql.conf file and update the below parameters.

max_wal_senders = 10 max_replication_slots = 10 wal_level = 'hot_standby' or 'replica' or 'logical' hot_standby = on archive_mode = on wal_log_hints = on shared_preload_libraries = 'repmgr'

Create repmgr user and database in primary:

create user repmgr; create database repmgr with owner repmgr;

Ensure that the repmgr user has the necessary rights in pg_hba.conf and can connect in replication mode; pg_hba.conf should include entries such as the following:

Note: If you are using SSL encryption, make sure to use it in the postgresql.conf file and change SSL to on and add cert and keys path.

SSL is optional and you can use it without ssl as well, but you will have to update hostssl to host in the pg_hba.conf file.

hostssl   replication     repmgr          Primary_server_IP/32         trust

hostssl   replication     repmgr          Secondary_server_IP/32    trust

hostssl   repmgr           repmgr          Witness_server_IP/32         trust

host       repmgr            repmgr          Primary_server_IP/32         trust

host       repmgr            repmgr          Secondary_server_IP/32     trust

After making changes in postgresql.conf & pg_hba.conf, restart service on primary:

sudo /usr/bin/systemctl restart  postgresql-15.service

Configure Primary server repmgr config file and register:

Create a repmgr.conf file in the Primary server under the /var/lib/pgsql/repmgr/repmgr.conf directory and update the below parameters. Make sure to change the owner of the file to postgres.

/var/lib/pgsql/repmgr/repmgr.conf

node_id=1

node_name=Node01

conninfo='host= <Primary server IP> user=repmgr dbname=repmgr connect_timeout=2 password=<repmgr_login_password>’

data_directory='/var/lib/pgsql/data'

failover=automatic

promote_command='/usr/pgsql-15/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf --log-to-file'

follow_command='/usr/pgsql-15/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf --log-to-file --upstream-node-id=%n'

log_level=INFO

log_file='/var/log/repmgr/repmgr.log'

use_replication_slots=true

pg_bindir='/usr/pgsql-15/bin'

#degraded_monitoring_timeout=120

repmgrd_service_start_command='sudo /usr/bin/systemctl start repmgrd.service'

repmgrd_service_stop_command='sudo /usr/bin/systemctl stop repmgrd.service'

service_start_command='sudo /usr/bin/systemctl start postgresql-15.service'

service_stop_command='sudo /usr/bin/systemctl stop postgresql-15.service'

service_restart_command='sudo /usr/bin/systemctl restart postgresql 15.service'

service_reload_command='sudo /usr/bin/systemctl reload postgresql-15.service'

Once the repmgr configuration is completed, register the primary server:

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf primary register

To check cluster status execute below command:

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

Build\Clone secondary server configuration and register:

Install PostgreSQL 15 on Linux using the installation documentation.

Refer below link for reference: https://www.devart.com/dbforge/postgresql/how-to-install-postgresql-on-linux/

DATA Directory & Database Initialization (Create Cluster):

Create Data Directory:

mkdir -p /var/lib/pgsql/data

Create User:

useradd postgres

Ownership Change:

chown -R postgres:postgres /usr/local/pgsql/
chown -R postgres:postgres /var/lib/pgsql/15/data/

Install Repmgr on Secondary server using commands with root user.

sudo yum install repmgr_15*

Create the repmgr.conf file on standby server under /var/lib/pgsql/repmgr directory.

/var/lib/pgsql/repmgr/repmgr.conf

node_id=2

node_name=Node02

conninfo='host=<Secondary_server_IP> user=repmgr dbname=repmgr connect_timeout=2 password=<repmgr_login_password>’

data_directory='/var/lib/pgsql/data/

failover=automatic

promote_command='/usr/pgsql-15/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf --log-to-file'

follow_command='/usr/pgsql-15/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf --log-to-file --upstream-node-id=%n'

log_level=INFO

log_file='/var/log/repmgr/repmgr.log'

use_replication_slots=true

pg_bindir='/usr/pgsql-15/bin'

#degraded_monitoring_timeout=120

repmgrd_service_start_command='sudo /usr/bin/systemctl start repmgrd.service'

repmgrd_service_stop_command='sudo /usr/bin/systemctl stop repmgrd.service'

service_start_command='sudo /usr/bin/systemctl start postgresql-15.service'

service_stop_command='sudo /usr/bin/systemctl stop postgresql-15.service'

service_restart_command='sudo /usr/bin/systemctl restart postgresql 15.service'

service_reload_command='sudo /usr/bin/systemctl reload postgresql-15.service'

To Setup clone on secondary server, execute below commands:

/usr/pgsql-15/bin/repmgr -h <primary_server_IP> -U repmgr -d repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby clone -c -F --dry-run

If all prerequisites are met during the dry run, perform the following command to clone the secondary server:

/usr/pgsql-15/bin/repmgr -h <primary_server_IP> -U repmgr -d repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby clone -c -F

Use the script below to register the secondary server:

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby register -F

Use below command to check the status of the secondary server:

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

Replication Validation:

Execute the script below on primary server to verify the replication status.

SELECT client_addr AS client, usename AS user, application_name AS name,

 state, sync_state AS mode,

 (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::bigint as pending,

 (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::bigint as write,

 (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::bigint as flush,

 (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::bigint as replay,

 (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::bigint / 1024 as total_lag

 FROM pg_stat_replication;

Witness server configuration and register:

Install PostgreSQL 15 on Linux using the installation documentation.

Refer below link for reference: https://www.devart.com/dbforge/postgresql/how-to-install-postgresql-on-linux/

Install Repmgr on Witness server:

Install repmgr using commands with the root user.

sudo yum install repmgr_15*

Create the repmgr.conf file on standby server under /var/lib/pgsql/repmgr directory.

To Configure Witness, add the below parameters in the configuration file.

/var/lib/pgsql/repmgr/repmgr.conf

node_id=3

node_name=Witness

conninfo='host= <Witness_server_IP> user=repmgr dbname=repmgr connect_timeout=2 password=<repmgr_login_passwrod>'

data_directory='/var/lib/pgsql/data'

Run the below command to register the Witness server:

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf witness register -h <primary_server_ip>  -F

Check the status of the cluster:

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

DB Level Replication Validation:

Run below script in Primary server to check replication status:

SELECT client_addr AS client, usename AS user, application_name AS name,

 state, sync_state AS mode,

 (pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::bigint as pending,

 (pg_wal_lsn_diff(sent_lsn,write_lsn) / 1024)::bigint as write,

 (pg_wal_lsn_diff(write_lsn,flush_lsn) / 1024)::bigint as flush,

 (pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::bigint as replay,

 (pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::bigint / 1024 as total_lag

 FROM pg_stat_replication;

Check in the Secondary server and database status should be in recovery:

Recovery mode should be true.

postgres=# select pg_is_in_recovery(); pg_is_in_recovery
pg_is_in_recovery

-------------------
t
row)

Setup Passwordless Authentication

SSH stands for Secure Shell. It is an open-source network protocol that allows you to log in to servers and run commands remotely, which is ideal for VPS hosting users.

There are two ways to enable SSH:

1. Password-based authentication.
2. Public key-based authentication

Public key authentication is sometimes known as passwordless SSH.

To generate a public and private key on Ubuntu or CentOS, use the command:

ssh-keygen -t rsa

Copying Public key to enable passwordless SSH:

ssh-copy-id remote_username@remote_IP_Address

Connect to Primary server and copy the public Key to standby and vice versa.

ssh-copy-id postgres@Primary_server_IP

Repmgr daemon configuration

To enable automated failover, we must now start the repmgrd daemon process on the Master, Slave, and Witness servers. Follow the same procedures on all the three servers.

Go to path

sudo vi /etc/systemd/system/repmgrd.service

[Unit]

Description=Replication Manager Daemon

Documentation=https://repmgr.org

After=network.target postgresql.service

Wants=postgresql.service

[Service]

Type=forking

User=postgres

Group=postgres

ExecStart=/usr/pgsql-15/bin/repmgrd -f /var/lib/pgsql/repmgr/repmgr.conf

ExecReload=/bin/kill -HUP $MAINPID

KillMode=process

Restart=on-failure

RestartSec=5s

[Install]

WantedBy=multi-user.target

Once updated the entries, reload\enable the daemon and start the service.

sudo systemctl daemon-reload

sudo systemctl enable repmgrd

sudo systemctl status repmgrd

sudo systemctl start repmgrd

Failover scenario

To check the events for the cluster, run the below command in Master or Slave.

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster event

Now, in case the Master server goes down, the Witness server will try to Promote the standby server and it will act as a new Primary. Once the old Master server is online, it will follow the new Primary server.

Run the below command on the Standby server to make sure after switchover, Standby will promote to Primary and vice versa.

/usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby switchover  --siblings-follow --dry-run

Want to discuss your PostgreSQL high availability needs? Get in touch with our expert team.

References:

https://www.2ndquadrant.com/en/blog/how-to-automate-postgresql-12-replication-and-failover-with-repmgr-part-2/
https://www.hostinger.in/tutorials/how-to-setup-passwordless-ssh/
https://www.devart.com/dbforge/postgresql/how-to-install-postgresql-on-linux/
https://www.hostinger.in/tutorials/how-to-setup-passwordless-ssh/