Slony with Multiple Databases

While implementing Slony setup for a customer we came up with a scenario where they had four databases running on one PostgreSQL instance on one machine and we had to replicate all the tables within all the databases using Slony. It was pretty simple to implement but the problem was that with all databases on one machine, which means we were going to run a Slon process for each database and that ends up having four Slon processes running on the same box.

After giving it some thought we came up with the following:

– Instead of four databases have one database with four schemas.

– Now create Slony set for each schema with all the schema specific tables in there.

– And then ALTER database users to change there search_path in a way that on login they point to there specific schema so that they dont have to change anything on the application side.

Following the above mentioned approach, we were able to configure Slony with just one Slon process for all the tables and the customer was happy as well 🙂

Shoaib Mir
shoaibmir[@]gmail.com