Re: Correct query to check streaming replication lag

Thanks a load Michael. This is really helpful.

Regards,
Granthana

On Tue, Jan 21, 2014 at 12:19 PM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com
> wrote:

>
>
> On Tue, Jan 21, 2014 at 2:33 PM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)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
>