Replicating databases

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. We´re 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