Set Up PostgreSQL for Rails 3.1

Heroku requires PostgreSQL for production and recommends it for development. Here is the process I worked out for installing PostgreSQL 8.4 and setting up a Rails 3.1 project. I’m working in Ubuntu 10.04.

Install PostgreSQL

1. Install PostgreSQL locally. See Local PostgreSQL Installation on the Heroku site.

sudo apt-get install postgresql

As of this writing, this installs PostgreSQL 8.4, which matches the version on Heroku’s shared servers.

2. We also need libpq-dev. See this Stackoverflow article.

sudo apt-get install libpq-dev

A different article says to manually install the pg gem but I did not do that. (Well, I don’t think I did; there was a lot of trial and error in working this out.) However the gem is in my Gemfile.

# sudo gem install pg

Set Up a New Rails Project to Use PostgreSQL

Generate a new Rails project with PostgreSQL as the database. I also add –T so it won’t make a Test folder (I’ll add rspec later).

Note All instructions refer to myapp. Substitute your own application name.

cd ~/rails_projects
rails new myapp -T --database=postgresql
cd myapp

This will create a config/database.yml file that uses PostgreSQL. The Gemfile will also load the ‘pg’ gem instead of the ‘sqlite3’ gem.

Source Control

You may want to exclude database.yml from source control since it will contain passwords. If you are using git, add config/database.yml to .gitignore. If you’ve already committed with a database.yml file, use this command to remove it from source control but not from the project:

git rm --cached config/database.yml

Set up PostgreSQL

This section is cobbled together from advice in the following articles:

• Getting started with rails 3 & postgres database
• Setup Rails with Postgresql
• Creating Rails users in Postgres on Ubuntu

By default, PostgreSQL creates a postgres superuser with no password, and links that user to a new postgres system user. Our goal is to create and use a project-specific database user that does not have a corresponding system user account.

1. Change to default “postgres” admin user (will prompt for your Ubuntu password).

sudo su postgres

2. Use the psql interactive terminal to create a role that can create databases and log in (basically creates a user). Note the semicolon at the end of PostgreSQL commands! I used the password generator lat this site to create a nice long password. If you’re “stuck” in the PostgreSQL shell, try \q.

psql template1 #starts Postgres interactive shell
# make your own password for the next line
create role myapp with createdb login password 'sPUKuBr6wRa36A7';
select * from pg_user;    # Verify user created ("\q" to exit!)
select * from pg_shadow;  # sysid and password hash listed here
\l # list databases
\q # exit Postgres shell
exit # exit "Postgres" admin user

3. By default, PostgreSQL will try to use to your Ubuntu identity for logging in to the databases, but that identity has no database rights. That’s the default “ident” authentication method.

To tell PostgreSQL to use passwords instead, we need to update the pg_hba.conf file (note “sudo” to allow opening protected file).

sudo gedit /etc/postgresql/8.4/main/pg_hba.conf

Add these four lines to pg_hba.conf, before the local all all ident line:

local postgres            myapp       md5
local "myapp_development" myapp       md5
local "myapp_test"        myapp       md5
local "myapp_production"  myapp       md5

This tells PostgreSQL to accept MD5 passwords for the new “myapp” user when connecting to the project databases. Note that we must also allow access to the default “postgres” database so rake db:create:all can later create our project databases. Also, quote database names containing a special character (underscore). See this Stackoverflow article for more info.

4. Restart PostgreSQL.

sudo /etc/init.d/postgresql-8.4 restart

5. Test logging in to the default “postgres” database as the new user.

psql postgres -U myapp

This should prompt for a password. If you immediately get the message “Ident authentication failed for user “myapp””, the pg_hba.conf file is not right. After supplying the password, use \q to exit the Postgres shell.

6. Edit the config/database.yml file and add the password from step 2 to all three databases. Save the file. Here is an excerpt:

test:
  adapter: postgresql
  encoding: unicode
  database: myapp_test
  pool: 5
  username: myapp
  password: sPUKuBr6wRa36A7


7. Create all databases based on database.yml.

rake db:create:all

Optional:  Graphical UI for PostgreSQL

This list of PostgreSQL GUIs pointed to this list. I decided to try pgAdmin. There are advanced instructions for installing it under Ubuntu here, but all I did was run this command:

sudo apt-get install pgadmin3

After that, I started the program from Applications > Programing > pgAdmin III and clicked on the “plug” icon to create a connection. From the documentation, I learned that for a local connection, you can leave Host blank; just give it a name and the Username and Password defined above, and you’re connected!

Note that this installs pgAdmin 1.10.2. From what I can tell from the pgAdmin Change Log, that includes most of the support for PostgreSQL 8.4. A later version of pgAdmin may be required for PostgreSQL 9.1. Here’s a complicated article on installing under Ubuntu, or maybe just use the latest installer here.

For data modeling, SQL Power Architect looks promising, but I haven’t tried it yet.