On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings, > > * hvjunk (hvj...@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 > 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.
ws_base_backup.sh
Description: Bourne shell script
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general