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

  1. Log in or SSH as root to the OS of any of the running nodes in the cluster.
  2. Change the user to postgres. 
    sudo -i -u postgres
  3. 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
  4. (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.
    1. Connect to the psql terminal 
      /opt/vmware/vpostgres/current/bin/psql
    2. To expand the display and make query results easier to read, run the set \x command.
    3. 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;