Postgres Pro
Downloads
Home   >   mailing lists

Re: Correct query to check streaming replication lag - Mailing list pgsql-general

From Sameer Kumar
Subject Re: Correct query to check streaming replication lag
Date
Msg-id CADp-Sm5Gevh2s2as+Omm2HEYoP26LNnHHZ+MKYKKQE=nn_BZ+g@mail.gmail.com
Whole thread Raw
In response to Correct query to check streaming replication lag  (Granthana Biswas <granthana@zedo.com>)
Responses Re: Correct query to check streaming replication lag  (Granthana Biswas <granthana@zedo.com>)
List pgsql-general
Tree view

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.

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

email patch

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

Attachment

pgsql-general by date:

Previous
From: Laurence Rowe
Date:
Subject: json_array_elements_text?
Next
From: Sameer Kumar
Date:
Subject: Re: Correct query to check streaming replication lag