You can use the replication manager tool suite and the PostgreSQL interactive terminal to check the replication status of individual nodes in a database high availability cluster.
Procedure
- Log in or SSH as root to the OS of any of the running nodes in the cluster.
- Change the user to postgres.
sudo -i -u postgres
- Check the replication status of the node.
/opt/vmware/vpostgres/current/bin/repmgr -f /opt/vmware/vpostgres/current/etc/repmgr.conf node status
The system output for the primary provides information on the node, PostgreSQL version, and replication details. For example:Node "bos1-vcloud-static-161-5": PostgreSQL version: 10.9 Total data size: 81 MB Conninfo: host=172.18.36.193 user=repmgr dbname=repmgr connect_timeout=2 Role: primary WAL archiving: off Archive command: (none) Replication connections: 2 (of maximal 10) Replication slots: 0 physical (of maximal 10; 0 missing) Replication lag: n/a
The system output for a standby node provides information on the node, PostgreSQL version, replication details and an upstream node. For example:Node "bos1-vcloud-static-161-49": PostgreSQL version: 10.9 Total data size: 83 MB Conninfo: host=172.18.36.191 user=repmgr dbname=repmgr connect_timeout=2 Role: standby WAL archiving: off Archive command: (none) Replication connections: 0 (of maximal 10) Replication slots: 0 physical (of maximal 10; 0 missing) Upstream node: bos1-vcloud-static-161-48 (ID: 683) Replication lag: 0 seconds Last received LSN: 2/D863B4E0 Last replayed LSN: 2/D863B4E0
- (Optional) For more detailed information, use the PostgreSQL interactive terminal to check the replication status of the nodes.
The PostgreSQL interactive terminal can provide information regarding whether any of the received log records of the standby nodes are lagging behind the logs sent by the primary.
- Connect to the psql terminal
/opt/vmware/vpostgres/current/bin/psql
- To expand the display and make query results easier to read, run the
set \x
command. - Run a replication status query depending on the role of the node.
Option Action Run a query on the primary node.
select* from pg_stat_replication;
Run a query on a standby node.
select* from pg_stat_wal_receiver;
- Connect to the psql terminal