RE: replication - Mailing list pgsql-general

  I asked a similar question earlier in the week and got no response.  If
there is transaction logging, then it is fairly easy to implement syncing,
even real time, the only question is to play back the log every x amount of
time on the remote db.  The tricky part is two fold.

  First, you have to maintain a table of all of the remote DBs that are
syncing and the last two send and receive timestamps, and whether the last
was successful.  If not, then at the next sync time, you have to replay the
logs again back to the previous time that was successful.  Then you have to
have a process that cleans up all of the transactions that are previous to
the most recent successful sync.

  The second part is collision resolution.  That is, what happens when an
edit is made to a DB at both ends in between the sync period.  Most of the
time the later timestamp wins, but what happens if that effects business
rules, range limitations, etc.  For instance, if b must be between a and c,
and b and c are lowered on one end, and on the other b is raised above the
new setting for c, how is this resolved.  You end up writing a rules engine
for this stuff...

Rob

> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Adam Lang
> Sent: Thursday, September 21, 2000 12:42 PM
> To: Stephan Szabo; Daryl Chance
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] replication
>
>
> Well, if I end up needing to do something with it, I'll get back
> to the list
> on ideas/solutions I encountered and see about potential pitfalls.
>
> Adam Lang
> Systems Engineer
> Rutgers Casualty Insurance Company
> ----- Original Message -----
> From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
> To: "Daryl Chance" <dchance@valuedata.net>
> Cc: <pgsql-general@postgresql.org>
> Sent: Thursday, September 21, 2000 12:59 PM
> Subject: Re: [GENERAL] replication
>
>
> > On Thu, 21 Sep 2000, Daryl Chance wrote:
> >
> > > Could this possibly be done using triggers?  I'm new to
> > > postgres, but I know on a project I was doing using oracle
> > > the dba could setup triggers to run on the OnInsert() (not
> > > sure what it's actually called in oracle...).  Do maybe
> > > on the "OnInsert" of table foo you could do:
> > >
> > > Insert into foo@remotesite1 ....
> > >
> > > Is this possible in postgres?  I'm looking at using postgres
> > > for the next version of my SW and if replication isn't in,
> > > I'm gonna need something like this :).
> >
> > You could probably write a C trigger that would propogate
> > changes, except that there are still problems.  What do you
> > do when you roll back the transaction?  Currently, there
> > aren't triggers for transaction start and end.  Triggers that
> > do stuff outside the database right now are a bad idea unless
> > you have some other mechanism to determine whether something
> > was really supposed to be done.  It could be done, but isn't
> > trivial.
>
>