Change from SQLite to PostgreSQL in a fresh Rails project

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 f3Shall the new role be a superuser? (y/n) nShall the new role be allowed to create databases? (y/n) yShall 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: 5000development:  adapter: postgresql  encoding: unicode  database: f3_development  pool: 5  username: f3  password:#test:#  adapter: sqlite3#  database: db/test.sqlite3#  pool: 5#  timeout: 5000test:  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