Обсуждение: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

Hi there,

 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();”

Reading the PostgreSQL9.6 documentation, the advice/future is to use the non-exclusive method, where I’ll need to keep
asession *open* while the snapshot takes place, and after that I’ll have to issue the pg_stop_backup(false); in that
activeconnection that issued the pg_start_backup(‘backup’,false,false); 

How is this done inside a shell script?
Especially how to do error checking from the commands as psql -c “select pg_start_backup{‘test’,false,false);” not
goingto work? 

Hendrik

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
keepa session *open* while the snapshot takes place, and after that I’ll have to issue the pg_stop_backup(false); in
thatactive 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
goingto 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

Greetings,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost <sfrost@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

On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> Your criticism is noted, however, I have used it many times in the past with absolutely no problem.

Plug off the server on which is stored the backup just after your
script finishes, you have a good chance to be surprised if you try to
restore from this backup later on.

> 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
andI would welcome them. 

Stephen has mentioned two of them, with hundreds of man hours spent in
developing those backup tools to be robust solutions, done by
specialists on the matter.
--
Michael

Greetings,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> 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.

Part of my concern is that such a script is unlikely to show any
problems until it comes time to do a restore- it could be failing now
due to the issues I noted previously without any obvious error being
thrown but with the resulting backup not being viable.  Hopefully that
isn't the case and ideally you're performing test restores of each
backup you take to ensure that it works.

Further, it doesn't address the OP's question, which was specifically
how to avoid using the now-deprecated exclusive backup method that the
script you posted uses.

* Michael Paquier (michael.paquier@gmail.com) wrote:
> On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> > Your criticism is noted, however, I have used it many times in the past with absolutely no problem.
>
> Plug off the server on which is stored the backup just after your
> script finishes, you have a good chance to be surprised if you try to
> restore from this backup later on.

What might be worse would be to pull the plug while the backup is
running and then try to bring the primary back online. :/  That issue is
part of why the API used in this script is now deprecated.

> > 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
opand I would welcome them. 
>
> Stephen has mentioned two of them, with hundreds of man hours spent in
> developing those backup tools to be robust solutions, done by
> specialists on the matter.

Right, there's little sense in trying to perfect a shell script when
proper solutions exist.

Thanks!

Stephen

Melvin,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost <sfrost@snowman.net> wrote:
> >Part of my concern is that such a script is unlikely to show any problems
> until it comes time to do a restore
> As previously stated, the script was used to set up a slave and has done so
> successfully many times. There are subsequent scripts
> that check results.

Ah, the impression was that this was being suggested as a way of
performing regular backups.  For simply creating a replica,
pg_basebackup works pretty well for small systems.  For larger
databases, being able to perform parallel backup/restore is very useful,
even if it's just for building a replica.

> >What might be worse would be to pull the plug while the backup is running
> and then try to bring the primary back online.
> Uh, whom would be so stupid as to do that?

Pulling the plug in the middle of various operations is a good way to
simulate what happens if the system crashes, for whatever reason, and to
make sure that processes and procedures are in place to address such a
failure scenario.

> >Right, there's little sense in trying to perfect a shell script when
> proper solutions exist.
> >>It's better to create something that others criticise than to create
> nothing and criticise others. Go create, have fun!!

Indeed, I'd certainly welcome criticism of pgBackRest.

Thanks!

Stephen

On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost wrote:
> 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
keepa session *open* while the snapshot takes place, and after that I’ll have to issue the pg_stop_backup(false); in
thatactive 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
goingto 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

Greetings,

* mariusz (marius@mtvk.pl) wrote:
> On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost wrote:
> > > 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

Sure, that's more-or-less what pgBackRest does (which is Perl, mostly).

Thanks!

Stephen