Re: pg_start/stop_backup non-exclusive scripts to snapshot

Greetings,

* Melvin Davidson (melvin6925(at)gmail(dot)com) wrote:
> On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > 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.
>
> Here is a model shell script I use to do a base backup to set up a slave.
> See attached ws_base_backup.sh

This script is a good example of why trying to take a PG backup using
shell scripts isn't a good idea. Offhand, there's issues like:

- No check that start_backup was successful
- No check that stop_backup was successful
- No syncing of files to disk anywhere
- Requires running as root (without any particular clear reason why)
- Doesn't check if the database is already in 'exclusive backup' mode
- Doesn't check the return codes for the main 'tar' command
- Uses pipes without checking return codes through PIPESTATUS
- Doesn't capture the output from pg_start/stop_backup
- Doesn't verify that all of the WAL required for the backup was
archvied
- Doesn't check the exit code of the rsync

I'm sure there's other issues also and I do hope it's working enough
that you have viable backups, but I wouldn't use such a script today
(though I wrote plenty like it in the distant past).

Thanks!

Stephen