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
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
Import Notes
Resolved by subject fallback
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
Import Notes
Resolved by subject fallback
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
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
MITREYes, 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:
Show quoted text
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
MITREYes, 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?
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
MITREYes, 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