On 2 July 2016 at 22:42, Bruce Momjian <bruce@momjian.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.