6

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?

krithikaGopalakrishnan
  • 1,217
  • 10
  • 28

2 Answers2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • edited the question :). i thought the replication_lag was to be measured as, the diff in timestamp of the record(record insert time in primary) and now()(the time it is replayed in standby). im not following how pg_last_xact_replay_timestamp() can be used for measuring lag. – krithikaGopalakrishnan Aug 06 '18 at 06:25
  • I extended the answer. If there is constant activity, then the difference to the current time is a measure for replication lag. – Laurenz Albe Aug 06 '18 at 06:41
0

If you use the "repmgr" package, it runs a daemon in the background and monitors this for you.

https://repmgr.org/docs/current/repmgrd-monitoring.html

Tel
  • 21
  • 1