Is it possible to mirror the db in Postgres?

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, Pennsylvania 19026

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

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