pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSafe way to check for PostgreSQL replication delay/lag
5+ messages / 4 participants
[nested] [flat]
* Safe way to check for PostgreSQL replication delay/lag
@ 2014-03-15 19:38 Strahinja Kustudić <strahinjak@nordeus.com>
0 siblings, 3 replies; 5+ messages in thread
From: Strahinja Kustudić @ 2014-03-15 19:38 UTC (permalink / raw)
To: pgsql-admin
Looking at the documentation and all the blog posts about how to monitor
replication delay I don't think there is one good and most importantly safe
solution which works all the time.
*Solution #1:*
I used to check replication delay/lag by running the following query on the
slave:
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;
This query works great and it is a very good query to give you the lag in
seconds. The problem is if the master is not active, it doesn't mean a
thing. So you need to first check if two servers are in sync and if they
are, return 0.
*Solution #2:*
This can be achieved by comparing pg_last_xlog_receive_location() and
pg_last_xlog_replay_location() on the slave, and if they are the same it
returns 0, otherwise it runs the above query again:
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())::INTEGER
END
AS replication_lag;
This query is all good, but the problem is that it is not safe. If for some
reason the master stops sending transaction logs, this query will continue
to return 0 and you will think the replication is working, when it is not.
*Solution #3:*
The Postgres Wiki
http://wiki.postgresql.org/wiki/Streaming_Replicationrecommends to run
the following two queries:
Master:
SELECT pg_current_xlog_location();
Slave:
SELECT pg_last_xlog_receive_location();
and by comparing these two values you could see if the servers are in sync.
The problem yet again is that if streaming replication fails, both of these
functions will continue to return same values and you could still end up
thinking the replication is working. But also you need to query both the
master and slave to be able to monitor this, which is not that easy on
monitoring systems, and you still don't have the information about the
actual lag in seconds, so you would still need to run the first query.
*Solution #4:*
You could query pg_stat_replication on the master, compare sent_location
and replay_location, and if they are the same, the replication is in sync.
One more good thing about pg_stat_replication is that if streaming
replication fails it will return an empty result, so you will know it
failed. But the biggest problem with this system view is that only the postgres
user can read it, so it's not that monitoring friendly since you don't want
to give your monitoring system super user privileges, and you still don't
have the delay in seconds.
*Real solution?*
Looking at all four solutions, I think the best one would be #2 combined
with a check if the wal receiver process is running before running that
query with something like:
$ ps aux | egrep 'wal\sreceiver'
postgres 3858 0.0 0.0 2100112 3312 ? Ss 19:35 0:01 postgres:
wal receiver process streaming 36/900A738
This solution would only be run on the slave and it is pretty easy to setup.
Does anyone have a better idea?
Strahinja Kustudić | Lead System Engineer | Nordeus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Safe way to check for PostgreSQL replication delay/lag
@ 2014-03-15 20:38 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Strahinja Kustudić <strahinjak@nordeus.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rosser Schwarz @ 2014-03-15 20:38 UTC (permalink / raw)
To: Strahinja Kustudić <strahinjak@nordeus.com>; +Cc: pgsql-admin
On Sat, Mar 15, 2014 at 2:38 PM, Strahinja Kustudić
<strahinjak@nordeus.com>wrote:
> Does anyone have a better idea?
>
Take a look at the check_postgres tool, available at bucardo.org. It has a
built-in test, "replicate_row", that may meet your needs.
http://bucardo.org/check_postgres/check_postgres.pl.html#replicate_row
--
:wq
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Safe way to check for PostgreSQL replication delay/lag
@ 2014-03-16 03:17 Murthy Nunna <mnunna@fnal.gov>
parent: Strahinja Kustudić <strahinjak@nordeus.com>
2 siblings, 1 reply; 5+ messages in thread
From: Murthy Nunna @ 2014-03-16 03:17 UTC (permalink / raw)
To: Strahinja Kustudić <strahinjak@nordeus.com>; pgsql-admin
I just built a plpgsql function called is_replication_lag_more_than_10mts() using Solution #1 this morning.... I run this from crontab every 5 minutes and sends an email if the replication lags behind more than 10 minutes.
I understand the limitation, but in my case it is ok. My primary is constantly active.
I have other databases that are not active where I am thinking of creating a simple table and keep truncating it may be every 5 minutes depending on what my archive_timeout is... This I know is not really elegant but it is robust in my opinion.
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Strahinja Kustudic
Sent: Saturday, March 15, 2014 2:39 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Safe way to check for PostgreSQL replication delay/lag
Looking at the documentation and all the blog posts about how to monitor replication delay I don't think there is one good and most importantly safe solution which works all the time.
Solution #1:
I used to check replication delay/lag by running the following query on the slave:
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;
This query works great and it is a very good query to give you the lag in seconds. The problem is if the master is not active, it doesn't mean a thing. So you need to first check if two servers are in sync and if they are, return 0.
Solution #2:
This can be achieved by comparing pg_last_xlog_receive_location() and pg_last_xlog_replay_location() on the slave, and if they are the same it returns 0, otherwise it runs the above query again:
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())::INTEGER
END
AS replication_lag;
This query is all good, but the problem is that it is not safe. If for some reason the master stops sending transaction logs, this query will continue to return 0 and you will think the replication is working, when it is not.
Solution #3:
The Postgres Wiki http://wiki.postgresql.org/wiki/Streaming_Replication recommends to run the following two queries:
Master:
SELECT pg_current_xlog_location();
Slave:
SELECT pg_last_xlog_receive_location();
and by comparing these two values you could see if the servers are in sync. The problem yet again is that if streaming replication fails, both of these functions will continue to return same values and you could still end up thinking the replication is working. But also you need to query both the master and slave to be able to monitor this, which is not that easy on monitoring systems, and you still don't have the information about the actual lag in seconds, so you would still need to run the first query.
Solution #4:
You could query pg_stat_replication on the master, compare sent_location and replay_location, and if they are the same, the replication is in sync. One more good thing about pg_stat_replication is that if streaming replication fails it will return an empty result, so you will know it failed. But the biggest problem with this system view is that only the postgres user can read it, so it's not that monitoring friendly since you don't want to give your monitoring system super user privileges, and you still don't have the delay in seconds.
Real solution?
Looking at all four solutions, I think the best one would be #2 combined with a check if the wal receiver process is running before running that query with something like:
$ ps aux | egrep 'wal\sreceiver'
postgres 3858 0.0 0.0 2100112 3312 ? Ss 19:35 0:01 postgres: wal receiver process streaming 36/900A738
This solution would only be run on the slave and it is pretty easy to setup.
Does anyone have a better idea?
Strahinja Kustudić | Lead System Engineer | Nordeus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Safe way to check for PostgreSQL replication delay/lag
@ 2014-03-16 08:34 Strahinja Kustudić <strahinjak@nordeus.com>
parent: Murthy Nunna <mnunna@fnal.gov>
0 siblings, 0 replies; 5+ messages in thread
From: Strahinja Kustudić @ 2014-03-16 08:34 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
@Rosser The replicate_row is a nice feature, but I don't want to do any
writes on the primary to be able to check something as simple as
replication delay.
@Murthy As I sadi, if the primary is constantly active, the first solution
works great. I used it all the time, until a got a few databases which are
not that active. I was also thinking of changing some tables just for that
every N minutes, but I still think there is a more elegant solution.
I would also like to mention that solution #2 doesn't work that great as
well, because pg_last_xlog_receive_location() and
pg_last_xlog_replay_location() can return different values and the
pg_last_xact_replay_timestamp() wouldn't change while those values are
different. Looking at my logs it looks like that these values change, but
they don't have to change the timestamp. The most interesting thing is that
sometimes the pg_last_xlog_replay_location() changes before
pg_last_xlog_receive_location(), which is odd. I left this simple script
running over night:
while true
do
psql -t -U nordeus -d postgres -c "SELECT now(),
pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), EXTRACT
(EPOCH FROM now() - pg_last_xact_replay_timestamp())::INT" >> pg_rep.log
sleep 10
done
And here is the interesting situation which happens a few times and it
breaks the script:
now | pg_last_xlog_receive_location |
pg_last_xlog_replay_location | date_part
-------------------------------+-------------------------------+------------------------------+-----------
2014-03-16 04:14:30.402919+01 | 36/66000000 |
36/66000000 | 1431
2014-03-16 04:14:40.408628+01 | 36/66000000 |
36/66000000 | 1441
2014-03-16 04:14:50.414527+01 | 36/66000000 |
36/66000000 | 1451
2014-03-16 04:15:00.421092+01 | 36/66EA0000 |
*36/67000000* | 1461
2014-03-16 04:15:10.427686+01 | 36/67000000 |
36/67000000 | 1471
2014-03-16 04:15:20.433828+01 | 36/67000000 |
36/67000000 | 1481
2014-03-16 04:15:30.43982+01 | 36/67000000 |
36/67000000 | 1491
I guess the replay_location can change before receive_location since I have
wal shipping configured and there is an archive_timeout configured to 5
minutes. I will probably need to set email triggers to ignore if this
doesn't happen a few consecutive times.
Strahinja Kustudić | Lead System Engineer | Nordeus
On Sun, Mar 16, 2014 at 4:17 AM, Murthy Nunna <mnunna@fnal.gov> wrote:
> I just built a plpgsql function called is_replication_lag_more_than_10mts()
> using Solution #1 this morning…. I run this from crontab every 5 minutes
> and sends an email if the replication lags behind more than 10 minutes.
>
>
>
> I understand the limitation, but in my case it is ok. My primary is
> constantly active.
>
>
>
> I have other databases that are not active where I am thinking of creating
> a simple table and keep truncating it may be every 5 minutes depending on
> what my archive_timeout is… This I know is not really elegant but it is
> robust in my opinion.
>
>
>
>
>
> *From:* pgsql-admin-owner@postgresql.org [mailto:
> pgsql-admin-owner@postgresql.org] *On Behalf Of *Strahinja Kustudic
> *Sent:* Saturday, March 15, 2014 2:39 PM
> *To:* pgsql-admin@postgresql.org
> *Subject:* [ADMIN] Safe way to check for PostgreSQL replication delay/lag
>
>
>
> Looking at the documentation and all the blog posts about how to monitor
> replication delay I don't think there is one good and most importantly safe
> solution which works all the time.
>
>
>
> *Solution #1:*
>
>
>
> I used to check replication delay/lag by running the following query on
> the slave:
>
>
>
> SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;
>
>
>
> This query works great and it is a very good query to give you the lag in
> seconds. The problem is if the master is not active, it doesn't mean a
> thing. So you need to first check if two servers are in sync and if they
> are, return 0.
>
>
>
>
>
> *Solution #2:*
>
>
>
> This can be achieved by comparing pg_last_xlog_receive_location() and
> pg_last_xlog_replay_location() on the slave, and if they are the same it
> returns 0, otherwise it runs the above query again:
>
>
>
> 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())::INTEGER
>
> END
>
> AS replication_lag;
>
>
>
> This query is all good, but the problem is that it is not safe. If for
> some reason the master stops sending transaction logs, this query will
> continue to return 0 and you will think the replication is working, when it
> is not.
>
>
>
>
>
> *Solution #3:*
>
>
>
> The Postgres Wiki http://wiki.postgresql.org/wiki/Streaming_Replicationrecommends to run the following two queries:
>
>
>
> Master:
>
> SELECT pg_current_xlog_location();
>
>
>
> Slave:
>
> SELECT pg_last_xlog_receive_location();
>
>
>
> and by comparing these two values you could see if the servers are in
> sync. The problem yet again is that if streaming replication fails, both of
> these functions will continue to return same values and you could still end
> up thinking the replication is working. But also you need to query both the
> master and slave to be able to monitor this, which is not that easy on
> monitoring systems, and you still don't have the information about the
> actual lag in seconds, so you would still need to run the first query.
>
>
>
>
>
> *Solution #4:*
>
>
>
> You could query pg_stat_replication on the master, compare sent_location
> and replay_location, and if they are the same, the replication is in
> sync. One more good thing about pg_stat_replication is that if streaming
> replication fails it will return an empty result, so you will know it
> failed. But the biggest problem with this system view is that only the postgres
> user can read it, so it's not that monitoring friendly since you don't
> want to give your monitoring system super user privileges, and you still
> don't have the delay in seconds.
>
>
>
>
>
> *Real solution?*
>
>
>
> Looking at all four solutions, I think the best one would be #2 combined
> with a check if the wal receiver process is running before running that
> query with something like:
>
>
>
> $ ps aux | egrep 'wal\sreceiver'
>
> postgres 3858 0.0 0.0 2100112 3312 ? Ss 19:35 0:01 postgres:
> wal receiver process streaming 36/900A738
>
>
>
> This solution would only be run on the slave and it is pretty easy to
> setup.
>
>
>
> Does anyone have a better idea?
>
>
>
>
> Strahinja Kustudić | Lead System Engineer | Nordeus
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Safe way to check for PostgreSQL replication delay/lag
@ 2014-03-17 16:39 Steve Crawford <scrawford@pinpointresearch.com>
parent: Strahinja Kustudić <strahinjak@nordeus.com>
2 siblings, 0 replies; 5+ messages in thread
From: Steve Crawford @ 2014-03-17 16:39 UTC (permalink / raw)
To: Strahinja Kustudić <strahinjak@nordeus.com>; pgsql-admin
On 03/15/2014 12:38 PM, Strahinja Kustudić wrote:
> Looking at the documentation and all the blog posts about how to
> monitor replication delay I don't think there is one good and most
> importantly safe solution which works all the time.
I have a basic homegrown script, the core of which is below, that runs
this query on my servers (running 9.1 - settings may need tweaking for
other versions). It is a bash script that runs every minute via cron and
sets $standby_delay to a value in seconds. The script sends varying
levels of alert depending on the severity of the delay. (We must be
sized appropriately since we essentially never get alerts unless we have
restarted a server for maintenance.)
The script uses a one-record/one-column table called sync_status to hold
a timestamptz. To keep things standard across machines, it determines
whether it is a master or replica based on the value of
'transaction_read_only' in pg_settings and either updates sync_status or
reads it, along with other xact_replay information, to determine the
worst-case delay.
The core of the script writes a query to a temporary file then executes
that query. The output will be the worst-case measurement of delay
seconds (or zero if the master) which is saved in $standby_delay. An
empty value indicates a failure of the script to run. Obviously there is
more to the script both in setting the value of creating and setting
$tempquery, cleaning up temp files, and generating the appropriate
alerts based on the severity of the delay.
Your alert methods and severity levels are up to you. Note that when run
by cron every minute there exists a reasonable possibility that the
slave can report a 60-second delay when actually caught up so you will
need to account for that in setting your alert levels.
standby_delay=$(
psql -q --tuples-only --no-align 2>/dev/null <<EOS
\o ${tempquery}
select
case when setting='on' then
'
with logdelay as
(
select
case when
pg_last_xlog_receive_location()=pg_last_xlog_replay_location() then 0::int
else
(extract(epoch from now())-extract(epoch from
pg_last_xact_replay_timestamp()))::int
end as replicadelay
union
select
(extract(epoch from now())-extract(epoch from sync_time))::int
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
replicadelay;
commit;
'
end
from pg_settings where name='transaction_read_only';
\o
\i ${tempquery}
EOS
)
Cheers,
Steve
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-03-17 16:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-15 19:38 Safe way to check for PostgreSQL replication delay/lag Strahinja Kustudić <strahinjak@nordeus.com>
2014-03-15 20:38 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2014-03-16 03:17 ` Murthy Nunna <mnunna@fnal.gov>
2014-03-16 08:34 ` Strahinja Kustudić <strahinjak@nordeus.com>
2014-03-17 16:39 ` Steve Crawford <scrawford@pinpointresearch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox