Thread: Need advice on keeping backup database up to date

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.

Thanks in advance.


Matt

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

Matthew Wilson wrote:
> 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.

What you are after is called "replication". There are a couple of paid
solutions out there and a few free options.

The "default" open-source replication system for PG is called Slony and
is trigger-based.
   http://www.slony.info/

Skype do their own replication, uses Python and I don't know much more
about it than that:
   http://pgfoundry.org/projects/skytools/

If you don't need to be quite so up-to-date and don't mind replicating
an entire cluster look at WAL archiving. See Ch 23 of the manuals (and
some other chapters). Note that this is only viable if both servers are
identical (so, both 32-bit Intel running Debian for example).

--
   Richard Huxton
   Archonet Ltd