Thread: PITR Questions
I'm setting up PITR for a client and have a few questions. I have done some googling for real world archive_command examples and haven't really found anything. The example in the PGSQL Docs are qualified by (This is an example, not a recommendation, and may not work on all platforms.) I have it set as follows: archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f' Any comments as to whether or not this is a *good* choice? Also, I'm concerned that this clients website has extended periods of time where it's very low traffic, which will result in the same WAL file being used for long periods of time and not getting archived. Does anyone have a tested script available for grabbing the most recent WAL file? I can write one myself, but it seems this is information that should be posted somewhere. Thanks,
On Thu, Aug 03, 2006 at 05:03:35PM -0400, Matthew T. O'Connor wrote:
> I have it set as follows:
> archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f'
>
> Any comments as to whether or not this is a *good* choice?
Are you also doing the dance with pg_start_backup(), doing a file copy
of main, and then pg_stop_backup()? That's your full backup; the PITR
files are something like incremental backups and need the copy of main
to play against.
Wayne Conrad
Wayne Conrad wrote: > On Thu, Aug 03, 2006 at 05:03:35PM -0400, Matthew T. O'Connor wrote: > >> I have it set as follows: >> archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f' >> >> Any comments as to whether or not this is a *good* choice? >> > > Are you also doing the dance with pg_start_backup(), doing a file copy > of main, and then pg_stop_backup()? That's your full backup; the PITR > files are something like incremental backups and need the copy of main > to play against. Yes, of course. Is there another way?
Chander Ganesan wrote: > Matthew T. O'Connor wrote: >> I have done some googling for real world archive_command examples and >> haven't really found anything. The example in the PGSQL Docs are >> qualified by (This is an example, not a recommendation, and may not >> work on all platforms.) >> >> I have it set as follows: >> archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f' > It doesn't look to be a *bad* choice. I'd definitely recommend > keeping a copy off of the current system - which you do here. You > might also consider keeping a local copy (so you don't have to copy > them back if you have to do a local recovery). I know this can, but what I'm looking for is if someone has written some scripts that I can crib from that offer some additional features such as protection from overwriting an existing file, notification of the admin in case of failure etc.. >> Also, I'm concerned that this clients website has extended periods of >> time where it's very low traffic, which will result in the same WAL >> file being used for long periods of time and not getting archived. >> Does anyone have a tested script available for grabbing the most >> recent WAL file? I can write one myself, but it seems this is >> information that should be posted somewhere. > The checkpoint_timeout value should help with this - its default is > 300 seconds, so you should checkpoint at least once every 5 minutes. I don't see how checkpoint_timeout is relevant. Just because we checkpoint doesn't mean the WAL file will get archived. I have to have 16M of WAL traffic before a file gets archived regardless of check-pointing, or am I missing something? > You could setup a 'hot standby' system that uses a tool like cron to > periodically sync your pg_xlog directory to your backup server (or > just sync it so you have it..) - which might be useful if you go for > long periods of time between checkpoints. A common scenario is to > place one server into a "constant recovery" mode by using a > restore_command that waits for new logs to be available before copying > them. Periodically sync your pg_xlog directory in this case to ensure > that when you need to recover you'll have most of what you need...but > perhaps not all. I say the "hot standby" is a common scenario, yet I'm not sure it's even possible since the docs only mention it in passing, and I wasn't able to find anyone example script that implements a restore_command that does this. Am I missing something that is obvious? Thanks, Matt
On Fri, Aug 04, 2006 at 11:04:03AM -0400, Matthew T. O'Connor wrote:
> Wayne Conrad wrote:
> >Are you also doing the dance with pg_start_backup(), doing a file copy
>
> Yes, of course. Is there another way?
Not that I know of. I'm embarassed I ask, since you know what you're
doing.
Wayne Conrad
Wayne Conrad wrote: > On Fri, Aug 04, 2006 at 11:04:03AM -0400, Matthew T. O'Connor wrote: > >> Wayne Conrad wrote: >> >>> Are you also doing the dance with pg_start_backup(), doing a file copy >>> >> Yes, of course. Is there another way? >> > > Not that I know of. I'm embarassed I ask, since you know what you're > doing. When it comes to PITR issues, I do NOT know what I'm doing, so no worries. My knowledge is limited to what I've read in the docs but they left me wanting. Perhaps after I get this up and running I'll try and work on some PITR Docs improvements. Matt
> I don't see how checkpoint_timeout is relevant. Just because we > checkpoint doesn't mean the WAL file will get archived. I have to have > 16M of WAL traffic before a file gets archived regardless of > check-pointing, or am I missing something? Right, I think ;-) If you want finer-grained backup, you have to do something like rsync the current WAL file frequently. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
On Fri, Aug 04, 2006 at 03:46:09PM -0400, Matthew T. O'Connor wrote: > Chander Ganesan wrote: > >Matthew T. O'Connor wrote: > >>I have done some googling for real world archive_command examples and > >>haven't really found anything. The example in the PGSQL Docs are > >>qualified by (This is an example, not a recommendation, and may not > >>work on all platforms.) > >> > >>I have it set as follows: > >>archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f' > >It doesn't look to be a *bad* choice. I'd definitely recommend > >keeping a copy off of the current system - which you do here. You > >might also consider keeping a local copy (so you don't have to copy > >them back if you have to do a local recovery). > > I know this can, but what I'm looking for is if someone has written some > scripts that I can crib from that offer some additional features such as > protection from overwriting an existing file, notification of the admin > in case of failure etc.. Take a look at http://pgfoundry.org/projects/pgpitrha/ Also, note that in 8.1, you have to manually archive the last WAL file after pg_stop_backup(), or you backup is useless until that WAL file fills up on its own and is archived. There's a bunch of new functions in 8.2 that will make a lot of this stuff easier, btw. > >>Also, I'm concerned that this clients website has extended periods of > >>time where it's very low traffic, which will result in the same WAL > >>file being used for long periods of time and not getting archived. > >>Does anyone have a tested script available for grabbing the most > >>recent WAL file? I can write one myself, but it seems this is > >>information that should be posted somewhere. > >The checkpoint_timeout value should help with this - its default is > >300 seconds, so you should checkpoint at least once every 5 minutes. > > I don't see how checkpoint_timeout is relevant. Just because we > checkpoint doesn't mean the WAL file will get archived. I have to have > 16M of WAL traffic before a file gets archived regardless of > check-pointing, or am I missing something? You're not. > >You could setup a 'hot standby' system that uses a tool like cron to > >periodically sync your pg_xlog directory to your backup server (or > >just sync it so you have it..) - which might be useful if you go for > >long periods of time between checkpoints. A common scenario is to > >place one server into a "constant recovery" mode by using a > >restore_command that waits for new logs to be available before copying > >them. Periodically sync your pg_xlog directory in this case to ensure > >that when you need to recover you'll have most of what you need...but > >perhaps not all. > > I say the "hot standby" is a common scenario, yet I'm not sure it's even > possible since the docs only mention it in passing, and I wasn't able to > find anyone example script that implements a restore_command that does > this. Am I missing something that is obvious? See above pgfoundry link. :) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > Take a look at http://pgfoundry.org/projects/pgpitrha/ I had already seen this however it says that this project has yet to release any files, so I thought it was a dead project. Am I missing something? > Also, note that in 8.1, you have to manually archive the last WAL file > after pg_stop_backup(), or you backup is useless until that WAL file > fills up on its own and is archived. Right, I was hoping to find someone who had well written and tested bash script or something that did this.
On Aug 9, 2006, at 10:31 PM, Matthew T. O'Connor wrote: > Jim C. Nasby wrote: >> Take a look at http://pgfoundry.org/projects/pgpitrha/ > > I had already seen this however it says that this project has yet > to release any files, so I thought it was a dead project. Am I > missing something? No, the project hasn't released files (yet), but they are available in CVS. I'll try to at least get a tarball up in the next week. >> Also, note that in 8.1, you have to manually archive the last WAL >> file >> after pg_stop_backup(), or you backup is useless until that WAL file >> fills up on its own and is archived. > > Right, I was hoping to find someone who had well written and tested > bash script or something that did this. I think the project does that, but I can't swear to it. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461