Thanks a load Michael. This is really helpful. Regards, Granthana
On Tue, Jan 21, 2014 at 12:19 PM, Michael Paquier <michael.paqu...@gmail.com > wrote: > > > On Tue, Jan 21, 2014 at 2:33 PM, Sameer Kumar <sameer.ku...@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 >
