Re: Re: Big replication project, please help

Paul Condon wrote:
> 
> Two way replication on a single "table" is availabe in Lotus Notes. In
> Notes, every record has a time-stamp, which contains the time of the
> last update. (It also has a creation timestamp.) During replication,
> timestamps are compared at the row/record level, and compared with the
> timestamp of the last replication. If, for corresponding rows in two

I've implemented a similar two-way replication scheme for an application
some years ago and it works well.  This was written using Progress 4GL,
which has specific triggers for handling replication functionality
(REPLICATION-CREATE, REPLICATION-WRITE and REPLICATION-DELETE) which we
coded to write replication data into a table (Progress also allows you
to grab the whole record as a "RAW" field which we stuffed into a field
in the replication table).

Then we wrote processes to periodically dump the replication tables at
each site, swap them, and apply the updates.

The reason/advantage of having separate replication triggers was that
they would be disabled on replicate-in, but the _other_ triggers could
be left firing if desired.  In fact I think we found it wasn't desired
in most cases (because the changes effected by the triggers were _also_
being replicated), but we did use it in some cases (like where a summary
table was not being replicated, and was being maintained entirely by
triggers at both sites).

Of course we were writing our own replication information with this
one.  We kept before-image records for replication changes, rather than
having modification timestamps for every record, and considered that a
different before-image was a replication conflict.  Since we were
writing the replication ourselves this before-image / after-image
approach worked better than having to add timestamps to every table on
the database.

The _really_ necessary function for achieving this sort of replication
would be a way of getting a raw record before and after the changes -
I'm no PostgreSQL guru, but I think that should be possible in a 3GL
trigger.  The replication itself could be implemented with normal logic
and some flags to indicate whether a process is running normally, or is
replicating data in.  That detection could be handled (e.g.) by having
the replication-in process operate as a special 'replication' user that
would be detected within normal triggers enabling/disabling
functionality as appropriate.

A fairly small 'C'-language routine to operate as a generic replication
trigger should be achievable quite readily within these constraints, I
think.  I imagine it would be best to have such a routine write output
directly to log files, rather than to PostgreSQL tables, given the 8k
record size limitation and current problems holding binary data directly
in PostgreSQL columns.  This is unfortunate, as it would also introduce
concurrency complications for a busy database.  If you can guarantee
table record sizes under 2k you can probably get away with using
PostgreSQL tables for the replication data if you did some sort of
encoding of the raw record images.

This scheme worked really well (continues to work well) for about 4
years now. Conflicts are rare because although there are around 40
people using the application at various locations, they are all all
accessing fairly narrow record sets, especially for update.

In my replication we have left the actual transfer mechanism as far out
of the equation as possible.  In fact we used e-mail for the replication
messages.  When the sites were on dial-up modems we just did it once a
day, but now they are on DSL internet connections we do it much more
regularly.  That much reduces the numbers of collisions too, of course.

Feel free to pick my brains more on this - I should even be able to dig
out all of our design documentation on it from somewhere!

Regards,                Andrew McMillan
-- 
_____________________________________________________________________           Andrew McMillan, e-mail:
Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267