Re: AW: [Extern] Re: consistent postgresql snapshot

"Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch> writes:
> I don't want to do use the normal backup algorithm where pg_start_backup + pg_stop_backup will fix any fractured block and I am required to have all archived logfiles, therefore.
> I want to produce an atomic consistent disk snapshot.

[ shrug... ] You can't have that. There is never any guarantee that
the on-disk database files are fully up-to-date while the Postgres
server is running, because there may be updates sitting in buffers
in shared memory that have not been written out yet. We achieve
crash safety by ensuring that the WAL log contains sufficient info to
recreate any such updates by replaying WAL from the last checkpoint.
But without going through that replay process, the data visible in
the filesystem may be inconsistent. Even taking a disk snapshot
immediately after a checkpoint won't help, because we use spread
checkpoints. These points are independent of the possibility that
any one block write is non-atomic, although that's surely a factor
as well.

The only way you could get a consistent on-disk image is to shut
the server down (being sure to do a clean not "immediate" shutdown)
and then take the snapshot.

regards, tom lane