PITR Questions

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