Re: Docs, backups, and MS VSS

On Sat, Jul 2, 2016 at 10:31:32PM +0800, Craig Ringer wrote:
> - Microsoft VSS is NOT safe, as it fails point 2. It is atomic only on a
> per-file level. You MUST use pg_start_backup() and pg_stop_backup() with WAL
> archiving or automated copy of the extra WAL if you use MS VSS. Most Windows
> backup products use MS VSS internally. You must ensure they have dedicated
> PostgreSQL backup support, using pg_basebackup, pg_dump/pg_restore, or
> pg_start_backup()/pg_stop_backup().

Yes, it would be good to point out that per-file snapshots are insufficient.

> - LVM is safe
>
> - BTRFS should be fine
>
> - Most SAN snapshots are fine, but verify with your vendor
>
>
> 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.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +