Re: pg_start/stop_backup non-exclusive scripts to snapshot

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.