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 1n34ye-0002Ad-RO for pgsql-admin@arkaria.postgresql.org; Thu, 30 Dec 2021 23:36:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n34yc-0001tb-R7 for pgsql-admin@arkaria.postgresql.org; Thu, 30 Dec 2021 23:36:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n34yc-0001tS-Cw for pgsql-admin@lists.postgresql.org; Thu, 30 Dec 2021 23:36:54 +0000 Received: from mail-oi1-x234.google.com ([2607:f8b0:4864:20::234]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1n34yZ-0005KV-De for pgsql-admin@lists.postgresql.org; Thu, 30 Dec 2021 23:36:53 +0000 Received: by mail-oi1-x234.google.com with SMTP id v6so42066724oib.13 for ; Thu, 30 Dec 2021 15:36:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=+orDghM7FpZq+SfQhbX33wsWOxCvOCRX09g3EmN5Mvg=; b=HvZ5GhSvin+83is7Dvqd7cIfpZiGVUYZB3gMQN03Qe2IKrW+joeeekOK+hUiqL+kjq uPJtesSt9yUFGQVe/Yt3zaFiej2+JmNdf/ooKdcbH0meLwXIJHmRGoep0S2CX7pGres9 N+hx+aMpvG3DbCIlEguYTHFZ11RsI/37KBeSBcVldMTA+bqxkPEOy9TDvOdDi8hEOhU+ H2U05dX6c9JSDjzvb5W6cbhfwuvnSrS0a5UtThBY9gq6Sl3s//O0I3d7nt+a8XbkuC7d GJZZc5lFQd55UmtTRwwtND9qBceYQzD7sHCjozchH5Zyamd+t67YeD9Tv/LL73sVV45K vy/w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=+orDghM7FpZq+SfQhbX33wsWOxCvOCRX09g3EmN5Mvg=; b=yYescUQT7ypLpXsIfYFkwyg/PB3o2+N2D80UtS6sbajdQYv7D8OWmEBF4BPFjTZ4fu vn4TrRxKnRMavxBfMvBm94uOSmM/tV09HF/xtyBzpFKLPta174BAOITC5goPSi6Y8OI3 l9CooaBckyE4sA3SR5VTrw+YpS/XTk5D6it5kOiuBd5WS36LnjfyYCF+rjG37052gUaP Ir8FJ76CiXW19+mqjhHI78wKReHyv/6PC+eTZLg64cD2JS4+0UxSoyocaYe7u5JagZ3d 0JGMQZ7rb0Gk8ZTAUOo3bDrAjiHLaGC4gxFCd7FEzj+iWap5ZuORmVxI9r4xn0QCbZUr nM3w== X-Gm-Message-State: AOAM532LSlkOmeo7Wjy5V+d8cu0PfdgjDQ+FEd0OfbU2NWEZww6y7TUV YUrZsCPEIknxJE1oL2BfmCsrBNNLRLY= X-Google-Smtp-Source: ABdhPJwLWsoStNUSk6NZ9RSPpkbJD1YFJSr89qTSXiEqaY4J+E8WlDhO95gXkLz1IPe0eagwNyUC3g== X-Received: by 2002:aca:d0e:: with SMTP id 14mr22830720oin.58.1640907409029; Thu, 30 Dec 2021 15:36:49 -0800 (PST) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id 52sm5014353oth.52.2021.12.30.15.36.48 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 30 Dec 2021 15:36:48 -0800 (PST) Subject: Re: Is file system replication sufficient to recovery? To: pgsql-admin@lists.postgresql.org References: <2276564.1640884845@sss.pgh.pa.us> <2286514.1640886731@sss.pgh.pa.us> From: Ron Message-ID: <00239b20-617f-4bcf-cba5-bfa260cd3dd0@gmail.com> Date: Thu, 30 Dec 2021 17:36:47 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.14.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------0DC8CB1BCC654C3786C589D8" Content-Language: en-US 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. --------------0DC8CB1BCC654C3786C589D8 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Use PgBackRest instead of rolling your own solution. On 12/30/21 4:36 PM, Tom Korach wrote: > True. So we thought to use: > 1. Periodic pg_basebackup with WAL archiving to allow recovering in case > of corruption/mistakes. > 2. EBS snapshots to quickly (minutes) recover from disk issues (at the > cost of uncommitted transactions being lost - it's not critical). > > According to the documentation, it seems that Postgresql will enter > recovery mode from the file-system snapshot: > > An alternative file-system backup approach is to make a “consistent > snapshot” of the data directory, if the file system supports that > functionality... The typical procedure is to make a “frozen snapshot” of > the volume containing the database,... > > This will work even while the database server is running. However, a > backup created in this way saves the database files in a state as if the > database server was not properly shut down; therefore, when you start the > database server on the backed-up data, it will think the previous server > instance crashed and will replay the WAL log. > > So there are two resiliency goals (1) overcoming corruption and 2) quickly > handling disk failure). So far we are using streaming replication to > handle #2, but we want a simpler setup. > > If the above does not make sense, what other solution (beside replication) > would suffice? > > Thanks, > Tom > > > On Thu, Dec 30, 2021 at 4:30 PM Paul Carlucci > wrote: > > Repeat after me: "RAID is not a backup". Write it down, chisel it into > your monitor, tattoo it on your arm.  Yes, DRBD replicates data at the > block level but if you do a mass update and scramble your data in one > place then you still have two perfectly identical copies of bad data. > > DRBD is pretty neat in that you create a block device under DRBD, that > device is replicated elsewhere, and then on top of that device you > format a filesystem or can use it as a raw device.  As writes happen > locally they get shuffled to the other side. > > --Paul D. Carlucci > > On Thu, Dec 30, 2021, 2:31 PM Tom Korach > wrote: > > > Maybe I'm missing something, but AFAIK plain old RAID will not > protect > > you against any scenario except failure of a single disk.  It > certainly > > won't do anything to help you revert to a prior database state. > > The idea for RAID came from > https://www.postgresql.org/docs/current/different-replication-solutions.html > > > File System (Block Device) Replication > > A modified version of shared hardware functionality is file > system replication, where all changes to a file system are > mirrored to a file system residing on another computer. The > only restriction is that the mirroring must be done in a way > that ensures the standby server has a consistent copy of the > file system — specifically, writes to the standby must be done > in the same order as those on the primary. DRBD is a popular > file system replication solution for Linux. > > DRBD seems to work similar to RAID but over network, but I might > be wrong. > > According to that link > (https://www.postgresql.org/docs/current/backup-file.html > ): > > An alternative file-system backup approach is to make a > “consistent snapshot” of the data directory, if the file system > supports that functionality (and you are willing to trust that it > is implemented correctly). > > The typical procedure is to make a “frozen snapshot” of the volume containing the database > > > So could the following backup architecture make sense? > 1. Periodic snapshots using EBS mechanism (to get consistent > snapshots). > 2. Periodic pg_basebackup + WAL file archiving ( to allow > reverting to a previous step if we e.g. mistakenly drop a table). > > Thanks, > Tom > On Thu, Dec 30, 2021 at 12:52 PM Tom Lane > wrote: > > Tom Korach > writes: > >> What do you mean exactly by "file-system replication"? > > > RAID1 setup (specifically, between two disks or EBS volumes > [on AWS]), > > using LVM. > > Maybe I'm missing something, but AFAIK plain old RAID will not > protect > you against any scenario except failure of a single disk.  It > certainly > won't do anything to help you revert to a prior database state. > > The docs page I pointed you to is part of a chapter that lays > out all > the backup methods the PG community considers reliable.  I > strongly > suggest sticking to one of those and not trying to take shortcuts. > (The following chapter on high-availability setups is relevant > reading as well.) > >                         regards, tom lane > -- Angular momentum makes the world go 'round. --------------0DC8CB1BCC654C3786C589D8 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Use PgBackRest instead of rolling your own solution.

On 12/30/21 4:36 PM, Tom Korach wrote:
True. So we thought to use: 
1. Periodic pg_basebackup with WAL archiving to allow recovering in case of corruption/mistakes.
2. EBS snapshots to quickly (minutes) recover from disk issues (at the cost of uncommitted transactions being lost - it's not critical). 

According to the documentation, it seems that Postgresql will enter recovery mode from the file-system snapshot: 
> An alternative file-system backup approach is to make a “consistent snapshot” of the data directory, if the file system supports that functionality... The typical procedure is to make a “frozen snapshot” of the volume containing the database,... 
> This will work even while the database server is running. However, a backup created in this way saves the database files in a state as if the database server was not properly shut down; therefore, when you start the database server on the backed-up data, it will think the previous server instance crashed and will replay the WAL log. 

So there are two resiliency goals (1) overcoming corruption and 2) quickly handling disk failure). So far we are using streaming replication to handle #2, but we want a simpler setup. 

