Re: pg_start/stop_backup non-exclusive scripts to snapshot

On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost 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.

not saying to use scripts or pg tools, but if OP needs a script, may
consider perl module IPC::Run instead of shell script - easy to use
interacting with subprocesses over filehandles or pipes (even supports
ptys). that way you can keep your active psql session, pass input to it,
view and process it's output, while doesn't block you from doing other
things meanwhile

of course it's perl, not shell, but looks "scripty" and can do what OP
wants

> 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