Re: [HACKERS] pg_basebackup vs WAL fetching

[Craig Ringer]

On 12 February 2016 at 22:37, Magnus Hagander  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.7020...@gmx.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?
> 
>

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

Re: [HACKERS] pg_basebackup vs WAL fetching

[Andres Freund]

On 2016-02-13 23:25:06 +0800, Craig Ringer wrote:
> Internally replication slots have an ephemeral form, where the slot is
> dropped automatically on crash recovery.

And upon release (including a http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pg_basebackup vs WAL fetching

[Magnus Hagander]

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.7020...@gmx.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?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/