Re: Remote On-line Backup

On 3/29/06, Thomas F. O'Connell <tfo(at)sitening(dot)com> wrote:
> I just want to make sure that I've got a good enough understanding of
> the built-in on-line backup facility to be able to minimize data loss
> and unavailability of the database during a remote recovery from on-
> line backup.
>
> 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.

even if it(STOP WAL) was archived in the new machine ,
you should also copy the last partially filled WAL log from pg_xlog
that was created just after the file refrenced in backup file
was archived.

I think the overall process is fine , you may consider
executing a test run skipping 4

Regds
Rajesh Kumar Mallah.

> 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?
>
> --
> 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)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>