Re: Interpreting pg_stat_replication values

Hi,

On Mon, Jan 16, 2012 at 1:25 PM, Alexander Fortin
<alexander(dot)fortin(at)gmail(dot)com> wrote:
> Anyway, the whole idea is to check "distance" between the actual value for
> the master (still have to figure out where is that) and the replicas
> replay_location, and alert if that value is too high compared to our average
> trends. If you've got better ideas to spare, don't hesitate ;)

With 9.1 you can use

SELECT now() - pg_last_xact_replay_timestamp() AS time_lag;

to measure a time lag on replica, and the following solution to get a
byte lag for each replica on master:

CREATE OR REPLACE FUNCTION hex_to_int(i_hex text, OUT o_dec integer)
RETURNS integer LANGUAGE 'plpgsql' IMMUTABLE STRICT AS $$
BEGIN
EXECUTE 'SELECT x''' || i_hex || '''::integer' INTO o_dec;
RETURN;
END $$;

SELECT
client_addr,
sent_offset - (
replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
FROM (
SELECT
client_addr,
hex_to_int(split_part(sent_location, '/', 1)) AS sent_xlog,
hex_to_int(split_part(replay_location, '/', 1)) AS replay_xlog,
hex_to_int(split_part(sent_location, '/', 2)) AS sent_offset,
hex_to_int(split_part(replay_location, '/', 2)) AS replay_offset
FROM pg_stat_replication
) AS s;

>
> Thanks for your time
>
>
> [1]
> http://www.postgresql.org/docs/9.1/static/monitoring-stats.html#MONITORING-STATS-VIEWS
>
> --
> Alexander Fortin
> http://about.me/alexanderfortin/
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp