pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: HECTOR INGERTO <hector_25e@hotmail.com>
To: Magnus Hagander <magnus@hagander.net>
Cc: "pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Cc: " <pgsql-general@postgresql.org>
Subject: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?
Date: Tue, 17 Jan 2023 09:26:13 +0100
Message-ID: <a80ba7a05a7d6563319cb2fcf886b05d6cb584cd.camel@cybertec.at> (raw)
In-Reply-To: <GV1P189MB2036AFE96E22BF5830C1E22CF5C19@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
References: <GV1P189MB203648062711BB810D564B05F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
<CABUevEwK7mzh6VKmiwQh1JVdhz4+tSxobmjQEnK5=KvTtJY1Xg@mail.gmail.com>
<GV1P189MB20362EF3D3B2BD49D563C848F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
<CABUevExTcY=zQqh=nhUJrpPZYr7Cf9DeBmpH0oUPaURjGv0RgA@mail.gmail.com>
<GV1P189MB20362DB632BD96D47BF6A6E9F5C19@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
<e07983a4d1cd0b5df43f2e5fcdfdb6f43ea0eb75.camel@cybertec.at>
<GV1P189MB2036AFE96E22BF5830C1E22CF5C19@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
On Mon, 2023-01-16 at 14:37 +0000, HECTOR INGERTO 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?
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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
view thread (17+ messages) latest in thread
Message-ID: <a80ba7a05a7d6563319cb2fcf886b05d6cb584cd.camel@cybertec.at> Permalink: ../a80ba7a05a7d6563319cb2fcf886b05d6cb584cd.camel@cybertec.at/ Also on: postgresql.org/message-id/a80ba7a05a7d6563319cb2fcf886b05d6cb584cd.camel@cybertec.at ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients from your mail client; the link fills in the headers below: reply via email Some clients, webmail especially, drop In-Reply-To and References from a link, and the reply then starts a new thread: use the mbox below. To: pgsql-general@postgresql.org Cc: laurenz.albe@cybertec.at, hector_25e@hotmail.com, magnus@hagander.net Subject: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools? In-Reply-To: <a80ba7a05a7d6563319cb2fcf886b05d6cb584cd.camel@cybertec.at> References: <GV1P189MB203648062711BB810D564B05F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM> <CABUevEwK7mzh6VKmiwQh1JVdhz4+tSxobmjQEnK5=KvTtJY1Xg@mail.gmail.com> <GV1P189MB20362EF3D3B2BD49D563C848F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM> <CABUevExTcY=zQqh=nhUJrpPZYr7Cf9DeBmpH0oUPaURjGv0RgA@mail.gmail.com> <GV1P189MB20362DB632BD96D47BF6A6E9F5C19@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM> <e07983a4d1cd0b5df43f2e5fcdfdb6f43ea0eb75.camel@cybertec.at> <GV1P189MB2036AFE96E22BF5830C1E22CF5C19@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM> <a80ba7a05a7d6563319cb2fcf886b05d6cb584cd.camel@cybertec.at> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox