Re: Docs, backups, and MS VSS

On 2 July 2016 at 22:42, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

>
> > I suspect, but cannot prove, that it is also safe to snapshot pg_xlog on
> a
> > separate filesystem if and only if you take the datadir snapshot before
> the
> > pg_xlog snapshot and you have wal_keep_segments high enough to ensure
> that WAL
> > needeed by the redo checkpoint in the datadir snapshot is not removed. I
> > wouldn't want to do this, and certainly not document it, since it's way
> saner
> > to use pg_start_backup() etc.
>
> Yes, I have wanted to document that WAL-at-the-end is sufficient for
> non-atomic snapshots assuming the needed WAL is there. However, even if
> the WAL is backed up, it doesn't mean we are going to read it during
> crash recovery, i.e. we only read from the last checkpoint or something
> like that. I have no idea how to tell people when this is safe.
>
> My simplistic idea would be to tell people to run a checkpoint right
> before all the snapshots are taken, but even that doesn't seem 100%
> safe. This needs someone who understands the WAL and how to tell people
> a totally safe procedure.
>

The main thing is to provide an easy way to get the filenames of all the
archives that are required. pg_start_backup() and pg_stop_backup() provide
the range of LSNs required to restore, but you have to - correctly -
convert them into xlog file names and copy everything in that range not
just the start and end. There's no simple way to ask PostgreSQL for the
file-list via a query, since we lack arithmetic operators for pg_lsn or any
sort of pg_xlogfile_name_next function or similar. You can easily get the
start and end filenames using pg_xlogfile_name() and rely on lexical
comparision of filenames but that's way less convenient than just getting a
file-list you can feed into rsync / tar / whatever.

It's too complicated. Yes, most users should just use pg_basebackup, but
that doesn't play well with snapshots etc. A pg_xlogfile_name_range
function would be a real help I think, so you could psql -qAt a simple
query to get all the xlogs you must copy from the saved LSNs reported by
pg_start_backup() and pg_stop_backup(). Especially if the docs incorporated
a sample script, including a test that marks the backup aborted/failed if
there are any missing files.

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