check postgres replication status check postgres replication status postgresql postgresql

check postgres replication status


I use following SQL queries to check status on Postgres v11 usually.

On master:

select * from pg_stat_replication;

On replica (streaming replication in my case):

select * from pg_stat_wal_receiver;


On your master, pg_stat_replication provides data about ongoing replication:

select client_addr, state, sent_location, write_location,        flush_location, replay_location from pg_stat_replication;

On postgresql v10:

select client_addr, state, sent_lsn, write_lsn,    flush_lsn, replay_lsn from pg_stat_replication;


Show replication status in PostgreSQL

on server

postgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;usename   | application_name |  client_addr   |         backend_start         |   state   | sync_state ------------+------------------+----------------+-------------------------------+-----------+------------replicator | walreceiver      | 192.168.10.132 | 2018-07-06 06:12:20.786918+03 | streaming | async(1 row)

on client

postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row)postgres=# select pg_last_xlog_receive_location(); pg_last_xlog_receive_location ------------------------------- 0/540C1DB8postgres=# select pg_last_xlog_replay_location(); pg_last_xlog_replay_location ------------------------------ 0/540C1DB8 (1 row)postgres=#    SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()                  THEN 0                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())              END AS log_delay; log_delay ----------- 0 (1 row)