Re: pg_basebackup and snapshots

Hi,

On 2020-02-07 14:56:47 -0500, Stephen Frost wrote:
> * Andres Freund (andres(at)anarazel(dot)de) wrote:
> > Maybe that's looking too far into the future, but I'd like to see
> > improvements to pg_basebackup that make it integrate with root requiring
> > tooling, to do more efficient base backups. E.g. having pg_basebackup
> > handle start/stop backup and WAL handling, but do the actual backup of
> > the data via a snapshot mechanism (yes, one needs start/stop backup in
> > the general case, for multiple FSs), would be nice.
>
> The challenge with this approach is that you need to drop the 'backup
> label' file into place as part of this operation, either by putting it
> into the snapshot after it's been taken, or by putting it into the data
> directory at restore time. Of course, you have to keep track of WAL
> anyway from the time the snapshots are taken until the restore is done,
> so it's certainly possible, as with all of this, it's just somewhat
> complicated.

It's not dead trivial, but also doesn't seem *that* hard to me compared
to the other challenges of adding features like this? How to best
approach it I think depends somewhat on what exact type of backup
(mainly whether to set up a new system or to make a PITR base backup)
we'd want to focus on. And what kind of snapshotting system / what kind
of target data store.

Plenty of snapshotting systems allow write access to the snapshot once
it finished, so that's one way one can deal with that. I have a hard
time believing that it'd be hard to have pg_basebackup delay writing the
backup label in that case. The WAL part would probably be harder, since
there we want to start writing before the snapshot is done. And copying
all the WAL at the end isn't enticing either.

For the PITR base backup case it'd definitely be nice to support writing
(potentially with callbacks instead of implementing all of them in core)
into $cloud_provider's blob store, without having to transfer all data
first through a replication connection and then again to the blob store
(and without manually implementing non-exclusive base backup). Adding
WAL after the fact to the same blob really a thing for anything like
that (obviously - even if one can hack it by storing tars etc).

Wonder if the the WAL part in particular would actually be best solved
by having recovery probe more than one WAL directory when looking for
WAL segments (i.e. doing so before switching methods). Much faster than
using restore_command, and what one really wants in a pretty decent
number of cases. And it'd allow to just restore the base backup
(e.g. mount [copy of] the snapshot) and the received WAL stream
separately, without needing more complicated orchestration.

Perhaps I am also answering something completely besides what you were
wondering about?

Greetings,

Andres Freund