From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?
Date: Sun, 15 Jan 2023 16:26:03 -0600
Message-ID: <25754f73-1e51-e868-ab5c-9cc290da5afa@gmail.com> (raw)
In-Reply-To: <GV1P189MB20362EF3D3B2BD49D563C848F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
References: <GV1P189MB203648062711BB810D564B05F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
<CABUevEwK7mzh6VKmiwQh1JVdhz4+tSxobmjQEnK5=KvTtJY1Xg@mail.gmail.com>
<GV1P189MB20362EF3D3B2BD49D563C848F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM>
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%7C...;
> Work: https://www.redpill-linpro.com/
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.redpill-linpro.com%2F&data...;
>
--
Born in Arizona, moved to Babylonia.
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: ronljohnsonjr@gmail.com
Subject: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?
In-Reply-To: <25754f73-1e51-e868-ab5c-9cc290da5afa@gmail.com>
References: <GV1P189MB203648062711BB810D564B05F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM> <CABUevEwK7mzh6VKmiwQh1JVdhz4+tSxobmjQEnK5=KvTtJY1Xg@mail.gmail.com> <GV1P189MB20362EF3D3B2BD49D563C848F5C09@GV1P189MB2036.EURP189.PROD.OUTLOOK.COM> <25754f73-1e51-e868-ab5c-9cc290da5afa@gmail.com>
* 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