Jonathan's Pancheria

dotcom Thousandaire

I ran across this today while trying to use the chef community database cookbook.  If you want to use it along with a postgresql database to e.g. create databases and create and assign roles to users, then you may need to do the following, at least with the chef 11.04 client running against a 10.18.2 server, which is my setup:




  • You will need to have your role have recipe[postgresql::ruby] in its run list before the database cookbook in the role file that uses database, or otherwise set up your chef environment have it run earlier in the order


  • In your recipe where you do your postgresql setup work, include the command chef_gem 'pg' before you use any of the database cookbook commands

Published on 18/03/2013 at 10:15PM under . Tags , , , , ,

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

\d

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:


production:
  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.

Enjoy!

Jonathan

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

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