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 */
/* 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 */