From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Is file system replication sufficient to recovery?
Date: Thu, 30 Dec 2021 17:36:47 -0600
Message-ID: <00239b20-617f-4bcf-cba5-bfa260cd3dd0@gmail.com> (raw)
In-Reply-To: <CALQRbyxHnrGEeMFHdQwbyDCpyy9Qk=XPe6RAPiyxD1ujLkGLrA@mail.gmail.com>
References: <CALQRbyyOqWPNBMmeHEa1T2NKqWHaUvjw7uyqR_vp9syVrD3UeA@mail.gmail.com>
<2276564.1640884845@sss.pgh.pa.us>
<CALQRbyyfjY7=z6Z8EGWj0aqPX0_Uk95v6pQkGoHbCDW=srFOGw@mail.gmail.com>
<2286514.1640886731@sss.pgh.pa.us>
<CALQRbyzu=Ov_KJd57+m+AwL0MQHiM=FtvOTkh=84rOpY9in0sw@mail.gmail.com>
<CAKhGwmCev=wSc-bbUHuA4CJ8QgsJtGY=-CKOcbDquCiyiYkeiw@mail.gmail.com>
<CALQRbyxHnrGEeMFHdQwbyDCpyy9Qk=XPe6RAPiyxD1ujLkGLrA@mail.gmail.com>
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
> <mailto: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
> <mailto: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.
>
> The idea for RAID came from
> https://www.postgresql.org/docs/current/different-replication-solutions.html
> <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
> <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 <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> Tom Korach <tom@safekeep.com <mailto: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.
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Is file system replication sufficient to recovery?
In-Reply-To: <00239b20-617f-4bcf-cba5-bfa260cd3dd0@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