[GLLUG] Re: Distributed / Linked with Open Source DB

[Jason Green]

I looked around for a long time at the open source db's out there, and the
biggest problem I had was that none of them (natively or stably) supported
synchronous replication between different sites.  The new version of mySQL
support round-robin master/slave replication that can be circular (site A ->
site B -> site A), but can fail if 2 different sites have updates made to
the same record at the same time.  PostgreSQL has a number of additional
packages that can be added to it to support replication, but none of them
have been declared stable, and I'm not running test software in a production
environment (Windows excluded   :-)

As far as Union Queries across a WAN, I suppose it could be done if you had
your program linking correctly to all 4 sites, but I'd imagine it might be
pretty slow.   Depending on how time-sensitive the data is, you could just
run hourly/nightly imports of all off-site data, and have a procedure that
removes the old stuff and replaces it with the new imports.  Then, your
union query would only have to involve the "on-site" table and an "off-site"
table that are present on all 4 sites...  Unfortunately, it looks like one
of the commercial databases is the only way to do a lot of multi-site stuff
right now without having to write a lot of custom code to patch everything
together.  But, mySQL is making a lot of headway right now, and I heard that
some company is starting to adopt and expand PostgreSQL quite a bit, but I
can't remember who right now.

Also, you might want to look at SAPDB...  I don't know how full-featured it
is (doesn't have synchronous replication), but it's open source as well.  It
claims to be an "enterprise" software package.

Jason Green
greenja6@msu.edu

> From: "Ex Fed" <exfed@hotmail.com>
> To: linux-user@egr.msu.edu
> Date: Fri, 04 Oct 2002 13:36:44 +0000
> Subject: [GLLUG] Distributed / Linked with Open Source DB
>
> Does anybody out there have experience implementing either a distributed
> homogenous database or linked databases with MYSQL or POSTGRES.
>
> The reason I ask is I will have 4 distant regions connected over high
speed
> data lines, that will have seperate databases, if the databases had the
same
> table structure, ie table names and field types and widths the same, would
> it easily be possible to link these together so that they can be queried
as
> one database (union), but the one region can only perform DML on their own
> databases data.  Has anybody out there done this?
>
> I assume this would require a lot of traffic between the servers.
> Is this efficient, and reliable?
>
> I am looking at Oracle, they offer all of the capabilities, but are pretty
> complex, and way more expensive than the open source solutions.
>
>
> Lee D.