Finding the database replication delay - Query

 

/*Finding the database replication delay*/

———————————————————————————————————

a) Running the following query on the slave:

SELECT EXTRACT(EPOCH FROM (now() – pg_last_xact_replay_timestamp()))::INT;

SELECT extract(epoch from now() – pg_last_xact_replay_timestamp()) AS replica_lag

This query gives you the lag in seconds.

Note: The issue with this query is that while your replica(s) may be 100% caught up, the time interval being returned is always increasing until new write activity occurs on the primary that the replica can replay. This can cause monitoring to give false positives.

b) This can be achieved by comparing pg_last_xlog_receive_location() and pg_last_xlog_replay_location()

on the slave, and if they are the same itreturns 0, otherwise it runs the above query again:

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())::INTEGER

END

AS replication_lag;

c) Compare master and slave xlog

Master:

SELECT pg_current_xlog_location();

Slave:

SELECT pg_last_xlog_receive_location()

d) Execute on Source :

SELECT now()::timestamp(0), slot_name, pg_current_wal_lsn(), restart_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) as replicationSlotLag, active from pg_replication_slots;