On Tue, Jan 14, 2014 at 2:31 PM, Granthana Biswas <granthana@zedo.com> wrote:
Can anyone please tell me which of the following is the correct replication lag query to find streaming replication lag in seconds?
IMHO none is 'correct'. :-)
1. SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) AS time_lag;
This is the difference between now and last replayed log. What if the last transaction on master has happened a few minutes ago?
2. 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;
For reason same as above, this won't be exact but the most accurate you can get.