I'm trying to create a simplest possible db cluster for my application and i'm not sure if that's the best way to achieve that so i'm gonna try to explain my situation the best i can.
Im basically running two instances of my app on two separate servers – one in US and one in Europe. I'm looking for a way to have one single database for both of them (i'm not interested in situation in which both apps has completely different data set in separate databases) while keeping the latency between app and db as low as possible.
It would be the best and the cheapest ("cheap" is fine in my situation because i'm not aiming to make my app super highly available, i just want the latency to be tolerateable for my users. And by cheap i mean as smallest amount of servers involved as possible.) to have two separate postgresql servers (one in US and one in EU) dedicated for each app instance BUT with the same dataset.
That's why my main question is – is that possible to have two PostgreSQL servers (one server for US and one for EU) which synchronises their contents with each other? I don't even need that small cluster to be server-down-proof (i'm not sure how that's called but i'm talking about the probably most common case and target of 99% of tutorials related to that topic on the internet – when one database is down, use the backup one)
If not, what would be the cheapest and most efficient solution to this problem?
Thanks in advance for any clues!
Best Answer
As far as which server Bucardo should run on, if both of your servers have the same resources, I would just pick one, because in multi-master, with two servers, the bucardo daemon won't have anything to do in a situation where the other server is down anyway. When the other server comes back up, you should just be able to restart the bucardo daemon and have it sync from the server that didn't go down.
https://mail.endcrypt.com/pipermail/bucardo-general/2013-June/001882.html
If you need something with HA, then, I would ask specifically on the bucardo-general mailing list, which is a wealth of knowledge.
You also might find these resources useful for dealing with Bucardo, in addition to the mailing list and the documentation.
Bootstrap Bucardo Multi-Master
Bucardo Multi-Master for PostgreSQL
Conflict Handling with Bucardo