Stephen, >This script is a good example of why trying to take a PG backup using shell scripts isn't a good idea.
Your criticism is noted, however, I have used it many times in the past with absolutely no problem. I submitted that script as a possible solution to the op's problem/question. If you have an alternate solution or can make improvements to it, then I am sure the op and I would welcome them. On Wed, Jul 5, 2017 at 9:10 AM, Stephen Frost <sfr...@snowman.net> wrote: > Greetings, > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost <sfr...@snowman.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 > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.