How to Check Postgres Replication

Run the following command in Master Node:

select application_name, state, sync_priority, sync_state from pg_stat_replication;

You should see the below:

application_name | state | sync_priority | sync_state
------------------+-----------+---------------+------------
pgslave001 | streaming | 1 | sync

To view all details, run:

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
-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+
-----------+---------------+----------------+----------------+-----------------+---------------+------------
56393 | 19125 | replica | pgslave001 | 10.0.0.34 | | 39356 | 2018-06-11 09:43:21.832726+08 | |
streaming | 2/C5D021A8 | 2/C5D021A8 | 2/C5D021A8 | 2/C5D02078 | 1 | sync

You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location on the primary and the pg_last_xlog_receive_location/pg_last_xlog_replay_location on the standby, respectively.

Run the below on Master Node:

SELECT pg_current_xlog_location();
pg_current_xlog_location
--------------------------
0/2000000
(1 row)

Run the below on Slave Node:

pg_last_xlog_receive_location();
pg_last_xlog_receive_location
-------------------------------
0/2000000
(1 row)

You can also check the progress of streaming replication by using ps command.

[master] $ ps -ef | grep sender
postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000

[slave] $ ps -ef | grep receiver
postgres 6878 6872 1 10:31 ? 00:00:01 postgres: wal receiver process streaming 0/2000000

Be the first to comment

Leave a Reply

Your email address will not be published.


*