RE: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

I wanted to understand the underlying issue.

I use ZFS snapshots instead of a “correct” backup because with only two machines it allows me to have backups in the main machine and in the secondary too that acts as hotspare at the same time.

To accomplish the same I would need 3 nodes. The main, the replica hotspare and the proper backup.

De: Laurenz Albe<mailto:laurenz(dot)albe(at)cybertec(dot)at>
Enviado: miércoles, 18 de enero de 2023 11:02
Para: HECTOR INGERTO<mailto:hector_25e(at)hotmail(dot)com>; Magnus Hagander<mailto:magnus(at)hagander(dot)net>
CC: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org><mailto:pgsql-general(at)postgresql(dot)org>
Asunto: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

On Tue, 2023-01-17 at 15:22 +0000, HECTOR INGERTO wrote:
> > Another case: a transaction COMMITs, and a slightly later transaction reads the data
> > and sets a hint bit. If the snapshot of the file system with the data directory in it
> > is slightly later than the snapshot of the file system with "pg_wal", the COMMIT might
> > not be part of the snapshot, but the hint bit could be.
> >
> > Then these uncommitted data could be visible if you recover from the snapshot.
>
> Thank you all. I have it clearer now.
>
> As a last point. Making the snapshot to the WAL dataset first or last would make any difference?

Imagine you run DROP TABLE. During the implicit COMMIT at the end of the statement,
the files behind the table are deleted. If the snapshot of "pg_wal" is earlier than
the snapshot of the data files, you end up with a table that is not yet dropped,
but the files are gone.

I won't try to find an example if you now ask what if no checkpoint ends between the
statements, the snapshot on "pg_wal" is earlier and we don't run DROP TABLE.

Why do you go to all this effort rather than performing a correct backup?

Yours,
Laurenz Albe