From: Chris Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: multiple servers updating from/to one
Date: Wed, 30 Mar 2005 11:18:31 -0500
Message-ID: <60k6nprtt4.fsf@dba2.int.libertyrms.com> (raw)
References: <9250651c0503300651788c62c6@mail.gmail.com>
aramefarpado@gmail.com (Marco Carvalho) writes:
> Hi, all
>
> I need to do this scenario:
>
> Web Application Web Application Web Application
> | |
> |
> pgsql server 1 pgsql server 2 pgsql server 3
> | |
> |
> | |
> |
> | adsl | adsl
> | adsl
> | |
> |
> | |
> |
> -------------------------------------------------------
> |
> pgsql server ("master")
>
> I need various servers in different locations running exactly some
> things, one Web Application and one pgsql server, all synchronizing
> with "master" server through unstable adsl connections.
> So, If I do one insert in server 1, this data goes to "master" and is
> available to other servers, and vice-versa, and if adsl connection
> broken, Web Application still working with local server and when
> connection is restored both databases are synchronized.
> Is this possible?
> If it's true, what tools I need?
The only system I am aware of that can support this sort of usage
scenario may be PeerDirect's replication system.
The replication systems "traditionally available" for use with
PostgreSQL do not directly support your desire for a multimaster
approach; systems like Slony-I, Mammoth Replicator, eRServer, and such
require that there be just one master system and that replicas be
read-only.
The Slony-II system being worked on is a multimaster system, but
requires synchronization of "live" servers so that it specifically
does NOT support the "operate while disconnected, and merge in changes
upon return" that you are suggesting.
There is _possibly_ a way to get Slony-I to do what you want, if your
application is suitably malleable...
In Slony-I, it is necessary for one and only one host to be the
"master" or "origin" for each table. But it supports the notion of
having replication sets (e.g. - sets of tables that are being
replicated) that have differing origins.
Thus, organizing those sets into their own schemas, you might set
up...
create schema master;
create table master.gl ( stuff );
create table master.ar ( stuff );
create table master.ap ( stuff );
create table master.users ( stuff );
create table master.inventory ( stuff );
create schema app1;
create table app1.gl ( stuff );
create table app1.ar ( stuff );
create table app1.ap ( stuff );
create table app1.users ( stuff );
create table app1.inventory ( stuff );
create schema app2;
create table app2.gl ( stuff );
create table app2.ar ( stuff );
create table app2.ap ( stuff );
create table app2.users ( stuff );
create table app2.inventory ( stuff );
create schema app3;
create table app3.gl ( stuff );
create table app3.ar ( stuff );
create table app3.ap ( stuff );
create table app3.users ( stuff );
create table app3.inventory ( stuff );
You'd set up the respective servers as the "origins" for the four
replication sets that naturally fall out of this.
Some process on the "master" server would be responsible for
synchronizing the sets, perhaps by adding some additional table that
feeds back what the app servers should update...
In effect:
"I pulled orders 123, 577, and 899 from app1, and put them into the
master, so feel free to delete them..."
This would be encoded in a new "master" table, perhaps thus:
insert into master.downstream_changes (server, table, id) values ('app1', 'orders', '123');
insert into master.downstream_changes (server, table, id) values ('app1', 'orders', '577');
insert into master.downstream_changes (server, table, id) values ('app1', 'orders', '899');
Thus, the app1, app2, and app3 schemas would just contain transient
data that, once processed on the master, would get deleted.
Slony-I isn't particularly happy about hosts that stay disconnected a
lot of the time, so I'd hate to set this up...
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/slony.html
"The present need for security products far exceeds the number of
individuals capable of designing secure systems. Consequently,
industry has resorted to employing folks and purchasing "solutions"
from vendors that shouldn't be let near a project involving securing a
system." -- Lucky Green
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: multiple servers updating from/to one
In-Reply-To: <60k6nprtt4.fsf@dba2.int.libertyrms.com>
References: <9250651c0503300651788c62c6@mail.gmail.com> <60k6nprtt4.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox