Re: Docs, backups, and MS VSS

On 2 July 2016 at 22:31, Craig Ringer <craig(at)2ndquadrant(dot)com> 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().
>

So, I read the manual [1] to confirm this, and I was wrong. VSS should be
fine if used correctly by the backup application, with a single snapshot
used for the entire PostgreSQL data directory. It is in fact block-level
CoW like LVM, or at least the default provider is.

It turns out that on Windows we could actually support safe snapshot based
backups across multiple volumes, too, by shipping a VSS Writer for
PostgreSQL that notifies PostgreSQL to perform a checkpoint and block
future XLogInsert()s until released, so we don't need a fully atomic
snapshot anymore. However, this would only work if the backup application
that the main datadir, xlog and any tablespace volumes be included in a
single VSS snapshot.

In the absence of a VSS Writer for PostgreSQL(provided by the backup app or
PostgreSQL install) and confirmation that the backup app snapshots all
volumes together, then like LVM it'll only be safe if the backup app does
pg_start_backup(), copies, pg_stop_backup() and copies the extra WAL. We
should probably provide a tool to make this easier by listing all the WAL
file names for a range of LSNs.

Anyway - still worth documenting safety requirements, but contrary to what
I remembered VSS looks to be fine.

It looks like a PostgreSQL VSS Writer could make the process a lot
smoother, though, by enumerating all volumes PostgreSQL uses to make sure
they get snapshotted, trying to flush in-progress xacts and pause new ones,
etc.

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