Re: Replicating databases - Mailing list pgsql-general

Carlos,
What you are asking for is a multi-master replication scheme.  Slony-I
is a single master system, meaning that for each slony cluster only one
node can make changes to the data.

Without breaking slony's normal rules, I believe that there might be a
way to do it, though it will not be pretty.

Basically you would create a slony cluster for each store, which
replicates store data back to the central system.  You will also have a
master cluster that replicates central data to all stores.

For each store you will create a slony cluster CS (ie for store 1, you
create cluster C1, for store 2 cluster C2, etc).

For the central (master) database you will create a cluster CM that
replicates to all stores.

For each application table, T, you will do the following:
- create a table T_S at each source store S, and on the central database
- add T_S to the replication set for CS
- on the central db create a master table T_M
- on the central db, add triggers on T_S that copy all changes into the
master table T_M (T_M will then contain the full set of data from all
stores)
- add T_M to the replication set for cluster CM
- at each store create a view T that does select * from T_S union select
* from T_M
- create instead of triggers on T that cause updates to be performed
only on the underlying local table T_S
- at the central node create a view T that does select * from T_M, (you
don't need instead of triggers for this as the data can only be updated
at the stores)

So, for N stores you will have created N+1 slony clusters, N+1 distinct
tables for each distributed table.

This is horrible and a lot of maintenance.  It might work though if the
number of stores is quite small.

You should probably ask the question again on slony1-general.  The
experts there may suggest a better solution.  I have seen talk of
disabling the standard slony triggers to allow this sort of thing but
whether that is more or less nasty is questionable.

Good luck

__
Marc

On Wed, 2005-11-02 at 12:18 -0400, pgsql-general-owner@postgresql.org
wrote:
> Date: Wed, 2 Nov 2005 12:06:36 +0000 (GMT)
> From: Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
> To: pgsql-general@postgresql.org
> Subject: Replicating databases
> Message-ID: <20051102120637.58061.qmail@web35507.mail.mud.yahoo.com>
>
> Hello,
>
> Currently our company has a lot of small stores distributed around the
> country and in the actual database configuration we have a central
> database and all the small stores accessing it remotely.
>
> All primary key tables were designed with a column identifying the
> store that it belongs. In other words, the store that can update the
> line, other stores can read it but the system was designed in such a
> way that other stores can not update information that do not belong to
> them.
>
> The performance is not good because the line speed that connects the
> store to the central database sometimes is overloaded. Were thinking
> to replicate the central database to each store. The store would be
> able to read all the information from the local database but should
> only update lines that belong to that store.
>
> When a store needs read information about other stores, it is not
> necessary to be updated, it can be a yesterday snapshot.
>
> During the night all the local store databases will be consolidated in
> only one database and replicated again to the stores. In the morning,
> when the store opens, the local database has an updated and
> consolidated data.
> I would appreciate suggestions about how the best way to implement
> such soluction.
>
> Slony-1? SQL scripts?
>
> Thanks in advance!
>
> Benkendorf