Re: Bidirectional replication

On Tue, May 3, 2011 at 4:19 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Tue, May 3, 2011 at 7:31 AM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
>
>> I have heard good things about Bucardo, though I haven't tried it myself
>> yet. I was warned that it would be risky to have 2 masters that have the
>> same tables modified in both because of issues such as delayed sync, race
>> conditions and other such goodies that may corrupt the meaning of the data.
>
>
> Just to be clear and fair to Bucardo, I would add a few points.
>
> All multi-master replication solutions that use an optimistic
> mechanism require "conflict resolution" cases and code. This is the
> same with SQLServer and Oracle etc.. Referring to a well known problem
> as a race condition seems to introduce doubt and fear into a situation
> that is well understood. Bucardo does offer hooks for conflict
> resolution to allow you to program around the issues.
>
> So if I felt that multi-master replication was the right way to go for
> a solution, Bucardo is a good choice.
>
> Just to add other info: if multi-master replication uses pessimistic
> coherence, then the coherence mechanism can also be a source of
> contention and/or cause the need for alternative kinds of conflict
> resolution.

Yeah. One nasty property that async multi master solutions share is
that they change the definition of what 'COMMIT' means -- the database
can't guarantee the transaction is valid because not all the
supporting facts are necessarily known. Even after libpq gives you
the green light that transaction could fail an arbitrary length of
time later, and you can't rely in the assumption it's valid until
you've done some synchronizing with the other 'masters'. Maybe you
don't need to rely on that assumption so a 'fix it later, or possibly
never' methodology works well. Those cases unfortunately fairly rare
in the real world.

Multi master replication, at least those implementations that don't
hold locks and release the transaction until you've got a guarantee
it's valid and will stay valid, are fundamentally incompatible with
SQL. I know some people do some cool, usable things with that stuff,
but the whole concept seems awfully awkward to me. I suppose I'm a
crotchety, cane shaking fundamentalist, but the old school approach of
dividing work logically and developing communication protocols is
often the best approach to take.

merlin