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

On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost <sfrost@snowman.net> 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 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.

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

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 <sfrost@snowman.net> wrote:

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

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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 and 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.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 op and 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

On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost <sfrost@snowman.net> wrote:

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 op and 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

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.

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?

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!!

http://www.azquotes.com/quote/874849

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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 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.

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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