If they both have independent data, that's not quite so bad. I'd just caution you that at some people a request may come in that they want to edit each other's data. I'd make sure and establish a firm plan for that before implementing a solution. Either clearly state it won't be allowed or figure out exactly how it will be handled. Bucardo has some conflict resolution management built in, but you have to be sure that's the method of resolution you want to implement.
If this is a relatively small database, and they will be kept as independent databases, bucardo can be a bit of a chore to set up and maintain. I've been working on a smaller (hopefully easier to use) logical table replication extension.
You could set up replication of the tables that both sites need to some secondary tables made just for replicating the other site's data (perhaps keep it in its own schema). Then you can resolve getting the data in synch yourself whenever it's needed.
Still, I'd definitely recommend going for the single database solution first and then if it really can't work, then go the multi-master route.
On Fri, May 2, 2014 at 5:04 PM, Yuksel Pinarbasi <yukselp@gmail.com> wrote:
Thanks Keith.
Those two branches will mostly be handling independent data.
Only time to time they will need to see a record that the other branch has been created. I don't think there will be ant conflict. Because they will not read or edit the same row in a close time frame.
It seems a Multi Master- Asynchronous replication will be the right solution in the end.
Anyway, I will start with a single database and work my way around later.
Yuksel
You're going down a rather complex path, especially for someone new to full RDBMS systems. Multi-master (as this is frequently called) is one of the most difficult problems to solve in database administration. A poor network between the two locations is going to make it even more difficult to manage. The conflict resolution for both sites editing the same data is more complex of a problem than I think you realize right now. If there's any chance you can stick with a single, more reliable offsite location that both locations can access, I'd highly recommend starting there, perhaps in Amazon EC2 or RDS. Then one site being down isn't a bottleneck to the other.