If the above does not make sense, what other solution (beside replication) would suffice?

Thanks,
Tom


On Thu, Dec 30, 2021 at 4:30 PM Paul Carlucci <paul.carlucci@gmail.com> wrote:
Repeat after me: "RAID is not a backup". Write it down, chisel it into your monitor, tattoo it on your arm.  Yes, DRBD replicates data at the block level but if you do a mass update and scramble your data in one place then you still have two perfectly identical copies of bad data.

DRBD is pretty neat in that you create a block device under DRBD, that device is replicated elsewhere, and then on top of that device you format a filesystem or can use it as a raw device.  As writes happen locally they get shuffled to the other side.

--Paul D. Carlucci

On Thu, Dec 30, 2021, 2:31 PM Tom Korach <tom@safekeep.com> wrote:
> Maybe I'm missing something, but AFAIK plain old RAID will not protect 
> you against any scenario except failure of a single disk.  It certainly
> won't do anything to help you revert to a prior database state.

File System (Block Device) Replication

A modified version of shared hardware functionality is file system replication, where all changes to a file system are mirrored to a file system residing on another computer. The only restriction is that the mirroring must be done in a way that ensures the standby server has a consistent copy of the file system — specifically, writes to the standby must be done in the same order as those on the primary. DRBD is a popular file system replication solution for Linux.

DRBD seems to work similar to RAID but over network, but I might be wrong. 

> An alternative file-system backup approach is to make a “consistent snapshot” of the data directory, if the file system supports that functionality (and you are willing to trust that it is implemented correctly). 
> The typical procedure is to make a “frozen snapshot” of the volume containing the database


So could the following backup architecture make sense? 
1. Periodic snapshots using EBS mechanism (to get consistent snapshots). 
2. Periodic pg_basebackup + WAL file archiving ( to allow reverting to a previous step if we e.g. mistakenly drop a table). 

Thanks,
Tom
On Thu, Dec 30, 2021 at 12:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tom Korach <tom@safekeep.com> writes:
>> What do you mean exactly by "file-system replication"?

> RAID1 setup (specifically, between two disks or EBS volumes [on AWS]),
> using LVM.

Maybe I'm missing something, but AFAIK plain old RAID will not protect
you against any scenario except failure of a single disk.  It certainly
won't do anything to help you revert to a prior database state.

The docs page I pointed you to is part of a chapter that lays out all
the backup methods the PG community considers reliable.  I strongly
suggest sticking to one of those and not trying to take shortcuts.
(The following chapter on high-availability setups is relevant
reading as well.)

                        regards, tom lane

--
Angular momentum makes the world go 'round.
--------------0DC8CB1BCC654C3786C589D8--