Migrating from sqlite3 to postgresql in Rails apps.

Heroku for some reason doesn't support sqlite3 in their deployments. So, it is must to migrate your db from default sqlite3 to mysql or postgresql.

• In order to do that you need to first add pg gem to your Gemfile file:
  gem 'pg'

• Then install the gem with bundle install.
  bundle install

• change database.yml file to use postgresql
  config/database.yml
  

development:
  adapter: postgresql
  database: my_app_development
  pool: 5
  timeout: 5000
test:
  adapter: postgresql
  database: my_app_test
  pool: 5
  timeout: 5000

production:
  adapter: postgresql
  database: my_app_production
  pool: 5
  timeout: 5000

• Start postgres as service on your local machine: brew services start postgresql

• Now create the database by running rake db:create
  this creates local db

• Create the tables using the famous rake db:migrate command.

• If you have seed data like I do, then do rake db:seed

Troubleshooting

Sometimes the postgresql that's on your machine might be old one and so it becomes problematic to run postgresql service and create local database that your development env is trying to connect to. If that's the case then you need to update your postgres version

1. Update postgresql brew postgresql-upgrade-database
2. Restart postgres service brew services restart postgresql
3. Go to your apps root & create the database in postgresql rails db:create This will create a new my_app_development db in your local postgres instance
4. Now the usual steps follow like migrating db & seeding data if you have any. rails db:migrate rails db:seed