I'm building a system which requires a master db (BIG master) that contains things like accounts, balances, actions, etc.
This db then needs to be accessible at a physical place, like a fairground say by point of sale systems. The accounts are used by customers at the fair to buy things with their balance.
The network connectivity locally is fine, i.e. we can create a wifi network and have a copy of the master db available locally (SMALL master) . The issue is that network connectivity to the outside is patchy/non-existent for hours.
My thinking is that I need master to master replication because people could be signing up online (to BIG master) and then head to the fair. People will also be spending their balance at the fair (SMALL master) and obviously we can't have people spending their balance in 2 places (at the fair and online)
I'm using Postgres (latest) and was thinking of using bucardo for the replication.
Is this the best solution for this problem? Is there another solution to patchy db sync?
Best Answer
Bucardo is the standard tool for this sort of job. It's well tested to run when the two servers are disconnected from each other for a while. You could use any master/master replication approach instead: Londiste, Slony, or the early release of BDR from 2ndQuadrant.
With the network setup you've described, you will be hard pressed to avoid race conditions here in all conditions no matter what you do. The only way to keep someone from spending money in both locations here is to setup a two-phase commit transactions or do some form of synchronous replication, basically only putting the record on one machine if it's coordinated on both. One nice thing about Bucardo is that it's designed expecting this will happen. You can setup a conflict resolution mechanism, to recognize and potentially resolve conflicts on nodes when things are changed on both. The other multi-master solutions are not aiming as directly at the disconnected network / conflicting change problems.