Re: Postgresql replication - Mailing list pgsql-general

carlosreimer@yahoo.com.br (Carlos Henrique Reimer) writes:
> Currently we have only one database accessed by the headquarter and
> two branches but the performance in the  branches is very poor  and
> I was invited to discover a way to increase it.
>
> One possible solution is replicate the headquarter DB into the two
> branches.
>
> I read about slony-i, but then the replicated DBs will be read-only.

Correct.

> Pgcluster is a sync solution and I think is not fited for us becase
> the replicated DBs will be located remotely and we have a lot of
> updates on the DBs.

Unfortunately, pgcluster isn't much maintained anymore.

> I think I´m looking for a master-slave assynchronous solution. I
> know pgReplicator can do it, but I think the project is not active
> any more.

Slony-I is a master/slave asynchronous replication system; if you
already considered it unsuitable, then I see little likelihood of
other systems with the same sorts of properties being suitable.

What could conceivably be of use to you would be a *multimaster*
asynchronous replication system.  Unfortunately, multimaster
*anything* is a really tough nut to crack.

There is a Slony-II project ongoing that is trying to construct a
more-or-less synchronous multimaster replication system (where part of
the cleverness involves trying to get as much taking place in an
asynchronous fashion as possible) that would almost certainly be of no
use to your "use case."

The most successful "multimaster asynchronous" replication system that
I am aware of is the PalmComputing "PalmSync" system.

It would presumably be possible to use some of the components of
Slony-I to construct a multimaster async replication system.  A
pre-requisite would be the creation of some form of "distributed
sequence" which would try to minimize the conflicts that arise out of
auto-generation of sequence numbers.

But beyond that lies the larger challenge of conflict resolution.

Slony-I, as a single-master system, does not need to address
conflicts, as changes must be made on the "master" and propagate
elsewhere.

Synchronous multimaster systems address conflicts by detecting them
when they occur and rejecting one or another of the conflicting
transactions.

Asynchronous multimaster systems require some sort of conflict
management/resolution system for situations where tuples are being
concurrently updated on multiple nodes.  How that is managed is, well,
troublesome :-(.  The PalmSync approach is that if it finds conflicts,
it duplicates records and leaves you, the user, to clean things up.
That may not be suitable for every kind of application...
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://cbbrowne.com/info/slony.html
((LAMBDA (X) (X X)) (LAMBDA (X) (X X)))