Verifying PostGres Cluster

Check service status

Master

On master node, while checking service status, we will see wal sender process for each slave node

[root@MasterNode ~]# systemctl status postgresql-X.x | grep -i sender
        ├─ 8819 postgres: wal sender process replication <SlaveNodeIP1>(50412) streaming 9/X12345
        ├─28942 postgres: wal sender process replication <SlaveNodeIP2>(56212) streaming 9/X12345
[root@MasterNode ~]#

Slaves

On slave nodes, while checking service status, we will see wal receiver process for each slave node

[root@SlaveNode ~]# systemctl status postgresql-X.x | grep -i receiver
           └─2899 postgres: wal receiver process   streaming 9/X56789
[root@SlaveNode ~]#

Check DB

Login to the server and connect to DB

sudo -u postgres psql -p <Port>

Master

On master node

• We will see that pg_stat_wal_receiver is empty

  postgres=# select * from pg_stat_wal_receiver;
   pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | conninfo
  -----+--------+-------------------+-------------------+--------------+--------------+--------------------+-----------------------+----------------+-----------------+-----------+----------
  (0 rows)
  
  postgres=#
  

• pg_stat_replication points to slave nodes

  postgres=# select * from pg_stat_replication;
    pid  | usesysid |   usename   | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_prior
  ity | sync_state
  -------+----------+-------------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+-----------
  ----+------------
    PID1 |    163XX | replication | walreceiver      | SlaveNodeIP1 |                 |       50XXX | <SomeDate> <SomeTime> |              | streaming | 9/6505BYYY    | 9/6505BYYY     | 9/6505BYYY     | 9/6505BYYY      |
    0 | async
   PID2 |    163XX | replication | walreceiver      | SlaveNodeIP2 |                 |       56XXX | <SomeDate> <SomeTime> |              | streaming | 9/6505BYYY    | 9/6505BYYY     | 9/6505BYYY     | 9/6505BYYY      |
    0 | async
  (2 rows)
  
  postgres=#
  

Slaves

On slave nodes

• We will see that pg_stat_replication is empty

  postgres=# select * from pg_stat_replication;
  pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
  -----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+---------------+----------------+----------------+-----------------+---------------+------------
  (0 rows)
  
  postgres=#
  

• pg_stat_wal_receiver should point to IP of master node

  postgres=#
  postgres=# select * from pg_stat_wal_receiver;
  pid  |  status   | receive_start_lsn | receive_start_tli | received_lsn | received_tli |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time        |   slot_name    |
                                                                  conninfo
  ------+-----------+-------------------+-------------------+--------------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------+----------------+-------------------
  -----------------------------------------------------------------------------------------------------------------------------------------------------------
  2500 | streaming | 9/64000000        |                 1 | 9/6508767A90   |            1 | <SomeDate> <SomeTime>.162117-06 | <SomeDate> <SomeTime>.069635-06 | 9/650DFA90     | <SomeDate> <SomeTime>.162117-06 | <SlaveNodeName> | user=replication p
  assword=******** dbname=replication host=<MasterNodeIP> port=<PSQLPort> fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres
  (1 row)
  
  postgres=#