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