pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas F. O'Connell <tfo@sitening.com>
To: PgSQL Admin <pgsql-admin@postgresql.org>
Subject: Remote On-line Backup
Date: Tue, 28 Mar 2006 14:31:30 -0600
Message-ID: <89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com> (raw)

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




view thread (4+ messages)  latest in thread

Message-ID: <89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com>
Permalink:  ../89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com/
Also on:    postgresql.org/message-id/89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tfo@sitening.com
  Subject: Re: Remote On-line Backup
  In-Reply-To: <89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com>
  References: <89CD4C44-D35B-4D01-9728-D4CCDEC122EE@sitening.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox