measuring replication lag in postgresql

Question

im trying to measure the replication time lag in my system. (postgresql 10.1)

I use a combination of pg_last_xact_timestamp(), pg_last_receive_lsn() and pg_last_replay_lsn() functions in a query to check the lag.

(Took an example of how to measure from this link)

postgres=# SELECT now(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INT;
               now                | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | date_part 
----------------------------------+-------------------------+------------------------+-----------
 2018-08-06 07:00:36.540959+05:30 | 4/99B84030              | 4/99B84030             |       223

As it can be seen from the 2nd and 3 rd columns, the last receive lsn and replay lsn are same, meaning that the systems are in sync. But im unable to understand what is pg_last_xact_replay_timestamp() is. How does it find out replication lag in seconds. Am i using wrong approach to measure the lag in seconds?

Answer 1

Are you talking about pg_last_xact_replay_timestamp? That gives you the time when the last transaction replayed on the standby was committed on the primary.

This can be used to measure replication lag if you know that there is always some activity that is being replicated. If there is no such activity, you cannot tell replication lag from that value.

You can make sure that there is always activity by regularly calling txid_current(), or you can measure replication lag in bytes.