From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Matt Patey <matt.patey@ableton.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
Date: Thu, 30 Apr 2015 08:08:22 -0600
Message-ID: <EE1482C5-35B8-487B-89A2-9157C5B8ABB5@elevated-dev.com> (raw)
In-Reply-To: <A1BBF909-1F33-4704-963E-F61F23F8FB58@ableton.com>
References: <A1BBF909-1F33-4704-963E-F61F23F8FB58@ableton.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, matt.patey@ableton.com
Subject: Re: Simultaneously streaming database replication and pg_dump, yet observing zero lag
In-Reply-To: <EE1482C5-35B8-487B-89A2-9157C5B8ABB5@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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