[clug] Disconnected multi-master DB?

[Grant Allen]

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.
>
> I.e.
> 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.
>
> -c
>   

Chris,

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).

Ciao
Fuzzy
:-)

------------------------------------------------
Dazed and confused about technology for 20 years
http://fuzzydata.wordpress.com/