Thread: Is it possible to mirror the db in Postgres?
We use Postgres 7.0.3 to store data for our scientific research. We have two other labs in St. Louis, MO and Tempe, AZ. I'd like to see if there's a way for them to mirror our database. They would be able to update our database when they received new results and we would be able to update theirs. So, in effect, we'd have 3 copies of the same db. Each copy would be able to update the other. Any thoughts on if this is possible? Thanks. -Tony Reina
On Fri, Apr 20, 2001 at 03:33:38PM -0700, G. Anthony Reina wrote: > We use Postgres 7.0.3 to store data for our scientific research. We have > two other labs in St. Louis, MO and Tempe, AZ. I'd like to see if > there's a way for them to mirror our database. They would be able to > update our database when they received new results and we would be able > to update theirs. So, in effect, we'd have 3 copies of the same db. Each > copy would be able to update the other. > > Any thoughts on if this is possible? Does the replication have to be reliable? Are you equipped to reconcile databases that have got out of sync, if not? Will the different labs ever try to update the same existing record, or insert conflicting (unique-key) records? Symmetric replication is easy or impossible, but usually somewhere in between, depending on many details. Usually when it's made to work, it runs on a LAN. Reliable WAN replication is harder. Most of the proprietary database companies will tell you they can do it, but their customers will tell you they can't. Nathan Myers ncm@zembu.com
> Reliable WAN replication is harder. Most of the proprietary database > companies will tell you they can do it, but their customers will tell > you they can't. This comment is great. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Nathan Meyers wrote: > Does the replication have to be reliable? Are you equipped to > reconcile databases that have got out of sync, if not? Will the > different labs ever try to update the same existing record, or > insert conflicting (unique-key) records? > (1) Yes, of course. (2) Willing--yes; equipped--dunno. (3) Yes, probably. Reliable WAN replication is harder. Most of the proprietary database companies will tell you they can do it, but their customers will tell you they can't. Joel Burton suggested the rserv utility. I don't know how well it would work over a wide network. -Tony
On Fri, Apr 20, 2001 at 04:53:43PM -0700, G. Anthony Reina wrote: > Nathan Myers wrote: > > > Does the replication have to be reliable? Are you equipped to > > reconcile databases that have got out of sync, when it's not? > > Will the different labs ever try to update the same existing > > record, or insert conflicting (unique-key) records? > > (1) Yes, of course. (2) Willing--yes; equipped--dunno. (3) Yes, > probably. Hmm, good luck. Replication, by itself, is not hard, but it's only a tiny part of the job. Most of the job is in handling failures and conflicts correctly, for some (usually enormous) definition of "correctly". > > Reliable WAN replication is harder. Most of the proprietary database > > companies will tell you they can do it, but their customers will tell > > you they can't. > > Joel Burton suggested the rserv utility. I don't know how well it would > work over a wide network. The point about WANs is that things which work nicely in the lab, on a LAN, behave very differently when the communication medium is, like the Internet, only fitfully reliable. You will tend to have events occurring in unexpected order, and communications lost, and queues topping over, and conflicting entries in different instances which you must somehow reconcile after the fact. Reconciliation by shipping the whole database across the WAN is often impractical, particularly when you're trying to use it at the same time. WAN replication is an important part of Zembu's business, and it's hard. I would expect the rserv utility (about which I admit I know little) not to have been designed for the job. Nathan Myers ncm@zembu.com
You probably already thought of this - but - why not just set up a centralized server and have each office interact to the db via a web interface. Let your application enforce security (apacheSSL, use db for user auth) and to prevent two users from editing the same record simultaneously. -r At 06:19 PM 4/20/01 -0700, Nathan Myers wrote: >On Fri, Apr 20, 2001 at 04:53:43PM -0700, G. Anthony Reina wrote: > > Nathan Myers wrote: > > > > > Does the replication have to be reliable? Are you equipped to > > > reconcile databases that have got out of sync, when it's not? > > > Will the different labs ever try to update the same existing > > > record, or insert conflicting (unique-key) records? > > > > (1) Yes, of course. (2) Willing--yes; equipped--dunno. (3) Yes, > > probably. > >Hmm, good luck. Replication, by itself, is not hard, but it's only >a tiny part of the job. Most of the job is in handling failures >and conflicts correctly, for some (usually enormous) definition of >"correctly". > > > > Reliable WAN replication is harder. Most of the proprietary database > > > companies will tell you they can do it, but their customers will tell > > > you they can't. > > > > Joel Burton suggested the rserv utility. I don't know how well it would > > work over a wide network. > >The point about WANs is that things which work nicely in the lab, on a >LAN, behave very differently when the communication medium is, like the >Internet, only fitfully reliable. You will tend to have events occurring >in unexpected order, and communications lost, and queues topping over, >and conflicting entries in different instances which you must somehow >reconcile after the fact. Reconciliation by shipping the whole database >across the WAN is often impractical, particularly when you're trying to >use it at the same time. > >WAN replication is an important part of Zembu's business, and it's hard. >I would expect the rserv utility (about which I admit I know little) not >to have been designed for the job. > >Nathan Myers >ncm@zembu.com > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.250 / Virus Database: 123 - Release Date: 4/18/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.250 / Virus Database: 123 - Release Date: 4/18/01
> Joel Burton suggested the rserv utility. I don't know how well it would
> work over a wide network.
It should work well over a WAN for what it can do. However, that is
async one-way replication, which was not your initial requirement.
Of course, requirements sometimes adjust in the face of reality ;)
- Thomas
Thomas Lockhart wrote: > > > Joel Burton suggested the rserv utility. I don't know how well it would > > work over a wide network. > > It should work well over a WAN for what it can do. However, that is > async one-way replication, which was not your initial requirement. > > Of course, requirements sometimes adjust in the face of reality ;) If it is a low-update mostly-analyze DB then updating on central db and then doing async one-way replication from there may be a good strategy. ------------- Hannu