Re: PITR as Online Backup Solution

On Mar 4, 2006, at 3:56 AM, Simon Riggs wrote:

> On Fri, 2006-03-03 at 12:03 -0600, Thomas F. O'Connell wrote:
>> On Mar 3, 2006, at 11:54 AM, Simon Riggs wrote:
>>
>>> On Thu, 2006-03-02 at 16:38 -0600, Thomas F. O'Connell wrote:
>>>> Ideally, I'd be able to take a base backup of a production system,
>>>> copy it to a remote system, which is also the repository for
>>>> segment
>>>> files generated by archive_command, and complete the recovery
>>>> process
>>>> outlined in the docs. From that point, it would make sense to me
>>>> that
>>>> I should be able to continuously replay WAL files against the new
>>>> database (possibly as soon as archive_command generates a new one)
>>>> without having to purge my data directory. Is that a reasonable
>>>> assumption?
>>>
>>> Yes, it was designed to be able to do this.
>>
>> From the docs, I'm having a hard time determining which steps to
>> edit or omit in order to execute this scenario. Is it possible for
>> you (or anyone else on the list) to present an extension of section
>> 23.3.3 <http://www.postgresql.org/docs/8.1/static/backup-
>> online.html#BACKUP-PITR-RECOVERY> that covered the continuous replay
>> scenario? I'd be happy to help contribute a patch to the docs once I
>> understand the procedure a bit better.
>
> The place the primary backs up to is the place where the secondary
> restores from, otherwise all procedures are as documented. Neither
> system knows about the other, so its simpler than maybe you think.
>
> The only thing you need is the wait-for-next-file script.

A few more questions in this thread:

1. With the wait-for-next-file script scenario, what happens with
recovery.conf? Does it ever become recovery.done? What happens in the
event of needing to recover? Does the script need a trigger to say,
"Okay, stop waiting. We need to use what we have right now."?

2. Are there any salient details when performing an online backup
from one machine to another? I'm assuming filesystem is an important
consideration since the recovery process is not like the pg_dump
recovery process. Any other gotchas?

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)