Re: Postgresql replication

On Thursday 25 August 2005 13:03, William Yu wrote:
> As far as I know, nobody has a generic solution for multi-master
> replication where servers are not in close proximity. Single master
> replication? Doable. Application specific conflict resolution? Doable.
> Off the shelf package that somehow knows financial transactions on a
> server shouldn't be duplicated on another? Uhh...I'd be wary of trying
> it out myself.

The most obvious one that does exactly this (generic multi-master
replication) is Lotus Domino. It is not a relational DB, but not sufficiently
far off to stop the analogy.

Domino marks each document with a binary value which identifies the
server (built from a hash of the server name and the time the DB was
created) and a timestamp when it was last modified, and also each document
(record) has an ID (like OIDs). More recent versions also do this at a field
level to avoid conflicts and speed replication. When two servers replicate
they look for all documents modified since the last replication time, and
compare the list. Those only modified on one server are copied across
to the other server replacing the old record and carrying the updated on
server and timestamp with them. When a document is deleted in Domino it
actually does not dissapear, it is reduced to a deletion stub, and this gets
replicated as it has the same ID as the original record. Those that have been
modified on both sides are copied to the other DB, but both records remain and
it is left to the user to resolve conflicts. Field level replication reduces
the need for this considerably. Periodically the deletion stubs are purged,
once all known replicas have replicated.

Domino has absolutely no concept of a master DB.

Obviously this scheme would be difficult to do on a pure relational system.
But with triggers and a few standard fields it would not be impossible to
do for a limited application set. How the user would resolve conflicts would
also be application specific I suspect and how one would relate having two
version of a record in the DB then they both have a field which is supposed to
be unique is also a problem that would have to be resolved (Domino does not
have the concept of unique keys).

David

>
> Bohdan Linda wrote:
> > I would have a slight offtopic question, this is issue only of pgsql or
> > there are some other db solutions which have good performance when doing
> > this kind of replication across the world.
> >
> > Regards,
> > Bohdan
> >
> > On Thu, Aug 25, 2005 at 09:01:49AM +0200, William Yu wrote:
> >>It provides pseudo relief if all your servers are in the same building.
> >>Having a front-end pgpool connector pointing to servers across the world
> >>is not workable -- performance ends up being completely decrepit due to
> >>the high latency.
> >>
> >>Which is the problem we face. Great, you've got multiple servers for
> >>failover. Too bad it doesn't do much good if your building gets hit by
> >>fire/earthquake/hurricane/etc.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly