Re: Asychronous database replication - Mailing list pgsql-general

smanes@magpie.com (Steve Manes) writes:
> I have a project on my plate which will involve potentially hundreds
> of PG8 databases in the field which will need to synchronize data with
> a central database.  The company is a secular nonprofit which delivers
> medical services to underprivileged kids as well as to disaster
> victims like those hit by Katrina.  We have six mobile medical units
> there now as a matter of fact.
>
> Some of these databases will have 24/7 net connections; some may not
> even have telephone access for days so traditional database
> replication techniques won't work.  I've not found any third-party
> software yet which could help us here so I'm proceeding on the
> assumption that we're going to need to build it ourselves.
>
> This sort of database topography is virgin ground for me but I'm
> guessing that others here have encountered this challenge before and
> will have some tips/advice/war stories to steer us in the right
> direction.

Well, what you clearly want/need is asynchronous multimaster...

I'm involved with Slony-I, which is asynchronous but definitely,
consciously, intentionally NOT multimaster.

It seems to me that you might be able to usefully cannibalize
components from Slony-I; the trigger functions that it uses to
intercept updates seem likely to be useful.  Some of the data
structures would be useful, notably "sl_log_1", which is where the
updates are collected.

There are some conspicuous "troublesome bits" which Slony-I has evaded
since it is NOT multimaster.

For instance, you'll need some form of conflict resolution system, as
async multimaster allows inserting conflicting combinations of
updates.

You may need some special way of detecting updates to "balance
tables," that is, things where people typically updates of the form:

  update balance_table set balance = balance + 10;

In Slony-I, that becomes read, by the trigger, as, let's say...
  update balance_table set balance = 450;

  (as the old value was 440, and 440+10 = 450)

I have been led to believe that Sybase has a sort of "delta update"
for this sort of thing...

It's worth your while to look into whatever you can find on how other
async multimaster systems function.  Two conspicuous (tho perhaps
unexpected) examples include:

 a) Palm Computing's PalmSync system - which addresses conflicts by
    creating duplicate records and saying "You fix that..."

 b) Lotus Notes, which does a somewhat document-oriented sort of
    async MM replication.

There's _some_ collected wisdom around; if you visit the Slony-I list,
you might be able to attract some commentary.  Just be aware that
we're not planning to make it a multimaster system :-).
--
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/slony.html
TTY Message from The-XGP at MIT-AI:
The-XGP@AI 02/59/69 02:59:69
Your XGP output is startling.