Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2098\))
Subject: Re: Simultaneously streaming database replication and pg_dump,
 yet observing zero lag
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <A1BBF909-1F33-4704-963E-F61F23F8FB58@ableton.com>
Date: Thu, 30 Apr 2015 08:08:22 -0600
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <EE1482C5-35B8-487B-89A2-9157C5B8ABB5@elevated-dev.com>
References: <A1BBF909-1F33-4704-963E-F61F23F8FB58@ableton.com>
To: Matt Patey <matt.patey@ableton.com>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On Apr 30, 2015, at 8:00 AM, Matt Patey <matt.patey@ableton.com> wrote:
>=20
> We have a streaming replication setup, where the replication slave runs p=
g_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 pro=
cesses 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 r=
ecovery. AFAIK, it=E2=80=99s only when the query requires a version of a ro=
w which would be removed by WAL replay that the recovery gets blocked. So i=
t=E2=80=99s possible for pg_dump to not block recovery, but in any reasonab=
ly active db I think it=E2=80=99s unlikely=E2=80=94exactly what you=E2=80=
=99re seeing.

--=20
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice


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