Install Postgres on Debian squeeze for Rails, howto

Intro

Here's how to install Postgres, the popular, open-source database server for Rails 3 development on Debian squeeze (I used Rails 3.2.6). I switched to Postgres for maximum compatibility with Heroku (it's one of their '12-factor app' development principles).

This installation procedure keeps safety particularly in mind for anyone (like me) who has never before used Postgres.

Install

Debian squeeze's normal Postgres version (8.4) is unnecessary, unlike (apparently) the case on Mac OS.

Don't install the latest Postgres from source though postgresql.org recommends it—because its setup for Debian is difficult. Likely you will get mysterious errors such as:

$ bundle exec rake db:create:all --trace
  rake aborted!
  libpq.so.5: cannot open shared object file: No such file or directory - /home/mark/.rvm/gems/ruby-1.9.2-p320@global/gems/pg-0.14.0/lib/pg_ext.so
  /home/mark/.rvm/gems/ruby-1.9.2-p320@global/gems/pg-0.14.0/lib/pg.rb:4:in `require'

Instead use the most recent Postgres, backported to squeeze (currently 9.1.4). Here's how:

Remove old versions of Postgres software (e.g. 8.4) with:
$ apt-get purge libpq-dev libpq5 postgresql postgresql-client postgresql-common

Pay attention to messages, especially those warning about directory names containing 'postgres'. Take appropriate action to remove those directories.

Then clean up if you want to:
$ apt-get clean

Note that libpq is part of Postgres. Edit where Debian gets packages:
$ nano /etc/apt/sources.list

Include backports by adding:
  deb http://backports.debian.org/debian-backports squeeze-backports main

Ruby needs package 'libpq-dev' to connect to Postgres. Get the latest backported Postgres packages:
$ apt-get update
$ apt-get upgrade
$ apt-get -t squeeze-backports install postgresql libpq-dev

Expect to see the message, 'Configuring postgresql.conf to use port 5432' (which is the proper port for PostgreSQL *not* port 5433, which can come about if Debian gets confused).

Automatically, installation should start the Postgres server—look at what's actually running to confirm the port:
$ ls -a /var/run/postgresql

Instead of 5433 you should see:
  .s.PGSQL.5432

Minimally alter one of Postgres's configuration files to accept (app-specific) connections from Rails...:
$ nano /etc/postgresql/9.1/main/pg_hba.conf

by changing 'peer' to 'md5' where it says:

  # "local" is for Unix domain socket connections only
  #local   all         all                               peer
  local   all         all                               md5

Restart the Postgres server:
$ sudo /etc/init.d/postgresql stop
$ sudo /etc/init.d/postgresql start

New app

Now that you have Postgres installed, you can use it to create a new Rails app (which I'll call, 'APP'; replace this with something in lower case):
$ rails new APP -d postgresql; cd APP

Tell Rails you'll be using a database password from environment variables:

$ nano config/database.yml

Do this by changing the relevant lines (without moving them) to:

database:   <%=   ENV['DATABASE_USERNAME']   %>_development
database:   <%=   ENV['DATABASE_USERNAME']   %>_test
database:   <%=   ENV['DATABASE_USERNAME']   %>_production

username:   <%=   ENV['DATABASE_USERNAME']   %>
password:   <%=   ENV['DATABASE_PASSWORD']   %>

Repeat the username and password lines three times, once for each Rails environment.

Decide upon (or generate) a new database password for your app. Create the two environment variables above and set them somehow. If you're using foreman, you can set these in your .env file.

Unfortunately, Rails wants to drop the whole test database, not just its tables. Because it seems difficult to change this, we'll let Rails handle database creation:

Create the app's safe Postgres user. This asks you to enter your new app's password twice:
$ sudo -u postgres createuser --echo --encrypted --pwprompt --no-superuser --no-inherit --createdb --no-createrole APP

(If you made a mistake):
$ sudo -u postgres dropuser APP

Confirm your new app is included in the list of existing databases and users (called 'roles'):
$ sudo -u postgres psql
=> \dg
=> \l
=> \q

Rails should now be working. Test it by something like:
$ foreman run bundle exec rake db:create:all

If Rails merely complains that the three databases already exist, then this setup is working fine.

If Rails didn't work and the final error message started 'Couldn't create database' then scroll up: if you see...:

  could not connect to server: No such file or directory
  Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

then go back and redo the above part regarding installing for the correct port 5432 (not 5433).

If after scrolling you see:

  password authentication failed for user {APP}

then go back and redo the above part regarding creating the 'role' (user) for the new app.

Rake

To run tests, with foreman setting up your local environment, remember that you should be running rake by:

$ foreman run bundle exec bin/rake ...

E.g., to run database migrations, do:

$ foreman run bundle exec bin/rake db:migrate

(And similarly for some other commands.)

Heroku

Heroku (e.g.) rewrites your database.yml to use only the single value in DATABASE_URL, which makes it effectively your production database, so beware. Don't set the DATABASE_URL environment variable (locally) in your .env file.

If you want to troubleshoot Heroku's access to your database with a local setup closer to Heroku's method, make a script (call it: my-foreman) containing this:

environment=$1
shift 1
export DATABASE_URL=\
postgres://$DATABASE_USERNAME:$DATABASE_PASSWORD@\
localhost:5432/$DATABASE_USERNAME'_'$environment
foreman start $@

Then you can run foreman in your desired environment with (e.g.)

./my-foreman production -p 5001

Alternative

Alternatively, you could take this approach:
* Make your own Unix username be a Postgres superuser;
* Keep the local authentication line in pg_hba.conf as,  'peer'; and
* Change the database username in new Rails apps to your Unix username.

But that approach is less safe, especially across apps. If you make a mistake in a new app, you don't want it to overwrite the database of another of your apps.

References

http://backports-master.debian.org/Instructions/
http://railscasts.com/episodes/342-migrating-to-postgresql?view=asciicast
http://wiki.debian.org/Backports
http://wiki.debian.org/PostgreSql#Installation
http://www.jquantlib.org/index.php/Upgrading_PostgreSQL_8.2_to_8.3_on_Debian
http://www.postgresql.org/docs/9.1/interactive/install-short.html
http://www.postgresql.org/docs/9.1/static/auth-methods.html
http://www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source/
http://xtremekforever.blogspot.com/2011/05/setup-rails-project-with-postgresql-on.html

Copyright (c) 2012 Mark D. Blackwell.