Re: pg_start/stop_backup non-exclusive scripts to snapshot

From: Melvin Davidson <melvin6925(at)gmail(dot)com>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: hvjunk <hvjunk(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_start/stop_backup non-exclusive scripts to snapshot
Date: 2017-07-04 23:06:45
Message-ID: CANu8Fizobz8eLw5F9uoCxF1pKviajz47u=dmG+TyczgGfkSu2w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * hvjunk (hvjunk(at)gmail(dot)com) wrote:
> > I’ve previously done ZFS snapshot backups like this:
> >
> > psql -c “select pg_start_backup(‘snapshot’);”
> > zfs snapshot TANK/postgresql(at)`date ‘+%Ymd’`
> > psql -c “select * from pg_stop_backup();”
>
> Hopefully you are also doing WAL archiving...
>
> > Reading the PostgreSQL9.6 documentation, the advice/future is to use the
> non-exclusive method, where I’ll need to keep a session *open* while the
> snapshot takes place, and after that I’ll have to issue the
> pg_stop_backup(false); in that active connection that issued the
> pg_start_backup(‘backup’,false,false);
>
> Right.
>
> > How is this done inside a shell script?
>
> Generally, it's not. I suppose it might be possible to use '\!' with
> psql and then have a shell snippet that looks for some file that's
> touched when the snapshot has finished, but really, trying to perform a
> PG backup using hacked together shell scripts isn't recommended and
> tends to have problems.
>
> In particular WAL archiving- there's no simple way for a shell script
> which is being used for archiving to confirm that the WAL it has
> "archived" has been completely written out to disk (which is required
> for proper archiving). Further, simple shell scripts also don't check
> that all of the WAL has been archived and that there aren't any holes in
> the WAL between the starting point of the backup and the end point.
>
> > Especially how to do error checking from the commands as psql -c “select
> pg_start_backup{‘test’,false,false);” not going to work?
>
> I'd recommend considering one of the existing PG backup tools which know
> how to properly perform WAL archiving and tracking the start/stop points
> in the WAL of the backup. Trying to write your own using shell scripts,
> even with ZFS snapshots, isn't trivial. If you trust the ZFS snapshot
> to be perfectly atomic across all filesystems/tablespaces used for PG,
> you could just take a snapshot and forget the rest- PG will do crash
> recovery when you have to restore from that snapshot but that's not much
> different from having to do WAL replay of the WAL generated during the
> backup.
>
> As for existing solutions, my preference/bias is for pgBackRest, but
> there are other options out there which also work, such as barman.
>
> Thanks!
>
> Stephen
>

Here is a model shell script I use to do a base backup to set up a slave.
See attached ws_base_backup.sh

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Attachment Content-Type Size
ws_base_backup.sh application/x-sh 1.6 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-07-05 05:05:52 Re: 64bit initdb failure on macOS 10.11 and 10.12
Previous Message Stephen Frost 2017-07-04 21:55:03 Re: pg_start/stop_backup non-exclusive scripts to snapshot