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

We regularly use "history" databases.  Put them on slow media, and only take
a backup when data is added to them (monthly, quarterly, etc).

On 1/15/23 15:57, HECTOR INGERTO wrote:
>
> > But you cannot and should not rely on snapshots alone
>
> That’s only for non atomic (multiple pools) snapshots. Isn’t?
>
> If I need to rely only on ZFS (automated) snapshots, then the best option
> would be to have two DB? Each one in each own pool. One HDD DB and one SSD
> DB. Then, the backend code should know on which DB the requested data is.
>
> *De: *Magnus Hagander <mailto:magnus(at)hagander(dot)net>
> *Enviado: *domingo, 15 de enero de 2023 20:36
> *Para: *HECTOR INGERTO <mailto:HECTOR_25E(at)hotmail(dot)com>
> *CC: *pgsql-general(at)postgresql(dot)org
> *Asunto: *Re: Are ZFS snapshots unsafe when PGSQL is spreading through
> multiple zpools?
>
> On Sun, Jan 15, 2023 at 8:18 PM HECTOR INGERTO <HECTOR_25E(at)hotmail(dot)com> wrote:
>
> Hello everybody,
>
> I’m using PostgreSQL on openZFS. I use ZFS snapshots as a backup +
> hotspare method.
>
> From man zfs-snapshot: “Snapshots are taken atomically, so that all
> snapshots correspond to the same moment in time.” So if a PSQL
> instance is started from a zfs snapshot, it will start to replay the
> WAL from the last checkpoint, in the same way it would do in a crash
> or power loss scenario. So from my knowledge, ZFS snapshots can be
> used to rollback to a previous point in time. Also, sending those
> snapshots to other computers will allow you to have hotspares and
> remote backups. If I’m wrong here, I would appreciate being told about
> it because I’m basing the whole question on this premise.
>
> On the other hand, we have the tablespace PGSQL feature, which is
> great because it allows “unimportant” big data to be written into
> cheap HDD and frequently used data into fast NVMe.
>
> So far, so good. The problem is when both ideas are merged. Then,
> snapshots from different pools are NOT atomical, snapshot on the HDD
> pool isn’t going to be done at the same exact time as the one on the
> SSD pool, and I don’t know enough about PGSQL internals to know how
> dangerous this is. So here is where I would like to ask for your help
> with the following questions:
>
> First of all, what kind of problem can this lead to? Are we talking
> about potential whole DB corruption or only the loss of a few of the
> latest transactions?
>
> Silent data corruption. *not* just losing your latest transaction.
>
> In second place, if I’m initializing a corrupted PGSQL instance
> because ZFS snapshots are from different pools and slightly different
> times, am I going to notice it somehow or is it going to fail silently?
>
> Silent. You might notice at the application level. Might.
>
> In third and last place, is there some way to quantify the amount of
> risk taken when snapshotting a PGSQL instance spread across two (or
> more) different pools?
>
> "Don't do it".
>
> If you can't get atomic snapshots, don't do it, period.
>
> You can use them together with a regular online backup. That is
> pg_start_backup() // <snapshot multiple volumes> // pg_stop_backup()
> together with log archiving. That's a perfectly valid method. But you
> cannot and should not rely on snapshots alone.
>
> --
>
>  Magnus Hagander
>  Me: https://www.hagander.net/
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.hagander.net%2F&data=05%7C01%7C%7C4860509b67ea484420fb08daf72fddd4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638094082195595508%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=LRa%2BFTXpoZNsMLMrNLbL6xmgo9I3Mxx2CcCAh6nmguU%3D&reserved=0>
>  Work: https://www.redpill-linpro.com/
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.redpill-linpro.com%2F&data=05%7C01%7C%7C4860509b67ea484420fb08daf72fddd4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638094082195752157%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=ziYhcTa5YOvHZEr2xk2nEKvSjLICE75zRhhCehvzIMs%3D&reserved=0>
>

--
Born in Arizona, moved to Babylonia.