Re: Slony-I for circular replication

Bojidar Mihajlov wrote:
> Hi All;
>
> Can Slony-I be used for circular replication (A
> <replicates> B <replicates> A)
> or there is some kind of problem with locking or
> anything else?

No - Slony is an asynchronous single-master replication scheme. As such
changes are only allowed at a "master" node.

You might want to look at pgpool which I believe can send queries to two
systems at the same time and sort of do this.

> If I avoid insert conflicts will the following
> scenario succeed for the updates :
>    A: X.balance-= 5
>    B: X.balance-= 3
>    A receives the update from B (X.balance -= 3)
>    B receives the update from A (X.balance -= 5)
> or it will lead to deadlock ?

No - see above.

> Nodes A and B will reside at different geografical
> locations.
> I'll use only node A to service both locations in the
> most time and B for redundancy.

Hmm - OK. So that's A=>B, simple enough.

> But the case of broken connection between node A and B
> could lead to scenarios
> like latter because then A and B will service their
> own location.

Don't do that (unless you have no choice). It's much simpler (and
probably cheaper) to have a separate network connection to "A".

 > Could such a scenarios
> be detected and logged ?
 >
> What happen in case of a conflict (dublicated primary
> key/update deleted) ?

No replication system in existence handles conflicts like this
automatically. The "meaning" of a conflict is something that depends on
the application and its context. You would need to handle conflicts
yourself.

What I would do is have replication A=>B with a failover should "A"
become unavailable. If the link between A/B is broken, you'll need your
own system for failover since you'll need to decide whether it's because
one of the databases failed or a more general network problem. If it's
the network, then you'll just have to live without updates for clients
that can only see "B".

HTH
--
   Richard Huxton
   Archonet Ltd