I was thinking of maybe just having 2nd location receive a PG dump (full or incremental) every so often (an hour to 6 hours) and if the main location fails majorly, restore the PG cluster from the dump and switch DNS settings on the actual sites. I can make sure all website files are always in sync on both locations.
Ben <[EMAIL PROTECTED]> wrote: If you're sure that data loss is unacceptable no matter what happens to either site, then I'm not aware of too many options. As I understand it, pgpool can be configured to send data-altering queries to multiple servers in order to simulate a multi-master cluster, but it's never been clear to me how this works if you use any non- deterministic functions like random(). I'm not aware of any other multi-master synchronous replication solution. The only option I can think of (and it's ugly so hopefully somebody could suggest something more elegant) would be to make postgres sit on top of something like DRBD, so that both sites can stay in sync with every write to disk. Then pick a site at which to run your database, having your application at both sites connect to the active server. In case of a problem, start up postgres at the other site and have your app reconnect. This will be slow. It will eat up your bandwidth. It will add complexity. But it won't loose data. On Dec 29, 2006, at 2:38 PM, Dennis wrote: > - Yes, both sites have to be online and changing data at the same > time. > - data loss is unacceptable > - platform is Gentoo Linux > - downtime of up to 1 day is acceptable as long as there is no data > loss > - throughput latency -> internet over 10megabit line > > > Ben wrote: Sure, there are lots of ways. > Factors that start constraining things > are: > > - do both sites have to be online (making changes to the data) at the > same time? > - how tightly do both sites have to stay in sync? > - is data loss acceptable if one site suffers a disaster? > - what platform are you running on? > - how much throughput latency do you have between sites? > - how much downtime is acceptable in switching sites? > > On Dec 26, 2006, at 11:41 PM, Dennis wrote: > > > Is there any feasible way to achieve geographical redundancy of > > postgresql database? > > > > Say you have a website which uses PG on the backend to read/write > > data and you want to have the website running on 2 separate servers > > distributed geographically and have the data synchronize somehow > > over the internet. > > > > In case one data center fails, website is still up and running from > > 2nd geographical location (from say 2nd DNS server). > > > > Thank you. > > __________________________________________________ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam protection around > > http://mail.yahoo.com > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
