Thread: replication
Are there any type of replication features in postgresql 7.0? I would like it where two databases have the same structure, but say at midnight every night Database 1 synchs up database 2. Granted, I could always write code to do that, but it wouldn't be very sophisticated. (If I coded it, would do something like find rows in table 1 which aren't in the second database, append them database two, same with the second table, etc.) Adam Lang Systems Engineer Rutgers Casualty Insurance Company
On Thu, 21 Sep 2000, Adam Lang wrote:
> Are there any type of replication features in postgresql 7.0?
Not exist some standard solution for PG for DB replication ...
Maybe in a far future (via some WAL logs?).
Karel
Adam Lang wrote: > > Are there any type of replication features in postgresql 7.0? > > I would like it where two databases have the same structure, but say at > midnight every night Database 1 synchs up database 2. > > Granted, I could always write code to do that, but it wouldn't be very > sophisticated. (If I coded it, would do something like find rows in table > 1 which aren't in the second database, append them database two, same with > the second table, etc.) And you would also have to check which records have been modified and replicate them. You are not the first person to ask for this feature and it is on the TODO list: http://www.postgresql.org/docs/pgsql/doc/TODO.detail/replication but it is categorized under "exotic features", so I don't know when we'll see it implemented :( But I think www.psql.com are working on replication right now. Poul L. Christiansen > > Adam Lang > Systems Engineer > Rutgers Casualty Insurance Company
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 :). btw, remotesite could be setup in what oracle referred to as "tnsnames.ora". It was a file that had a list of hosts, ports and the database name so that you wouldn't have to know all that info to connect to an oracle database, just what you named it, your username and your password. Thanks, -------------------------------------------------------- | Daryl Chance | I have made this letter longer then | | Valuedata, LLC | usual because I lacked the time to | | Memphis, TN | make it shorter. -- Blaise Pascal | -------------------------------------------------------- ----- Original Message ----- From: "Adam Lang" <aalang@rutgersinsurance.com> To: "PGSQL General" <pgsql-general@postgresql.org> Sent: Thursday, September 21, 2000 7:52 AM Subject: [GENERAL] replication > Are there any type of replication features in postgresql 7.0? > > I would like it where two databases have the same structure, but say at > midnight every night Database 1 synchs up database 2. > > Granted, I could always write code to do that, but it wouldn't be very > sophisticated. (If I coded it, would do something like find rows in table > 1 which aren't in the second database, append them database two, same with > the second table, etc.) > > Adam Lang > Systems Engineer > Rutgers Casualty Insurance Company > >
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.
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.
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. > >
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. > > > >
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. > >> > > >> > > > > >
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. > > >> > > > >> > > > > > > > >
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. > > > >> > > > > >> > > > > > > > > > > >
Adam Lang wrote: > > Are there any type of replication features in postgresql 7.0? > > I would like it where two databases have the same structure, but say at > midnight every night Database 1 synchs up database 2. > > Granted, I could always write code to do that, but it wouldn't be very > sophisticated. (If I coded it, would do something like find rows in table > 1 which aren't in the second database, append them database two, same with > the second table, etc.) I would suggest to consider the xmin values stored with each row in database. This number does contain the transaction-number of the last change to this tuple. An replication should copy all tuples having larger xmin than copied by the last replication. If each table has an primary key, replication can decide wether the tuple is updated or inserted. For deletes you should consider to use triggers copying the keys of deleted tuples to an "delete-log-table". If an delete-transaction is aborted, the entries to this table should be cancelled automatically by the transaction control. I'm not sure wether you can use the oid as an key for this purpose. Is oid updatable to keep it in sync on both databases? Elmar
On Fri, Sep 22, 2000 at 09:58:24AM +0200, Elmar Haneke wrote: > Adam Lang wrote: > > Are there any type of replication features in postgresql 7.0? I've been thinking that one way to emulate replication would be to run a pgsql "shim" that would redirect queries to two back-ends simultaneously, creating transactions for update commands, and aborting when necessary. The shim would be a long-running gateway process, that could check for data consistency when it started up. This is all off the top of my head, so is this at all a reasonable idea? I am running into a similar situation, where I want to set up two live redundant sites, where nightly updates aren't an option. -- Bruce Guenter <bruceg@em.ca> http://em.ca/~bruceg/
Attachment
Bruce Guenter <bruceg@em.ca> writes:
> I've been thinking that one way to emulate replication would be to run a
> pgsql "shim" that would redirect queries to two back-ends
> simultaneously, creating transactions for update commands, and aborting
> when necessary. The shim would be a long-running gateway process, that
> could check for data consistency when it started up. This is all off
> the top of my head, so is this at all a reasonable idea?
Awhile back, someone reported that they were successfully using exactly
this idea. Check the PG list archives (sorry, I forget which list).
I'm not convinced that this scales real well to multiple concurrent
clients ... at the very least, you'd need a single gateway that
serializes all the requests. You might still have problems with the
two databases not executing requests in exactly the same order. Also
it'd be really dangerous to use OIDs as a way of identifying rows in
application queries. But with a cooperative application it could
probably be made to work.
regards, tom lane
On Fri, Sep 22, 2000 at 12:03:13PM -0400, Tom Lane wrote: > Bruce Guenter <bruceg@em.ca> writes: > > I've been thinking that one way to emulate replication would be to run a > > pgsql "shim" that would redirect queries to two back-ends > > simultaneously, creating transactions for update commands, and aborting > > when necessary. The shim would be a long-running gateway process, that > > could check for data consistency when it started up. This is all off > > the top of my head, so is this at all a reasonable idea? > > Awhile back, someone reported that they were successfully using exactly > this idea. Check the PG list archives (sorry, I forget which list). Cool! I'll take a look. > I'm not convinced that this scales real well to multiple concurrent > clients ... at the very least, you'd need a single gateway that > serializes all the requests. Or multiple gateways, one "near" each database server, that try to keep some synchronization with each other. > You might still have problems with the > two databases not executing requests in exactly the same order. > Also > it'd be really dangerous to use OIDs as a way of identifying rows in > application queries. Oh, definitely agreed. OIDs go out the window, unless both servers have have identical queries delivered to them at all times. This is rather difficult to guarantee. -- Bruce Guenter <bruceg@em.ca> http://em.ca/~bruceg/