Message-ID: <53272551.6050205@pinpointresearch.com>
Date: Mon, 17 Mar 2014 09:39:45 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.3.0
MIME-Version: 1.0
To: =?ISO-8859-2?Q?Strahinja_Kustudi=E6?= <strahinjak@nordeus.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Safe way to check for PostgreSQL replication delay/lag
References: 
 <CADKbJJWz9M0swPT3oqe8f9+tfD4-F54uE6Xtkh4nERpVsQnjnw@mail.gmail.com>
In-Reply-To: 
 <CADKbJJWz9M0swPT3oqe8f9+tfD4-F54uE6Xtkh4nERpVsQnjnw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On 03/15/2014 12:38 PM, Strahinja Kustudi=E6 wrote:
> Looking at the documentation and all the blog posts about how to=20
> monitor replication delay I don't think there is one good and most=20
> importantly safe solution which works all the time.

I have a basic homegrown script, the core of which is below, that runs=20
this query on my servers (running 9.1 - settings may need tweaking for=20
other versions). It is a bash script that runs every minute via cron and=20
sets $standby_delay to a value in seconds. The script sends varying=20
levels of alert depending on the severity of the delay. (We must be=20
sized appropriately since we essentially never get alerts unless we have=20
restarted a server for maintenance.)

The script uses a one-record/one-column table called sync_status to hold=20
a timestamptz. To keep things standard across machines, it determines=20
whether it is a master or replica based on the value of=20
'transaction_read_only' in pg_settings and either updates sync_status or=20
reads it, along with other xact_replay information, to determine the=20
worst-case delay.

The core of the script writes a query to a temporary file then executes=20
that query. The output will be the worst-case measurement of delay=20
seconds (or zero if the master) which is saved in $standby_delay. An=20
empty value indicates a failure of the script to run. Obviously there is=20
more to the script both in setting the value of creating and setting=20
$tempquery, cleaning up temp files, and generating the appropriate=20
alerts based on the severity of the delay.

Your alert methods and severity levels are up to you. Note that when run=20
by cron every minute there exists a reasonable possibility that the=20
slave can report a 60-second delay when actually caught up so you will=20
need to account for that in setting your alert levels.

standby_delay=3D$(
psql -q --tuples-only --no-align 2>/dev/null <<EOS
\o ${tempquery}
select
case when setting=3D'on' then
'
with logdelay as
     (
     select
     case when
pg_last_xlog_receive_location()=3Dpg_last_xlog_replay_location() then 0::int
     else
         (extract(epoch from now())-extract(epoch from=20
pg_last_xact_replay_timestamp()))::int
     end as replicadelay
     union
     select
         (extract(epoch from now())-extract(epoch from sync_time))::int=20
as replicadelay
     from
         sync_status
     )
select
     max(replicadelay)
from
     logdelay
;
'
else
'
begin;
delete from sync_status;
insert into sync_status (sync_time) values (now()) returning 0::int as=20
replicadelay;
commit;
'
end
from pg_settings where name=3D'transaction_read_only';
\o
\i ${tempquery}
EOS
)


Cheers,
Steve


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin