Re: Interpreting pg_stat_replication values

Hi,

On Mon, Jan 16, 2012 at 1:25 PM, Alexander Fortin
<alexander.fortin@gmail.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@postgresql.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.ru@gmail.com Skype: gray-hemp