Syncing two (or more) PostgreSQL 9.1 clusters

Question

This question assumes the use of PostgreSQL 9.1.

Say I have two PostgreSQL database clusters. Each cluster contains three PostgreSQL servers. A master, a slave synced via synchronous replication and a standby synchronous replication server. One cluster is hosted in London and the other is hosted in Tokyo.

I have a web app that directs users to the nearest server to them. So I have two instances of the web app running. Each directly connected to one of the PostgreSQL clusters. What would be the best way to keep the two PostgreSQL clusters in sync with each other? I know I could do synchronous replication between the two masters in London and Tokyo but that would add a long time on top of any write queries because the PostgreSQL server would have to wait for a response from the remote system which would (at best) probably add another 200ms of latency to the whole operation.

Does anyone have any suggestions for solving this issue?

Thank you for any help :).

Edit: Also it would be good if the answer took into out account the fact that I may want to expand the system to include a third or a fourth PostgreSQL cluster. I'm lost on the best way to handle syncing between so many different clusters.

Answer 1

There isn't currently anything in the PostgreSQL core which would help with that, but there is work in progress to do what you want that is likely to make it into version 9.3, which will probably be released in the summer of 2013. If you can't wait for that, there are a number of solutions available from third parties. From what you describe, Bucardo sounds like it might be the best fit.

A new option, which recently hit production release 1.0, is Postgres-XC. It is exciting technology, but if you have a stable production environment you might want to just keep an eye on it for now; it's pretty "bleeding edge" at the moment, and they haven't yet figured out how to incorporate all of the features of normal PostgreSQL in that environment.

• Cromulent: Thank you for the information. I'll certainly checkout Bucardo and see what is upcoming for PostgreSQL 9.3.
• kgrittn: Just to be clear, 9.2 is the release which will be out later this summer; 9.3 development is still in its early stages, and the first "work in progress" versions of built-in multi-master replication are currently being reviewed by developers. There may not be a lot to find on it just yet outside of the PostgreSQL developer community. BTW, I neglected to mention an option not on the Wiki page; so I'll add it to the answer now.