Received: from bush.rutgersinsurance.com (IDENT:root@rutgersinsurance.com [38.138.71.194]) by hub.org (8.10.1/8.10.1) with ESMTP id e8LKFGs65432 for ; Thu, 21 Sep 2000 16:15:16 -0400 (EDT) Received: from Adam ([10.10.10.51]) by bush.rutgersinsurance.com (8.9.3/8.9.3) with SMTP id QAA02120 for ; Thu, 21 Sep 2000 16:20:53 -0400 Message-ID: <001101c02408$e8614140$330a0a0a@Adam> From: "Adam Lang" Cc: "PGSQL General" References: <00b101c023fd$1c5367a0$0908a8c0@H233.bstx.cc> Subject: Re: replication Date: Thu, 21 Sep 2000 16:17:04 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200009/625 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" To: "Adam Lang" 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 > Cc: 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" > >To: "Adam Lang" ; "Stephan Szabo" > >; "Daryl Chance" > >Cc: > >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" > >> > To: "Daryl Chance" > >> > Cc: > >> > 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. > >> > > >> > > > > >