Re: Netapp SnapCenter - Mailing list pgsql-general

Hi Magnus,

> On 18. Jun, 2020, at 16:19, Magnus Hagander <magnus@hagander.net> wrote:
> I don't know specifically about SnapCenter, but for snapshots in general, it does require backup mode *unless* all
yourdata is on the same disk and you have an atomic snapshot across that disk (in theory it can be on different disk as
well,as long as the snapshots in that case are atomic across *all* those disks, not just individually, but that is
unusual).

according to what I know from our storage guys, Netapp does atomic snapshots for each volume. We have the database and
itscorresponding WAL files (pg_wal directory) on the same volume and the archived WALs (archive_command) on another.
Andthe snapshots on those two volumes are not taken at the same time. Currently, the database is set to backup mode
(usingcron) and the storage guys have a window during which they can take the snapshots. 

> So the upthread suggestion of putting data and wal on different disk and snapshoting them at different times is *NOT*
safe.Unless the reference to the directory for the logs means a directory where log files are copied out with
archive_command,and it's actually the log archive (in which case it will work, but the recommendation is that the log
archiveshould not be on the same machine). 

as I said above, pg_wal is a directory in PGDATA at the default location and WALs are archived using the
archive_commandto a different volume. So I guess, we should be safe then. 

> The normal case is that snapshots are guaranteed to be atomic, and thus this millisecond window cannot appear. But
thatusually only applies across individual volumes. It's also worth noticing that taking the backups without using the
backupmode and archive_command means you cannot use them for PITR, only for restore onto that specific snapshot. 
>
> While our documentation on backups in general definitely needs improvement, this particular requirement is documented
athttps://www.postgresql.org/docs/current/backup-file.html. 

since we use backup mode, we should be good with PITR too. I didn't test that myself, but a workmate did and he said it
workednicely. 

So bottom line, SnapCenter means for PostgreSQL just a plain volume snapshot like we currently do with SnapCreator,
usingbackup mode scripts and "archive_command"-ing WALs to a diffent volume, i.e. no change in strategy. It's just that
SnapCentercan't control backup mode as it can with Oracle. 

Thanks for the invaluable input. Also, thanks Ken.

Cheers,
Paul