On Thu, Jun 18, 2020 at 4:07 PM Paul Förster <paul.foerster@xxxxxxxxx> wrote:
Hi Ken,
> On 18. Jun, 2020, at 15:56, Wolff, Ken L <ken.l.wolff@xxxxxxxx> wrote:
> PostgreSQL doesn’t need to be in a special mode for backups to work.
this is curious. Doesn't the PostgreSQL cluster need to be set to backup mode to use SnapCenter?
I don't know specifically about SnapCenter, but for snapshots in general, it does require backup mode *unless* all your data 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).
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 archive should not be on the same machine).
The problem is, one can't test that and get a reliable answer, because you do 100 backups (snaps) for testing and restore those 100 backups, and all may be ok.
But what about the 1000th snap? Just in that millisecond of the snap, some important information is written to the volume on which the PostgreSQL cluster resides and for some reason, it needs to be restored later and this information is lost or may lead to corruption. This is why backup mode exists, after all. Really, no backup mode by a pre/post script when snapping?
The normal case is that snapshots are guaranteed to be atomic, and thus this millisecond window cannot appear. But that usually only applies across individual volumes. It's also worth noticing that taking the backups without using the backup mode 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 at https://www.postgresql.org/docs/current/backup-file.html.
