Home > PostgreSQL > Slony with Multiple Databases

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

Advertisements
Categories: PostgreSQL Tags: ,
  1. Bilal
    December 19, 2009 at 7:07 am

    Good stuff

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: