Re: pg_basebackup vs WAL fetching

On 12 February 2016 at 22:37, Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> Right now, pg_basebackup can only use replication slots with WAL
> streaming. It's intended, I believe, to be used when you use pg_basebackup
> to set up a new replica, to close the gap before starting a replica (ref
> http://www.postgresql.org/message-id/flat/555DD2B2(dot)7020000(at)gmx(dot)net)
>
> A bit down that thread there is a discussion about pg_basebackup being
> able to use this for standalone backups, specifically -X mode, to make sure
> things are not rotated away.
>
> AFAICT this was never done, with the thread ending on a subject of "this
> is for a different thread". I couldn't find such a different thread.
>
> I think it would still be a very useful feature. In fact, I think it would
> be useful enough that it should be enabled by default (but of course
> possible to turn off), in the interest of making the default more difficult
> to break.
>
> On the backend side, I think that would just mean adding a parameter to CREATE_REPLICATION_SLOT
> to create the slot ephemeral. And register an exit handler that will remove
> it when the backup session disconnects as well (ephemeral slots go away on
> crash, but AIUI not on regular shutdown).
>
> With this, it should be useful for both -X fetch and -X stream, as long as
> pg_basebackup doesn't connect from the master until it has seen all it's
> WAL.
>
> Did I miss a thread somewhere saying this is not a good idea?
> <http://www.redpill-linpro.com/>
>

I think it's a very good idea ... with a significant caveat.

Using a persistent replication slot greatly increases the risk of problems
if the backup fails partway though. The abandoned slot will not be dropped
automatically and will continue to pin the minimum required LSN, risking
the filling of pg_xlog .

Internally replication slots have an ephemeral form, where the slot is
dropped automatically on crash recovery. This isn't exposed directly to
users. It's used for logical replication slots while they're waiting for a
valid snapshot. Maybe something along those lines could be useful, but
limited to the lifetime of the connection? The downside is that this would
stop pg_basebackup from ever learning to reconnect and resume receiving WAL
where it left off.

I'm not really sure how to handle that. I don't want pg_basebackup failures
to risk breaking the master. On the other hand it's clearly good to make
backups more reliable and slots provide a useful way to do that. Letting
pg_basebackup reconnect and resume reading WAL when using a slot would be
nice.

Thoughts?

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services