Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Greetings,

* hvjunk (hvjunk@gmail.com) wrote:
> I’ve previously done ZFS snapshot backups like this:
>
> psql -c “select pg_start_backup(‘snapshot’);”
> zfs snapshot TANK/postgresql@`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