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

On Mon, Jan 16, 2023 at 3:37 PM HECTOR INGERTO <hector_25e(at)hotmail(dot)com>
wrote:

> > The database relies on the data being consistent when it performs crash
> recovery.
> > Imagine that a checkpoint is running while you take your snapshot. The
> checkpoint
> > syncs a data file with a new row to disk. Then it writes a WAL record
> and updates
> > the control file. Now imagine that the table with the new row is on a
> different
> > file system, and your snapshot captures the WAL and the control file,
> but not
> > the new row (it was still sitting in the kernel page cache when the
> snapshot was taken).
> > You end up with a lost row.
> >
> > That is only one scenario. Many other ways of corruption can happen.
>
>
>
> Can we say then that the risk comes only from the possibility of a
> checkpoint running inside the time gap between the non-simultaneous
> snapshots?
>

No. The risk comes from any write at all, foreground or background,
occurring inside this gap. Checkpoints are only one of many types of writes
that can cause the problems.

The data integrity is dependent on writes happening in the order they are
issued/confirmed when they are synchronous. Thus terms like WAL = Write
*Ahead* Log.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>