Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHBS9-0007BR-AZ for pgsql-general@arkaria.postgresql.org; Sun, 15 Jan 2023 22:26:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pHBS7-0007f5-RD for pgsql-general@arkaria.postgresql.org; Sun, 15 Jan 2023 22:26:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHBS7-0007ev-5r for pgsql-general@lists.postgresql.org; Sun, 15 Jan 2023 22:26:11 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pHBS2-0008Rc-5p for pgsql-general@lists.postgresql.org; Sun, 15 Jan 2023 22:26:09 +0000 Received: by mail-qt1-x832.google.com with SMTP id fd15so13459799qtb.9 for ; Sun, 15 Jan 2023 14:26:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=rPBxuOKZimpA4Qzt4faly2YHIYrmA7e3H/4B8+omd8s=; b=Lqh2pnYZnEiOXLeubVgF88IjAZehf2bPauJzKUt2x30FZi0boNrYXDOBZU4j91ULpN OO42LCqmEhrs6HU5ls/jp/LCXrx01aWvXvbEodMpGoqL6qbI8c7JIQLTGEj3HZDoMfk1 wRnjAMpyvxzrn3XPaQIwKfbkN8kvJX+WEK2m9jq0j5xdNzfx3MCwroV8EurL9cI1YwFY Su2RPvF0ots0kD/VO3vKw/Refth9Cu2zhGwxHd67KxA+4TDQeCpWzeYT4PIBJh8FgpVX mLqaXXcJpyjLXDFHzxY5LQien870vTTx9eXHngrWBTNPH21wH3F8crHRyKPsqO+/ahUZ cZcA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=rPBxuOKZimpA4Qzt4faly2YHIYrmA7e3H/4B8+omd8s=; b=5BE6axQpfM0+wwifZRvx/10KDnwC9D7869rbToYERPIMFC8ZbkhT6llj176Hq/1bIr Tya5Y3sLHM2DFD5uUAz8cZ/lubRXWeJf9i8mZwzavfCKTLkMoURSOX3j7n76d158zulF sXp9MGAdjl0bgpKmtPf5UGFb+UNxMfRu//8PCY0t3zBuSjPHoRbl1InY6+dWY/dBo6QE ISEKjTAAY0NmWYfpFkbjLQ4odL+bEU24Tu8mkYZmN2fH9Q4bmW9ynHoDkj80mrX39TEZ 1/xRkJLobBmf5jRUJV7wzETbmvXjCXFhPSTXNBca4G6FWFLzgHJz/yqNW8ZuVYXlGJll 0YUA== X-Gm-Message-State: AFqh2kqfwgFopyCbzlo0ZlT4ZGDB9v/qwRR87syc3STQohmA1jcl05d7 Ofoo4oW4zHqe+R3zTZOnNFOZIcWEEOo= X-Google-Smtp-Source: AMrXdXtqaaNarBek4xeaByrsoyyGWFBQqZF39ESBpO1SMITmtYFHo3MA5Uv7CM7k/NsUxl/87mBt8Q== X-Received: by 2002:ac8:7445:0:b0:3b1:80ab:38e with SMTP id h5-20020ac87445000000b003b180ab038emr19423670qtr.0.1673821564435; Sun, 15 Jan 2023 14:26:04 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id h6-20020ac81386000000b003a7fef41526sm13692074qtj.77.2023.01.15.14.26.03 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 15 Jan 2023 14:26:04 -0800 (PST) Content-Type: multipart/alternative; boundary="------------Aq0vpSgHkwh7UbX3s6KIX0sI" Message-ID: <25754f73-1e51-e868-ab5c-9cc290da5afa@gmail.com> Date: Sun, 15 Jan 2023 16:26:03 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Aq0vpSgHkwh7UbX3s6KIX0sI Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > *Enviado: *domingo, 15 de enero de 2023 20:36 > *Para: *HECTOR INGERTO > *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 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() // // 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/ > >  Work: https://www.redpill-linpro.com/ > > -- Born in Arizona, moved to Babylonia. --------------Aq0vpSgHkwh7UbX3s6KIX0sI Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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
Enviado: domingo, 15 de enero de 2023 20:36
Para: HECTOR INGERTO
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/
 Work: https://www.redpill-linpro.com/

 


--
Born in Arizona, moved to Babylonia.
--------------Aq0vpSgHkwh7UbX3s6KIX0sI--