Re: How to determine replication lag

I used the SQL from this link to determine replication lag:

http://www.dansketcher.com/2013/01/27/monitoring-postgresql-streaming-replication/

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;

The one caveat is that if nothing else is committing changes and you have long running transactions that are generating wal, but have not committed, you will show a replication lag. As I understand it pg_last_xact_replay_timestamp() shows the last committed xact timestamp.

And Josh Berkus has done some nice write ups on replication lag:

http://www.databasesoup.com/2014/04/simplifying-replication-position.html