pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSimultaneously streaming database replication and pg_dump, yet observing zero lag
4+ messages / 3 participants
[nested] [flat]
* Simultaneously streaming database replication and pg_dump, yet observing zero lag
@ 2015-04-30 14:00 Matt Patey <matt.patey@ableton.com>
0 siblings, 1 reply; 4+ messages in thread
From: Matt Patey @ 2015-04-30 14:00 UTC (permalink / raw)
To: pgsql-admin
We have a streaming replication setup, where the replication slave runs pg_dump every hour. Our lag monitor shows an expected sawtooth shape, where pg_dump runs coincide with a climbing lag, which ends abruptly when pg_dump is finished. This is, to the best of my knowledge, expected behaviour. The lag pattern occasionally disappears though, and when looking at system processes we see that the database is recovering WAL data, and pg_dump is also running. How is this even possible?
FWIW, Master is running PostgreSQL 9.3.5-2.pgdg12.4+1 and the slave 9.3.6-1.pgdg12.4+1, and both running on Ubuntu 12.04 LTS.
Cheers,
Matt
--
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] 4+ messages in thread
* Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
@ 2015-04-30 14:08 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Matt Patey <matt.patey@ableton.com>
0 siblings, 2 replies; 4+ messages in thread
From: Scott Ribe @ 2015-04-30 14:08 UTC (permalink / raw)
To: Matt Patey <matt.patey@ableton.com>; +Cc: pgsql-admin
On Apr 30, 2015, at 8:00 AM, Matt Patey <matt.patey@ableton.com> wrote:
>
> We have a streaming replication setup, where the replication slave runs pg_dump every hour. Our lag monitor shows an expected sawtooth shape, where pg_dump runs coincide with a climbing lag, which ends abruptly when pg_dump is finished. This is, to the best of my knowledge, expected behaviour. The lag pattern occasionally disappears though, and when looking at system processes we see that the database is recovering WAL data, and pg_dump is also running. How is this even possible?
Any query (and pg_dump is a series of queries) does not necessarily block recovery. AFAIK, it’s only when the query requires a version of a row which would be removed by WAL replay that the recovery gets blocked. So it’s possible for pg_dump to not block recovery, but in any reasonably active db I think it’s unlikely—exactly what you’re seeing.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
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] 4+ messages in thread
* Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
@ 2015-04-30 14:54 Andres Freund <andres@anarazel.de>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 4+ messages in thread
From: Andres Freund @ 2015-04-30 14:54 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Matt Patey <matt.patey@ableton.com>; pgsql-admin
On 2015-04-30 08:08:22 -0600, Scott Ribe wrote:
> On Apr 30, 2015, at 8:00 AM, Matt Patey <matt.patey@ableton.com> wrote:
> >
> > We have a streaming replication setup, where the replication slave runs pg_dump every hour. Our lag monitor shows an expected sawtooth shape, where pg_dump runs coincide with a climbing lag, which ends abruptly when pg_dump is finished. This is, to the best of my knowledge, expected behaviour. The lag pattern occasionally disappears though, and when looking at system processes we see that the database is recovering WAL data, and pg_dump is also running. How is this even possible?
>
> Any query (and pg_dump is a series of queries) does not necessarily
> block recovery. AFAIK, it’s only when the query requires a version of
> a row which would be removed by WAL replay that the recovery gets
> blocked. So it’s possible for pg_dump to not block recovery, but in
> any reasonably active db I think it’s unlikely—exactly what you’re
> seeing.
Right. And incidences of thta can be greatly reduced by enabling
hot_standby_feedback on the standby.
Greetings,
Andres Freund
--
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] 4+ messages in thread
* Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
@ 2015-04-30 15:13 Matt Patey <matt.patey@ableton.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 4+ messages in thread
From: Matt Patey @ 2015-04-30 15:13 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
Hi Scott,
Thanks for the quick answer! The odd thing is that we see this behaviour, say, once or twice a month, and then the lag pattern becomes regular again. Since this is something that is specific to database activity, I'll have to do a bit of digging around to see what might be causing the inconsistency.
Cheers,
Matt
-----Original Message-----
From: Scott Ribe
Date: Thursday 30 April 2015 16:08 pm
To: Matt Patey
Cc: "pgsql-admin@postgresql.org"
Subject: Re: [ADMIN] Simultaneously streaming database replication and pg_dump, yet observing zero lag
On Apr 30, 2015, at 8:00 AM, Matt Patey <matt.patey@ableton.com> wrote:
>
> We have a streaming replication setup, where the replication slave runs pg_dump every hour. Our lag monitor shows an expected sawtooth shape, where pg_dump runs coincide with a climbing lag, which ends abruptly when pg_dump is finished. This is, to the best of my knowledge, expected behaviour. The lag pattern occasionally disappears though, and when looking at system processes we see that the database is recovering WAL data, and pg_dump is also running. How is this even possible?
Any query (and pg_dump is a series of queries) does not necessarily block recovery. AFAIK, it’s only when the query requires a version of a row which would be removed by WAL replay that the recovery gets blocked. So it’s possible for pg_dump to not block recovery, but in any reasonably active db I think it’s unlikely—exactly what you’re seeing.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
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] 4+ messages in thread
end of thread, other threads:[~2015-04-30 15:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-04-30 14:00 Simultaneously streaming database replication and pg_dump, yet observing zero lag Matt Patey <matt.patey@ableton.com>
2015-04-30 14:08 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-04-30 14:54 ` Andres Freund <andres@anarazel.de>
2015-04-30 15:13 ` Matt Patey <matt.patey@ableton.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