Foresee any problems with this? postgres-failover (long) - Mailing list pgsql-sql

A couple weeks back I wrote in with a few questions and comments asking
about the possibilities of creating a re-do log in Postgres for the purpose
of creating a Standby database.  That is, a database that would be
reasonably current (within 5 minutes) and available to take over within one
minute should a disaster occur on the primary database server.

Considering the difficulty and complexity of the re-do log approach, I've
decided to approach the problem in another way.  Granted, the techniques I'm
using might need to be optimized if you have tables with a large number of
rows, but it turns out that for the small-medium (but non the less critical)
sized databases we are using here, this technique seems to work just fine.

This is actually a two phase process.  

Phase I is making a reasonably up to date copy of the master db server on
the slave db server without taking down the master server.  
Phase II is creating robust failover mechanisms that work with Postgres in
mind.

The basic idea is that I have two identical database servers with 2 NICs
each and each with its own raid.  One node is set as a default master and
the other as a default slave in regards to serving database and network
requests.   1 NIC on each machine is attached through a switch to a web
farm, and the other NIC creates a private network between the two nodes.   

The private nework is used to set up a symmetric NFS mount between the two
machines.  (By symmetric I mean that the mount points are identical on each
machine, without being cyclical, so that the same scripts on each machine
will still function properly in the event of a node role reversal, eg. slave
becomes master.)

On the master server every 5 minutes a cron job runs a "find
$PGDATA/base/{databasename} -mmin -5" in each database data directory to
determine if there have been any recent changes.  If so,  a pg_dump is
issued on either that entire database or specific tables within that
database. (This is defined elsewhere through special config files.)  Once
pg_dump is finished, it is greped for ERRORs or FATALs and  if non are found
the file is copied to an archive directory and a ready.{databasename} file
is created.

Every minute the slave, again through a cronjob, checks the NFS mount
looking for a ready.{databasename} file, if it finds one it creates a
{databasename}.LCK file to keep the master from tampering with anything
while it copies the file to a local directory and then uses another script
and psql to reload the new data.  There are control files associated with
each database which give the slave server preliminary instructions to
perform before it attempts to apply the pg_dump data file.  e.g.  To connect
to template1, drop the database in question, create that database, reconnect
to it, then run the pg_dump data file.

The scripts are all fairly robust and seem to be working great.  In addition
to the pg_dumps, I'm also dumping and reapplying pg_shadow each time a
backup is made.  

I have considered also copying the differences between the master and slave
PG_LIB directory as part of each backup, but I need to think through that a
little more first.

I'd be interested in talking with anyone else who is running any system
similar to this, or fielding comments in general.   Specifically, I'd like
to hear about potential pit-falls I may not have considered or success
stories from others using similar techniques.

In the interest of brevity, I won't go into how I've done the failover
portion just yet.  I'd like to cover this topic first to make sure I haven't
made any glaring errors.

Thanks,
Bryan Ingram
bingram@sixtyfootspider.com