[gtaSAGE-members] pgsql synchronous duplication solution.

[Thamer Al-Harbash]

On Fri, 28 Mar 2003, Lennart Sorensen wrote:

> 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.

It's essentially a daemon that maintains a seperate set of tables
via triggers, and then a tool to sync the databases
together. It's "asynchronous" though which means the replication
is not done in real time. You have to explicitly run the tool
(out of crontab presumably).

It has conflict resolution which is helpful.

This solution does not allow me to run the all the databases as
active because there will be a race condition in between
synchronization. I can use this for an active/fail-over scenario
where one database is handling the load. When it croaks you
_hope_ that most of the data made it through to the fail-over,
and when you bring the primary database back up you should be
able to synchronize whatever data made it through.

It's OK for one project I have but completely useless for other
projects.

> 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,

The problem is even something like DBbalancer cannot do very
much.  It's a metaphysical limitation when using a wrapper
postmaster to replicate data. For example:

insert into foo (creation_time, foo_id) VALUES(CURRENT_TIME, 1);

The result will not be the same on each server. You cannot
guarantee that the CURRENT_TIME function will return the same
time on each server, and it probably never ever will. Not unless
the postmaster rewrites queries and that's just too creepy for
me.

(I think that's what you were alluding when you said there are
some things it cannot do)

I'm current considering doing this like LDAP. One master accepts
updates and replicates it all at once to the slaves. I'll fiddle
with pgreplicator to see if I can get updates to happen
atomically across the slaves. This may be possible.

Very limiting though. I cannot do a read and write in one session
to one database :|

-- 
Thamer Al-Harbash            http://www.whitefang.com/
	(if (> pressure too-much-pressure)
		'play-ac2 'work)