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.
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.
rails new myapp -T --database=postgresql
This will create a config/database.yml file that uses PostgreSQL. The Gemfile will also load the ‘pg’ gem instead of the ‘sqlite3’ gem.
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
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!)
from pg_shadow; # sysid and password hash listed here
\l # list
\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:
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:
7. Create all databases based on database.yml.
Optional: Graphical UI for PostgreSQL
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.