[gtaSAGE-members] pgsql synchronous duplication solution.

[Lennart Sorensen]

On Mon, Mar 24, 2003 at 02:29:06AM -0500, Thamer Al-Harbash wrote:
> I've found reasonable methods of doing asynchronous replication
> on a pgsql cluster of multiple machines:
> 
> http://pgreplicator.sourceforge.net

The documentation seems to indicate a lot of extra cruft to add to
tables to maintain it.  Not sure it is live replication either,
assuming that is necesary.

> Or by rolling my own triggers.

Hmm, I wonder if it would be possible to have all data updates cause one
db to send commands to another.  Of course what happens if one is down
for service then.

> I'd like for a way to do synchronous replication so as to keep
> the entire cluster active and maintain data integrity across them
> atomically. I understand the only real way to do this is via a
> wrapper postmaster that attempts the replication across all the
> databases and queues anything that cannot make it due to a
> temporary failure. Is there any such solution which is in
> production state? Or should I just roll my own?

I found one attempt at doing so, but it did not handle all cases.  I
have considered doing my own, but there seems to be a lot of things to
keep track of when a query does an update to a server.  I guess if you
could garentee that a query would succeed on all servers in an identical
manner, it would be ok, but how do you ensure that, given automaticly
incrementing counters and such?  I haven't tried to roll my own yet, in
the hopes that some day postgres will do it itself in the backend which
is where it belongs in my opinion.

> (DBbalancer is still alpha according to sf.net)

Yeah, and there are things it can't really do (like know to replacate a
select statement if it causes a trigger to update some data, if someone
happens to have set it up that way.)

Lennart Sorensen