Thread: Correct query to check streaming replication lag

Can anyone please tell me which of the following is the correct replication lag query to find streaming replication lag in seconds?

1. SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) AS time_lag;

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;

Thank you Sameer for your reply. Is there any other query that would help get exact replication lag?

On Fri, Jan 17, 2014 at 5:31 PM, Granthana Biswas <granthana@zedo.com> wrote:

Thank you Sameer for your reply. Is there any other query that would help get exact replication lag?

You 2nd Query is the most accurate you can get. 

 

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.

You can get lags in terms of bytes (and I have always believed that makes more sense).

May I ask, why are you looking for this info? Is it purely monitoring?

Regards

Sameer

Ashnik Pte Ltd.

Well in that case monitoring pending wal bytes would make more sense.

Regards
Sameer

PS: Sent from my Mobile device. Pls ignore typo n abb

Yes we already do that. Count the number of ready wal files.

On Mon, Jan 20, 2014 at 1:53 PM, Granthana Biswas <granthana@zedo.com> wrote:

Yes we already do that. Count the number of ready wal files.

I guess a better place to check would be pg_stat_replication

Check this discussion:

http://www.postgresql.org/message-id/4F13ED11.6080001@gmail.com

Another way is explained in wiki:

http://wiki.postgresql.org/wiki/Streaming_Replication

$ psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)pg_current_xlog_location 
--------------------------0/2000000
(1 row)

$ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20 (standby host)pg_last_xlog_receive_location 
-------------------------------0/2000000
(1 row)

$ psql -c "select pg_last_xlog_replay_location()" -h192.168.0.20 (standby host)pg_last_xlog_replay_location 
------------------------------0/2000000
(1 row)

Mind you, here is a simple suggestion:
SELECT application_name, pg_xlog_location_diff(sent_location, flush_location) AS replay_delta, sync_priority, sync_state FROM pg_stat_replication ORDER BY replay_delta ASC, application_name;

On Tue, Jan 21, 2014 at 12:12 PM, Michael Paquier <michael.paquier@gmail.com> wrote:

Mind you, here is a simple suggestion:
SELECT application_name, pg_xlog_location_diff(sent_location, flush_location) AS replay_delta, sync_priority, sync_state FROM pg_stat_replication ORDER BY replay_delta ASC, application_name;

This is much more simple, but I guess would work only with v9.2 and higher (?).

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.


On Tue, Jan 21, 2014 at 2:33 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
>>
>>
>> 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;
>>
> This is (delay) not the correct thing to monitor.
>
>> We cannot use pg_xlog_location_diff as we use postgresql 9.1.
>>
> You can still use the other two methods I mentioned.

FYI, here is an equivalent written in plpgsql easily findable by googling a bit, making a pg_xlog_location_diff-like function usable even in 9.1 and 9.0 servers:
CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text)
 RETURNS numeric
 LANGUAGE plpgsql
AS
 $function$
    DECLARE
       offset1 text;
       offset2 text;
       xlog1 text;
       xlog2 text;
       SQL text;
       diff text;
    BEGIN
       /* Extract the Offset and xlog from input in
          offset and xlog variables */
           
       offset1=split_part($1,'/',2);
         xlog1=split_part($1,'/',1);
       offset2=split_part($2,'/',2);
         xlog2=split_part($2,'/',1);
       
       /* Prepare SQL query for calculation based on following formula
         (FF000000 * xlog + offset) - (FF000000 * xlog + offset)
         which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome
         so convert into decimal and then calculate the difference */
       
       SQL='SELECT (x'''||'FF000000'||'''::bigint * x'''||xlog1||'''::bigint
                                +  x'''||offset1||'''::bigint)'||'
                -
                   (x'''||'FF000000'||'''::bigint * x'''||xlog2||'''::bigint
                                +  x'''||offset2||'''::bigint)';
       EXECUTE SQL into diff;
       
       /* Return the value in numeric by explicit casting  */
       
       RETURN diff::numeric;
    END;
 $function$;

Source: http://vibhorkumar.wordpress.com/2013/02/18/pg_xlog_location_diff-function-for-postgreqsqlppas/
--
Michael