In response to Matthew Wilson <matt@tplus1.com>:
>
> I have been using postgresql for my web application for a while now and
> it has been great.
>
> I want to set up a separate emergency failover server and database in a
> different data center.
>
> In the event that my primary data center becomes inaccessible, I want to
> update a DNS record and then redirect visitors to the backup data
> center.
>
> I am trying to figure out how I can keep the postgresql database in the
> backup data center as up to date as possible.
>
> The ideal solution would keep the backup database updated in real time
> as the primary database changes.
>
> I need advice about the best way to accomplish this.
This sounds like a textbook case for Slony.
Slony will not guarantee that your database is up to the second copy,
but that's part of the beauty of it. The _only_ way you can guarantee
that two databases in different datacenters are perfectly synchronized
at all times is not to let an application move forward until it has
received confirmation from both databases that a transaction has
completed -- and doing that will absolutely kill performance.
Slony will make a best effort. If traffic is low, it will keep the
two withing a few fractions of a second of each other. If traffic
gets busy, the backup will get behind, but when things slow down
again, Slony will get them caught up. As long as your average
database traffic does not exceed the available bandwidth, all will
be well.
Slony will also allow you pick/choose which tables you want to
duplicate. This can optimize things, as it's not normally worthwhile
to replicate things like session tables, and they usually eat up a
lot of bandwidth.
http://www.slony.info
--
Bill Moran
http://www.potentialtech.com