Re: Remote On-line Backup

On Mar 29, 2006, at 1:43 PM, Simon Riggs wrote:

> On Tue, 2006-03-28 at 14:31 -0600, Thomas F. O'Connell wrote:
>
>> Here are the steps I'm proposing:
>>
>> 1. Set up archive_command in postgresql.conf on oldhost to archive to
>> remote repository on newhost.
>> 2. Perform base backup on oldhost. (I'll probably just use rsync to
>> backup directly to newhost.)
>> 3. On newhost, remove postmaster.pid from $PGDATA, disable
>> archive_command in postgresql.conf, and create clean pg_xlog tree.
>> 4. Stop the postmaster on oldhost.
>> 5. If the WAL file referenced by the backup file in my archive
>> directory on newhost is not archived when the postmaster is stopped,
>> copy it from oldhost to pg_xlog on newhost.
>> 6. Create recovery.conf on newhost.
>> 7. Start the postmaster on newhost.
>> 8. Rejoice when recovery.done appears.
>>
>> The part I most want to make sure I understand well enough is step 5,
>> which I'm understanding to be a modification of steps 2 and 6 from
>> section 23.3.3 in the docs. As I understand it, there's a pretty good
>> possibility that the WAL file referenced by stop_backup() will not be
>> archived by the time I stop the postmaster on oldhost. In which case,
>> I should be in good shape to recover if I have a base backup, the
>> archived WAL files up to that final file referenced by stop_backup(),
>> and the partial segment file referenced by stop_backup(), which
>> should be the only unarchived WAL segment file and just needs to
>> exist in pg_xlog on newhost for things to run smoothly.
>>
>> Does this seem right? Or will I rather want to copy all the contents
>> of pg_xlog from oldhost as they represent current (as of stopping the
>> postmaster) unarchived WAL activity?
>
> The steps above show a one-time migration. If that is what you want
> then
> I suggest that the steps are:
>
> 1. Shutdown oldhost cleanly.
> 2. Copy all data directory and all files to newhost.
> 3. Edit any configuration file changes required
> 4. Startup on newhost
> 5. Delete files on oldhost so it is not started up again.
>
> This will be slower, but has less risk if you are unsure of the
> process.
>
> I'd suggest you follow your own procedure on a test box without step
> (4), so you can check you've done it right before you stop oldhost for
> good. Once you are happy with that, go for it in live.
>
> Best Regards, Simon Riggs

For now, all I need is the one-time migration. The reason I was
hoping to include the additional steps that you roll into step 2 is
to minimize the downtime. I could leave the postmaster on oldhost
running while I perform the base backup.

I'm looking to avoid being unsure of the process, which is why I
posted. :)

Is there any potential for data loss in the original scenario I
proposed?

--
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)