Re: replication - Mailing list pgsql-general

I'm not keeping the Access forms because I would like to move them to
something a little more robust. Also, if I'm going to continue support the
application, moving the code from Acces-VBA to VB is bit cleaner.  Even for
people after me.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Michael Fork" <mfork@toledolink.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "PGSQL General" <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 4:19 PM
Subject: Re: [GENERAL] replication


> You can continue to use the same Access app, just link the tables through
> ODBC to the postgres sever (save you from reinventing the wheel)
>
> Michael Fork - CCNA - MCP - A+
> Network Support - Toledo Internet Access - Toledo Ohio
>
> On Thu, 21 Sep 2000, Adam Lang wrote:
>
> > That is not the situation though.  The reason I want to have to
databases is
> > to have one at the site that actual production is going to be worked on,
and
> > then a replicated database at a remote location where the webserver is.
> >
> > As for the interface, they use Access forms, which I'll just write a new
VB
> > app using ODBC to replace.
> >
> > Adam Lang
> > Systems Engineer
> > Rutgers Casualty Insurance Company
> > ----- Original Message -----
> > From: "Len Morgan" <len-morgan@crcom.net>
> > To: "Adam Lang" <aalang@rutgersinsurance.com>
> > Sent: Thursday, September 21, 2000 2:52 PM
> > Subject: Re: [GENERAL] replication
> >
> >
> > > There is another approach that I have used: Convert all of the tables
in
> > the
> > > current Access system to Postgres tables and then use ODBC links in
the
> > > current program instead of internal tables.  This way you only have
one
> > > database, no need to replicate and it's much more "live".  If you give
the
> > > tables in Postgres the same names they have in access, you won't have
to
> > > change any of the forms/queries/reports, etc.
> > >
> > > Just my 2 cent's worth.
> > >
> > > Len Morgan
> > >
> > >
> > > -----Original Message-----
> > > From: Adam Lang <aalang@rutgersinsurance.com>
> > > Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
> > > Date: Thursday, September 21, 2000 1:43 PM
> > > Subject: Re: [GENERAL] replication
> > >
> > >
> > > >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.
> > > >> >
> > > >> >
> > > >
> > > >
> >