Re: PITR Questions - Mailing list pgsql-general

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