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.