Re: State of multi-master replication options

scott(dot)marlowe(at)gmail(dot)com (Scott Marlowe) writes:
> On Thu, Jan 20, 2011 at 9:53 AM, CS DBA <cs_dba(at)consistentstate(dot)com> wrote:
>> Hi All;
>>
>> I suspect I know the answer to this...
>>
>> What's the current state of multi-master replication for PostgreSQL? Is
>> Bucardo the only true master/master solution out there that might be worthy
>> of a production push?  Is Postres-R a candidate at this point (I suspect
>> not)? Are there any other master/master or preferably  multi-master (3+
>> masters) solutions available?
>
> Unless you roll your own with separate sequence ranges and inherited
> tables via slony, yes, Bucardo seems to be it right now.

Sounds right.

There were several attempts to come up with Slony successors doing
multimaster replication; it turns out to be a really hard problem to do
this in a general fashion, and have efficient results. It probably fits
in with Brewer's CAP Theorem:

<http://en.wikipedia.org/wiki/CAP_theorem>

CAP indicates you can choose between Consistency, Availability, and
Partition Tolerance, and you can notably not have all three.

When building would-be "general purpose" mechanisms for Postgres, it
seems rather undesirable to throw out Consistency; we usually imagine
that being able to require consistency was one of the reasons people
thought it wise to use Postgres in the first place :-). But retaining
consistency is pretty problematic.

-> Work on one node might be inconsistent with what's happening on
another node. Two kinds of examples that would have differing
kinds of behavior include:
a) Maintaining foreign key relationships
b) Managing account balances

-> Evaluating those sorts of consistency so as to *prevent*
inconsistency is a Mighty Expensive thing to do. (Slony-II tried
doing this, and found it punitively expensive to performance, as
well as biting back at applications. Postgres-R is on the same
road, so one may expect certain behaviors there.)

-> Bucardo takes the approach of having "application rules" where you
have to pre-code what to do when it discovers inconsistencies when
trying to apply changes to other nodes. That's not "general
purpose" in the sense that you need to write rules specific to your
system's behavior.
--
http://linuxfinances.info/info/slony.html
Rules of the Evil Overlord #14. "The hero is not entitled to a last
kiss, a last cigarette, or any other form of last request."
<http://www.eviloverlord.com/>