Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
Greetings, * mariusz (mar...@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 signature.asc Description: Digital signature
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost 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. 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
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
Melvin, * Melvin Davidson (melvin6...@gmail.com) wrote: > On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frostwrote: > >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 signature.asc Description: Digital signature
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frostwrote: > Greetings, > > * Melvin Davidson (melvin6...@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.paqu...@gmail.com) wrote: > > On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidson > 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.
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
Greetings, * Melvin Davidson (melvin6...@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.paqu...@gmail.com) wrote: > On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidsonwrote: > > 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 signature.asc Description: Digital signature
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
On Wed, Jul 5, 2017 at 10:47 PM, Melvin Davidsonwrote: > 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
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
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 Frostwrote: > Greetings, > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost > 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.
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
Greetings, * Melvin Davidson (melvin6...@gmail.com) wrote: > On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frostwrote: > > 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 signature.asc Description: Digital signature
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frostwrote: > 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
Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot
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 signature.asc Description: Digital signature
[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 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); 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 going to work? Hendrik -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general