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@hagander.net>
> *Enviado: *domingo, 15 de enero de 2023 20:36
> *Para: *HECTOR INGERTO <mailto:HECTOR_25E@hotmail.com>
> *CC: *pgsql-general@postgresql.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@hotmail.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.