Thread: Simultaneously streaming database replication and pg_dump, yet observing zero lag
Simultaneously streaming database replication and pg_dump, yet observing zero lag
From
Matt Patey
Date:
We have a streaming replication setup, where the replication slave runs pg_dump every hour. Our lag monitor shows an expectedsawtooth shape, where pg_dump runs coincide with a climbing lag, which ends abruptly when pg_dump is finished. Thisis, to the best of my knowledge, expected behaviour. The lag pattern occasionally disappears though, and when lookingat system processes we see that the database is recovering WAL data, and pg_dump is also running. How is this evenpossible? 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.04LTS. Cheers, Matt
Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
From
Scott Ribe
Date:
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 expectedsawtooth shape, where pg_dump runs coincide with a climbing lag, which ends abruptly when pg_dump is finished. Thisis, to the best of my knowledge, expected behaviour. The lag pattern occasionally disappears though, and when lookingat system processes we see that the database is recovering WAL data, and pg_dump is also running. How is this evenpossible? Any query (and pg_dump is a series of queries) does not necessarily block recovery. AFAIK, it’s only when the query requiresa version of a row which would be removed by WAL replay that the recovery gets blocked. So it’s possible for pg_dumpto 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
Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
From
Andres Freund
Date:
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 anexpected 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 whenlooking at system processes we see that the database is recovering WAL data, and pg_dump is also running. How is thiseven 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
Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
From
Matt Patey
Date:
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 patternbecomes regular again. Since this is something that is specific to database activity, I'll have to do a bit of diggingaround 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 expectedsawtooth shape, where pg_dump runs coincide with a climbing lag, which ends abruptly when pg_dump is finished. Thisis, to the best of my knowledge, expected behaviour. The lag pattern occasionally disappears though, and when lookingat system processes we see that the database is recovering WAL data, and pg_dump is also running. How is this evenpossible? Any query (and pg_dump is a series of queries) does not necessarily block recovery. AFAIK, it’s only when the query requiresa version of a row which would be removed by WAL replay that the recovery gets blocked. So it’s possible for pg_dumpto 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