Re: Backup and failover process

On Tue, Jul 15, 2008 at 11:08:27AM -0500, Campbell, Lance wrote:
> 1)       On the primary server, all WAL files will be written to a backup directory.  Once a night I will delete all
ofthe WAL files on the primary server from the backup directory.  I will create a full file SQL dump of the database
andput it into the same backup folder that the WAL files are put in.  The backup directory will be rsynced to the
failoverserver.  This will cause the failover server to delete all of the WAL files it has copies of each night.    
> 2)        On the primary server, I will then check periodically with cron during the day to see if there is a new WAL
file. If there is a new WAL file I will then copy it to the fail over server. 
> 3)  At the end of the day I will repeat step #1.

I think your outline sounds rather fragile.  Moreover, I don't
understand why you're planning to delete WAL files from the target
server.  It seems to me you'd be better off using pg_standby along
with some helper applications.  (Command Prompt has a tool we use for
this, and I believe it's been released, but I'll have to check.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/