How to check the replication delay in PostgreSQL?

Alf162 mentioned a good solution in the comments to Craig Ringer’s answer; so I’m adding this to clarify.

PostgreSQL has an administrative function pg_last_xact_replay_timestamp() which returns time stamp of the last transaction replayed during recovery. This is the time at which the commit or abort WAL record for that transaction was generated on the primary.

So this query select now()-pg_last_xact_replay_timestamp() as replication_lag on a replica will return a duration representing the difference in time between the current clock and the timestamp of the last WAL record applied from the replication stream.

Note that if the master is not receiving new mutations, there will be no WAL records to stream and the lag calculated this way will grow without actually being a signal of delays in replication. If the master is under more or less continuous mutation, it will be continuously streaming WALs and the above query is a fine approximation of the time delay for changes on the master to materialize on the slave. Accuracy will obviously be affected by how rigorously synchronized the system clocks on the two hosts are.

Leave a Comment