Re: PITR Questions

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