Re: [GENERAL] Need advice on keeping backup database up to date
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Need advice on keeping backup database up to date
In response to Matthew Wilson <[EMAIL PROTECTED]>: > > 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
[GENERAL] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
