Skip site navigation (1) Skip section navigation (2)

Re: Interpreting pg_stat_replication values

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: Alexander Fortin <alexander(dot)fortin(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Interpreting pg_stat_replication values
Date: 2012-01-18 08:38:16
Message-ID: CAL_0b1vJZdJtGLzBN_wRDoqUCAG6MuF_rCVELgvw+isSehoAUw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

Responses

pgsql-admin by date

Next:From: Rick DicaireDate: 2012-01-18 14:51:33
Subject: CLUSTER command
Previous:From: Tatsuo IshiiDate: 2012-01-18 02:12:50
Subject: Re: [GENERAL] PG synchronous replication and unresponsive slave

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group