Re: PITR Questions

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461