Thread: do I need replication or something else?
We have a billing system and we want the ability to send users out into the field with an unconnected (no WAN, VPN etc) laptop containing our pg db and software. Upon their return we need to synchronize changes to the main db. We would like the ability to be able to have this accomplished at the db level rather than doing this in our app. What is the best tool to accomplish this with? Caleb
Attachment
Sounds like you are begging for trouble. Suppose that a customer calls in to the main office, and you update some customer data. The field salesman also updates data for this customer. If you update the main office database with the field data, you will lose information. If you update the field database data with main office database data, you will lose information. Because we have two different changed records, it will be very difficult to reconcile this data without human intervention. In short, a disconnected system where data on both ends can change is begging for trouble. Now, if you never update the database in the main office except with data from the field salesmen, it could be made to work. But I doubt that this is what you are after. If it were me, and someone proposed a model where two-way replication was needed, I would tell them to rethink their model. It's broken. IMO-YMMV. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Caleb Simonyi-Gindele Sent: Tuesday, March 29, 2005 10:58 AM To: pgsql-general@postgresql.org Subject: [GENERAL] do I need replication or something else? We have a billing system and we want the ability to send users out into the field with an unconnected (no WAN, VPN etc) laptop containing our pg db and software. Upon their return we need to synchronize changes to the main db. We would like the ability to be able to have this accomplished at the db level rather than doing this in our app. What is the best tool to accomplish this with? Caleb
Dann Corbit wrote: > If it were me, and someone proposed a model where two-way replication > was needed, I would tell them to rethink their model. It's broken. I'm relatively new to PostgreSQL so won't comment about that. But some DBMSs have this feature built in because it is a fairly common usage model (think traveling salespeople.) I've personally used Watcom (now Sybase) SQL Anywhere which has this and it works quite well. I would respectfully disagree that the requirement for two-way replication indicates a broken design. -- Guy Rouillier
>> If it were me, and someone proposed a model where two-way replication >> was needed, I would tell them to rethink their model. It's broken. > > I would respectfully disagree that the requirement for two-way > replication > indicates a broken design. I agree with your disagreement. This design is present in lots of non-RDB systems - CVS, IMAP, PDA syncing, etc. It's clearly more complicated, but can be made to work, and has been many times. I don't see anything about databases in general, or Postgres specifically, that indicates it's a bad idea. - John D. Burger MITRE
John Burger wrote: >>> If it were me, and someone proposed a model where two-way replication >>> was needed, I would tell them to rethink their model. It's broken. >> >> >> I would respectfully disagree that the requirement for two-way >> replication >> indicates a broken design. > > > I agree with your disagreement. This design is present in lots of > non-RDB systems - CVS, IMAP, PDA syncing, etc. It's clearly more > complicated, but can be made to work, and has been many times. I > don't see anything about databases in general, or Postgres > specifically, that indicates it's a bad idea. > > - John D. Burger > MITRE > Yes, we use it successfully with the SQL Server edition of our product. Does anyone know if this is available with Postgre? Caleb
Attachment
On Tue, 2005-03-29 at 16:06, Caleb Simonyi-Gindele wrote: > John Burger wrote: > > >>> If it were me, and someone proposed a model where two-way replication > >>> was needed, I would tell them to rethink their model. It's broken. > >> > >> > >> I would respectfully disagree that the requirement for two-way > >> replication > >> indicates a broken design. > > > > > > I agree with your disagreement. This design is present in lots of > > non-RDB systems - CVS, IMAP, PDA syncing, etc. It's clearly more > > complicated, but can be made to work, and has been many times. I > > don't see anything about databases in general, or Postgres > > specifically, that indicates it's a bad idea. > > > > - John D. Burger > > MITRE > > > Yes, we use it successfully with the SQL Server edition of our product. > Does anyone know if this is available with Postgre? It's important to understand that what you're asking for is MORE than simple replication, it is replication with ((semi)automatic) conflict resolution. If you use a simple replication system to try and do this, you are likely to wind up with inconsistent data. Just because SQL Server does it doesn't mean it does it right. And the general philosophy of the PostgreSQL team seems to be do it right or don't bother. So, what are the chances that you'll have records on your sales folks machines that have also been updated back at the home office? What rules should be applied when conflicts arise? These are the kinds of questions you need to answer before jumping feet first into the fire and getting burnt.
On Tue, 29 Mar 2005 16:00:37 -0500, John Burger <john@mitre.org> wrote: > >> If it were me, and someone proposed a model where two-way replication > >> was needed, I would tell them to rethink their model. It's broken. > > > > I would respectfully disagree that the requirement for two-way > > replication > > indicates a broken design. > > I agree with your disagreement. This design is present in lots of > non-RDB systems - CVS, IMAP, PDA syncing, etc. It's clearly more > complicated, but can be made to work, and has been many times. I don't > see anything about databases in general, or Postgres specifically, that > indicates it's a bad idea. > I would suggest whenever changes on the main db caused by sync or immediate update by user, it better to archive the changes into separate table. Archiving is quite simple with creating rule on update or delete table to insert old record to separate table. It will be useful for further reconciliation
If you are using Delphi you can use the tclientdataset which has a Briefcase type system built in. Or you can use one of the many middleware systems that are available for Delphi, all of which will do what you want. www.remobjects.com www.astatech.com etc etc Most of them work by creating a XML local dataset then applying that XML dataset when the client logs back in, it handles the conflict resolution etc. Delphi really is one of THE best ways to develop database apps. Tony Scott Marlowe wrote: >On Tue, 2005-03-29 at 16:06, Caleb Simonyi-Gindele wrote: > > >>John Burger wrote: >> >> >> >>>>>If it were me, and someone proposed a model where two-way replication >>>>>was needed, I would tell them to rethink their model. It's broken. >>>>> >>>>> >>>>I would respectfully disagree that the requirement for two-way >>>>replication >>>>indicates a broken design. >>>> >>>> >>>I agree with your disagreement. This design is present in lots of >>>non-RDB systems - CVS, IMAP, PDA syncing, etc. It's clearly more >>>complicated, but can be made to work, and has been many times. I >>>don't see anything about databases in general, or Postgres >>>specifically, that indicates it's a bad idea. >>> >>>- John D. Burger >>> MITRE >>> >>> >>> >>Yes, we use it successfully with the SQL Server edition of our product. >>Does anyone know if this is available with Postgre? >> >> > >It's important to understand that what you're asking for is MORE than >simple replication, it is replication with ((semi)automatic) conflict >resolution. If you use a simple replication system to try and do this, >you are likely to wind up with inconsistent data. > >Just because SQL Server does it doesn't mean it does it right. And the >general philosophy of the PostgreSQL team seems to be do it right or >don't bother. > >So, what are the chances that you'll have records on your sales folks >machines that have also been updated back at the home office? What >rules should be applied when conflicts arise? These are the kinds of >questions you need to answer before jumping feet first into the fire and >getting burnt. > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > > >
Centuries ago, Nostradamus foresaw when caleb@vetstar.com (Caleb Simonyi-Gindele) would write: > John Burger wrote: >>>> If it were me, and someone proposed a model where two-way >>>> replication was needed, I would tell them to rethink their model. >>>> It's broken. >>> >>> I would respectfully disagree that the requirement for two-way >>> replication indicates a broken design. >> >> >> I agree with your disagreement. This design is present in lots of >> non-RDB systems - CVS, IMAP, PDA syncing, etc. It's clearly more >> complicated, but can be made to work, and has been many times. I >> don't see anything about databases in general, or Postgres >> specifically, that indicates it's a bad idea. >> >> - John D. Burger >> MITRE >> > Yes, we use it successfully with the SQL Server edition of our > product. Does anyone know if this is available with Postgre? There's no such thing as "Postgre," so there's a paucity of features available for that... If you're thinking of PostgreSQL, the only system I am aware of that offers a similar form of "highly asynchronous multi master with conflict avoidance/resolution" is PeerDirect's replication system. -- let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];; http://linuxdatabases.info/info/slony.html "Python's minimalism is attractive to people who like minimalism. It is decidedly unattractive to people who see Python's minimalism as an exercise in masochism." -- Peter Hickman, comp.lang.ruby
On Tue, 2005-03-29 at 12:58 -0600, Caleb Simonyi-Gindele wrote: > We have a billing system and we want the ability to send users out into > the field with an unconnected (no WAN, VPN etc) laptop containing our pg > db and software. Upon their return we need to synchronize changes to the > main db. We would like the ability to be able to have this accomplished > at the db level rather than doing this in our app. > What do you mean by "synchronize". Sometimes that's an easy problem, sometimes that's a hard problem, and sometimes that's an impossible problem. If it's something simple, like just UNIONing the data, it's very possible. You can use Slony-I (<http://www.slony.info>) to do that. Just make two tables, and have the laptop be the master for table1 and the slave for table2, and have the main db server be master for table2 and slave for table1. Then just have a view on each db that's the union of those two tables. Regards, Jeff Davis
> I agree with your disagreement. This design is present in lots of
> non-RDB systems - CVS, IMAP, PDA syncing, etc. It's clearly more
> complicated, but can be made to work, and has been many times. I don't
> see anything about databases in general, or Postgres specifically, that
> indicates it's a bad idea.
>
Depends on what you mean by "work". A database can be made to do lots of
kinds of replication, but there is no perfect solution. The problem
needs to be more clearly defined. In this case, what does the original
poster mean by "synchronize"? It can probably be made to work to his
satisfaction, but not if the synchronization problem is impossible.
If it's simple synchronization, like UNION, you can just use slony-I. If
it's harder, he should examine what each DB has to offer and then
whether that DB actually delivers what it promises, and whether that's
what he actually needs.
Regards,
Jeff Davis
On Tue, Mar 29, 2005 at 04:06:57PM -0600, Caleb Simonyi-Gindele wrote:
> Yes, we use it successfully with the SQL Server edition of our product.
> Does anyone know if this is available with Postgre?
> Caleb
Out of the box, the answer is, "No." It is not an insurmountable
problem, however, and I can think of a nifty way to attempt this with
the currently-beta Slony-I software.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell