[clug] Disconnected multi-master DB?
gxallen at gmail.com
Tue Nov 4 23:10:22 GMT 2008
Chris Smart wrote:
> Anyone have experience setting up asynchronous multi-master PostgreSQL (or
> MySQL) servers, that are disconnected 99.99% of the time? (Connected only
> for purposes of synchronisation.)
> At work we have two completely separate networks with PostgreSQL that must
> (at some stage) hold the same records, all the while being available to take
> new data separately.
> Server 1 takes in some records while at the same time server 2 takes in some
> other records.
> Sync the data so that both servers hold the same records (without conflicts)
> while not using uniquely identifiable primary keys.
Short answer: Yes
Long answer: Ensure your PKs have independent unique generation - e.g.
UUID, or sequence-based with non-colliding offsets, or your own scheme.
Your comment on "not using identifiable primary keys" I took to mean not
relying on communication to the other server to ensure uniqueness ...
independent generation is OK? Know your replication technology
reasonably well - given your async requirements, Bucardo for PG seems
like a good fit. Come up with a conflict resolution scheme ... you just
know that someone is going to ask to extend this to include updates :-)
If you know for certain that you'll only ever be dealing with inserts,
you could also roll your own scheme using pg_dump, and a simple ETL
process to swap the deltas (more details available if you're interested).
Dazed and confused about technology for 20 years
More information about the linux