Jonathan's Pancheria

dotcom Thousandaire

To list the schemas in a postgresql database:

select nspname from pg_namespace;

Published on 01/11/2005 at 05:20AM under . Tags , , ,

I wanted a nice convenient way to have multiple Rails apps share my single postgresql database but keep all the apps’ tables/procs/views/other artifacts isolated from each other. I came up with the following solution, which I can’t claim is necessarily original, but I did not find documentation that laid out the solution all in one place. I currently have the typo blog you are reading this article on up using this solution.

The trick is to use multiple Postgresql schemas (part of Postgresql since 7.4) to separate each Rails app’s tables etc. into their own schema space Then, every time you want to load up a new Rails app, or really any postgresql-based application, you just create a new schema. You could in theory even put your development, testing, and production databases for a single Rails app into separate schemas in one database.

Documentation on Postgresql schemas, and more information about what the recipe below is doing with schema commands is here. Rails has had support for leveraging Postgresql schemas since version 0.11.0 as mentioned here.
h3. Steps to create a schema in a postgresql database and configure a Rails app to use the schema

1. Create the new schema in your database:
bq. CREATE SCHEMA myrailsapp\g

Replace myrailsapp with whatever name you want for your schema in the SQL above and everywhere else following.

2. If you are installing an existing Rails app that has a SQL script to generate the tables etc., then insert the following as the first line of the schema file, otherwise skip to step 4:
bq. SET search_path TO myrailsapp;

Postgresql uses the first schema in the schema path as the default location to look for tables/procs/triggers/etc if the name is unspecified (see this discussion discussion in the Postgresql docs for more details).

3. Execute the SQL script with the change from step 2. If you go into psql and issue the following commands, you should see the objects that your script created:

SET search_path TO myrailsapp\g


4. Edit your database.yml file and add the following to the appropriate section(s):
bq. schema_search_path: myrailsapp

So your database.yml section for production would look like this if you wanted to use the myrailsapp schema in the mydbname database:

  adapter: postgresql
  database: mydbname
  #whatever other postgresql config options you require
  schema_search_path: myrailsapp

That schema_search_path: statement in your database.yml file tells Rails to set the schema search path to look only in the myrailsapp schema for unqualified database object names. That means that without changing anything about how you write your code, your Rails app now has all its artifacts in a schema inside your database.



Published on 01/11/2005 at 04:27AM under . Tags , , ,

Powered by Typo – Thème Frédéric de Villamil | Photo L. Lemos