In response to Matthew Wilson <matt@xxxxxxxxxx>: > > 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
