Re: Correct query to check streaming replication lag

On Tue, Jan 14, 2014 at 2:31 PM, Granthana Biswas <granthana(at)zedo(dot)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.

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350 <%2B65%208110%200350>* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).