Re: replication - Mailing list pgsql-general

Well, this is the situation.  A client has a database application currently
running on Access (which multiple people share over a network).  They want
to make data accessible via the internet.  So, what I was thinking was keep
the production database at their location, convert it to postgresql, and
make a replacement application for them to do their work.  Then, host their
webserver remotely and have another database there. The main location has a
DSL connection.  I figured then that once a day, three times a day
(whatever) the main database synchs up the remote one. (The remote database
will only be used by PHP to extract data, no modifications).

The point of this is that no matter what the main location will always be
able to get their work done while still being able to host their website
offsite.

If there was only a single database at either end, the whole setup is
susceptible to a telecomm link going down and cutting something off.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Rob Hutton" <rhutton@istmanagement.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>; "Stephan Szabo"
<sszabo@megazone23.bigpanda.com>; "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 3:19 PM
Subject: RE: [GENERAL] replication


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