Moving from sqlite3 to postgresql database for your rails project

Rails uses sqlite3 by default on development. If you are not careful enough, you may get so comfortable with sqlite which I did. After a while, there will be a need to push the application to production and at this point, you wouldn’t want sqlite on your web server. I’m using heroku for my app and they stated some good reasons not to use sqlite. On heroku, you wouldn’t be able to push your application to production if sqlite3 remains in your Gemfile.

If you are starting a fresh project, you can just begin this way:

~$: rails new myproject -d postgresql

On an existing project you’ll have to replace modify your Gemfile to replace

gem 'sqlite3'

with

gem 'pg'

run

~$: bundle install

After which you should change your database.yml in config directory. It should look somewhat like this:

default: &default
  adapter: postgresql
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: development_db
  username: joe
  password: 'foobar'

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: test_db
  username: joe
  password: 'foobar'

production:
  <<: *default
  database: production_db
  username: appname
  password:

The adapter has been changed from the default sqlite3 to postgresql.

Next you should create this db in postgre with rake

~$:bin/rake db:create

If you are like me, you should encounter an error at this point. You can’t access psql. This is because the username (joe) is not set as a role in psql. To create one

~$:sudo -u postgres createuser --interactive

If you’re on psql < 9.3 you may encounter an error with the –interactive switch so it’s best you try to upgrade.

Now you can create a password for the user that has been created. Log in to psql

~$:sudo -u postgres psql

and alter the role of the user created with:

ALTER ROLE joe WITH PASSWORD 'secret';

If for any reasons this fails, you should modify your pg_hba.conf file to use md5.