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@xxxxxxxxxxxxxx wrote: > Date: Wed, 2 Nov 2005 12:06:36 +0000 (GMT) > From: Carlos Benkendorf <carlosbenkendorf@xxxxxxxxxxxx> > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Replicating databases > Message-ID: <20051102120637.58061.qmail@xxxxxxxxxxxxxxxxxxxxxxxxxxx> > > 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
Attachment:
signature.asc
Description: This is a digitally signed message part
