Change from SQLite to PostgreSQL in a fresh Rails project

Question

You can change your database.yml to this instead of using the out of the box sqlite one:

development:
  adapter: postgresql
  encoding: utf8
  database: project_development
  pool: 5
  username: 
  password:

test: &TEST
  adapter: postgresql
  encoding: utf8
  database: project_test
  pool: 5
  username: 
  password:

production:
  adapter: postgresql
  encoding: utf8
  database: project_production
  pool: 5
  username: 
  password:

cucumber:
  <<: *TEST

The steps below worked for me. It uses the taps gem, created by Heroku and mentioned in Ryan Bates's Railscast #342. There are a few steps but it worked perfectly (even dates were correctly migrated), and it was far easier than the Oracle -> DB2 or SQL Server -> Oracle migrations I have done in the past.

Note that SQLite does not have a user id or password, but the taps gem requires something. I just used the literals "user" and "password".

Create the Postgres database user for the new databases

$ createuser f3
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

EDIT - Updated command below - use this instead

$ createuser f3 -d -s

Create the required databases

$ createdb -Of3 -Eutf8 f3_development
$ createdb -Of3 -Eutf8 f3_test

Update the Gemfile

gem 'sqlite3'
gem 'pg'
gem 'taps'
$ bundle

Update database.yml

#development:
#  adapter: sqlite3
#  database: db/development.sqlite3
#  pool: 5
#  timeout: 5000

development:
  adapter: postgresql
  encoding: unicode
  database: f3_development
  pool: 5
  username: f3
  password:

#test:
#  adapter: sqlite3
#  database: db/test.sqlite3
#  pool: 5
#  timeout: 5000

test:
  adapter: postgresql
  encoding: unicode
  database: f3_test
  pool: 5
  username: f3
  password:

Start the taps server on the sqlite database

$ taps server sqlite://db/development.sqlite3 user password

Migrate the data

$ taps pull postgres://f3@localhost/f3_development http://user:password@localhost:5000

Restart the Rails webserver

$ rails s

Cleanup the Gemfile

#gem 'sqlite3'
gem 'pg'
#gem 'taps'
$ bundle

Now its become easy with the single command

bin/rails db:system:change --to=postgresql

Since you're moving to heroku, you can use taps to do this:

heroku db:push

This will push your local development sqlite data to production, and heroku will automagically convert to postgres for you.

This should also work to push a production sqlite db to heroku, but it's not tested.

RAILS_ENV=production heroku db:push

you will also need to add the line "gem 'pg'" to your gemfile, 'pg' being the current postgres gem for Rails.