Set Up PostgreSQL for Rails 3.1

Mark Berry October 7, 2011

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:

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!

PostgreSQL pgAdmin

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.



9 Comments

  1. How to setup Rails3 postgres on mac « Chau   |  January 11, 2012 at 6:36 pm

    [...] that helpedhttp://www.funonrails.com/2011/03/getting-started-with-rails-3-postgres.htmlhttp://www.mcbsys.com/techblog/2011/10/set-up-postgresql-for-rails-3-1/http://devcenter.heroku.com/articles/local-postgresql#macintosh Advertisement Tagged as: heroku, [...]

  2. How to setup Rails3 postgres on mac « CSS Tips   |  January 11, 2012 at 6:37 pm

    [...] that helpedhttp://www.funonrails.com/2011/03/getting-started-with-rails-3-postgres.htmlhttp://www.mcbsys.com/techblog/2011/10/set-up-postgresql-for-rails-3-1/http://devcenter.heroku.com/articles/local-postgresql#macintosh Advertisement Tagged as: heroku, [...]

  3. How to setup Rails3 postgres on mac | t1u   |  January 11, 2012 at 6:39 pm

    [...] that helpedhttp://www.funonrails.com/2011/03/getting-started-with-rails-3-postgres.htmlhttp://www.mcbsys.com/techblog/2011/10/set-up-postgresql-for-rails-3-1/http://devcenter.heroku.com/articles/local-postgresql#macintosh Advertisement This entry was [...]

  4. Tomasz   |  March 06, 2012 at 1:31 pm

    Thanks – this helped me a lot
    Just one remark for potential other trouble-shooters
    There must be a space between “password:” and password text in config/database.yml
    I am new for the subject this was not obvious for me…
    Best Regards

  5. Ben M   |  March 31, 2012 at 6:41 am

    This is a great resource. I’m not sure why this wasn’t better documented at Heroku or elsewhere, but your article certainly fills the gap. Thank you!

  6. Postgres Error: Trigger is a System Trigger | MCB Systems   |  September 13, 2012 at 8:31 pm

    [...] on my previous article, Set Up PostgreSQL for Rails 3.1, I managed to get a Postgres superuser set up for use with the Rails test database. Once I did [...]

  7. Edward Garson   |  June 03, 2013 at 5:13 pm

    +1 – clear and helpful, thank you.

  8. Ryan   |  September 12, 2013 at 9:40 pm

    Many thanks Mark, very clear and worked a treat!

  9. Mark Berry   |  September 13, 2013 at 8:41 am

    Ryan et. al., glad it helped. Out of curiosity, what Linux are you using and what version of Postgres is getting installed now? I need to update soon.

Leave a Reply





Notify me of followup comments via e-mail. You can also subscribe without commenting.