Re: Correct query to check streaming replication lag

[Granthana Biswas <granthana@xxxxxxxx>]

Hi Ray,

We are already using the following query:

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;

We cannot use pg_xlog_location_diff as we use postgresql 9.1.


Regards,

Granthana

On Fri, Jan 17, 2014 at 8:24 PM, Ray Stell <stellr@xxxxxx> wrote:

On Jan 17, 2014, at 5:07 AM, Granthana Biswas <granthana@xxxxxxxx> wrote:

Yes it's purely for monitoring purpose.

I use the pg_controldata cmd locally and via bash/ssh shared keys and compare various values that seem interesting such as "Time of latest checkpoint, Latest checkpoint location."  My interest is recoverability and checkpoints seemed relevant at the time.  

I found a comment in the docs:

http://www.postgresql.org/docs/9.2/static/functions-admin.html

"pg_xlog_location_diff calculates the difference in bytes between two transaction log locations. It can be used with pg_stat_replication or some functions shown in Table 9-59 to get the replication lag."

and 

"The functions shown in Table 9-60 provide information about the current status of the standby. These functions may be executed both during recovery and in normal running."  

These look interesting wrt lag studies and seem to work on the stby:

template1=# select pg_last_xlog_receive_location();

 pg_last_xlog_receive_location 

-------------------------------

 18/9E000000

(1 row)

template1=# select pg_last_xlog_replay_location();

 pg_last_xlog_replay_location 

------------------------------

 18/9E000000

(1 row)