On Tue, Jan 14, 2014 at 2:31 PM, Granthana Biswas <granthana@xxxxxxxx> 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.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M : +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
