When you start using Rails, if you, like me, just use things the way they come out of the box, you were probably using SQLite3 as your default database. Your first inkling that this might not be sufficient will come when you try to push your Rails app to Heroku’s servers (“dynos.”) Heroku wants you to use PostgreSQL instead.

I ran into trouble when my app was modified by the Heroku push to use the pg gem instead of sqlite3. After the modification, I couldn’t use the rails command any more as it started crapping out on me, insisting that I needed to have an activerecord-sqlite3-adapter.

So I started a new app from scratch (I’m still learning, see, so my apps are all throw-away at this point) and this time set the database to be PostgreSQL right from the start. As the Heroku guys say on their blog post about why they chose PostgreSQL:

At Heroku, we believe PostgreSQL offers the best mix of powerful features, data integrity, speed, standards compliance, and open-source code of any SQL database on the planet. That’s why we were so excited to see the new release of PostgreSQL, version 9.0.1.

You could also set your Gemfile up to use sqlite3 in your development environment, and PostgresQL in your production environment by separating the SQLite3 and PostgreSQL into separate groups in your Gemfile:
group :development do
gem ‘sqlite3’
group :production do
gem ‘pg’
gem ‘activerecord-postgresql-adapter’

Update: Note that if you intend to build your bundle for the production environment on your own computer and not just on Heroku, you will need the ‘pg’ gem as well. You have to install the development environment for Postgres, which is usually the libpq-dev library – on Debian/Ubuntu, you can get that with apt-get install libpq-dev, for example.

PostgreSQL of course requires “proper” configuration like any database, unlike SQLite, which works in a very “light” way – you just install Rails and the database is there, in the background. But PostgreSQL requires you to do the following:

  1. Install the server itself
  2. Configure the initial database folder
  3. Create the database your app is going to use
  4. Create a user that can access this database.
  5. Add the following code to your database.yml file, with the appropriate values entered. If you don’t enter the database name explicitly, by default, Rails will expect that the name of your database is the same as that of your app, suffixed with the string _development or _production, corresponding to those two environments in your app
      adapter: postgresql
      host: localhost
      username: me
      database: mydatabasename

Steps 1 and 2, and more, are described in a couple of excellent blog posts, one on how to build and configure your first PostgreSQL DB (building is a lot easier than this if you are using a Mac OS and have access to Homebrew), and one with practical PostgreSQL administration commands, including how to create users, assign database ownership, list all your databases, and more.