Jimmy's Ruby on Rails Quest: Switching from SQLite to PostgreSQL in Development Environment

I don't understand why Ruby on Rails Tutorial uses SQLite database for development and uses PostgreSQL database in Heroku for production.
Why don't they use PostgreSQL for all the environments? To use PostgreSQL in development environment, it is as simple as just adding the "-d postgresql" parameter when creating a new Rails project.

$ rails new myapp -d postgresql

I am writing this because when I was going further after the Rails Tutorial, I just wanted to add Comments and Articles models for a message board, some of the database migration codes that worked well in Sqlite but they do not work in Heroku's PostgreSQL.
For example, in db/migrate/20170812232746_add_user_ref_to_articles.rb

# This line works in Sqlite development enviroment
# This line does not work in Heroku's PostgreSQL production environment
add_index :articles, [:user_id, :created_at]

So, I decided to switch from Sqlite database to PostgreSQL database in my Linux development environment. Approximately, this was what I did for the switching of the database.

In project: myapp_postgres

* I created a new Rails project that uses PostgreSQL:

$ cd ror
$ rails new myapp_postgres -d postgresql

* I made sure that I could create the PostgreSQL database and started the Rails server.

$ cd myapp_postgres
$ rake db:create
$ rails s -b 0.0.0.0 -p 3000

* I went "Turbo Speed" for the Chapter 2 of Rails Tutorial. I did NOT change the Gemfile of myapp_postgres project. And I did NOT change database.yml.

* I initialized a local Git repository, added all files, committed the changes, added remote Github URL, and pushed it onto Github.

$ git init
$ git add -A
$ git commit -m "Initialize repository"
$ git remote add origin https://github.com/jimmy2046/myapp_postgres.git
$ git push -u origin --all

* I created the "hello" action, committed the changes, created an Heroku project and pushed it onto Heroku.

$ git commit -am "Add hello"
$ heroku create
$ git push heroku master

* I generated the scaffold for User and made sure the database migration command worked.

$ rails generate scaffold User name:string email:string
$ rails db:migrate

* I generated the scaffold for Micropost.

$ rails generate scaffold Micropost content:text user_id:integer
$ rails db:migrate

* Using Firefox, I tried to add a new user and added a new micropost in order to make sure the local PostgreSQL database was working properly in development environment.

* At the end of Chapter 2, I added all untracked files, committed the changes, and pushed project myapp_postgres onto Github.

$ git add -A
$ git commit -m "Finish toy app"
$ git push

* At the same time, I pushed myapp_postgres to Heroku and ran database migration for Heroku production.

$ git push heroku
$ heroku run rails db:migrate

* At that time, I had a new PostgreSQL database project called myapp_postgres with configurations and settings for Heroku PostgreSQL deployment.

In project: myapp2

* After that, I went back to myapp2 project. I changed the the directory myapp2 and checked out a new branch called: "attempt-switch-to-postgresql".

$ cd ~/myapp2
$ git checkout -b attempt-switch-to-postgresql

* In myapp2, I made a backup copy of the database.yml file. And then, I copied and pasted the database.yml file, that I got from myapp_postgres using "$ rails new myapp_postgres -d postgresql" to myapp2.
myapp2/config/database.yml

# PostgreSQL. Versions 9.1 and up are supported.
#
# Install the pg driver:
#   gem install pg
# On OS X with Homebrew:
#   gem install pg -- --with-pg-config=/usr/local/bin/pg_config
# On OS X with MacPorts:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem 'pg'
#
default: &default
adapter: postgresql
encoding: unicode
# For details on connection pooling, see Rails configuration guide
# http://guides.rubyonrails.org/configuring.html#database-pooling
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
<<: *default
database: myapp_postgres_development

# The specified database role being used to connect to postgres.
# To create additional roles in postgres see `$ createuser --help`.
# When left blank, postgres will use the default role. This is
# the same name as the operating system user that initialized the database.
#username: myapp_postgres

# The password associated with the postgres role (username).
#password:

# Connect on a TCP socket. Omitted by default since the client uses a
# domain socket that doesn't need configuration. Windows does not have
# domain sockets, so uncomment these lines.
#host: localhost

# The TCP port the server listens on. Defaults to 5432.
# If your server runs on a different port number, change accordingly.
#port: 5432

# Schema search path. The server defaults to $user,public
#schema_search_path: myapp,sharedapp,public

# Minimum log levels, in increasing order:
#   debug5, debug4, debug3, debug2, debug1,
#   log, notice, warning, error, fatal, and panic
# Defaults to warning.
#min_messages: notice

# 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: myapp_postgres_test

# As with config/secrets.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password as a unix environment variable when you boot
# the app. Read http://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full rundown on how to provide these environment variables in a
# production deployment.
#
# On Heroku and other platform providers, you may have a full connection URL
# available as an environment variable. For example:
#
#   DATABASE_URL="postgres://myuser:mypass@localhost/somedatabase"
#
# You can use this database configuration with:
#
#   production:
#     url: <%= ENV['DATABASE_URL'] %>
#
production:
<<: *default
database: myapp_postgres_production
username: myapp_postgres
password: <%= ENV['MYAPP_POSTGRES_DATABASE_PASSWORD'] %>

* In Gemfile, I put gem 'pg', '0.18.4' on the top section. That meant Rails would use PostgreSQL for all Development, Testing and Production environments. I put a remark comment for gem 'sqlite3', '1.3.13' in development group, and I commented out the gem 'pg', '0.18.4' in Production group.
myapp2/Gemfile

source 'https://rubygems.org'

gem 'rails',        '5.1.2'
gem 'bcrypt',         '3.1.11'
gem 'faker',          '1.7.3'
gem 'carrierwave',             '1.1.0'
gem 'mini_magick',             '4.7.0'
gem 'fog',                     '1.40.0'
gem 'will_paginate',           '3.1.5'
gem 'bootstrap-will_paginate', '1.0.0'
gem 'bootstrap-sass', '3.3.7'
gem 'puma',         '3.9.1'
gem 'sass-rails',   '5.0.6'
gem 'uglifier',     '3.2.0'
gem 'coffee-rails', '4.2.2'
gem 'jquery-rails', '4.3.1'
gem 'turbolinks',   '5.0.1'
gem 'jbuilder',     '2.7.0'

gem 'pg', '0.18.4'

group :development, :test do
# gem 'sqlite3', '1.3.13'
gem 'byebug', '9.0.6', platform: :mri
end

group :development do
gem 'web-console',           '3.5.1'
gem 'listen',                '3.0.8'
gem 'spring',                '2.0.2'
gem 'spring-watcher-listen', '2.0.1'
end

group :test do
gem 'rails-controller-testing', '1.0.2'
gem 'minitest-reporters',       '1.1.14'
gem 'guard',                    '2.13.0'
gem 'guard-minitest',           '2.4.4'
end

group :production do
# gem 'pg', '0.18.4'end

# Windows does not include zoneinfo files, so bundle the tzinfo-data gem
gem 'tzinfo-data', platforms: [:mingw, :mswin, :x64_mingw, :jruby]

* Then, I ran bundle update.

$ bundle update

* And the, I restarted the my local Rails server.

Ctrl + C
$ rails s -b 0.0.0.0 -p 3000

* In local Linux development environment, I attempted to drop the database, created a new one. But, I was stuck during database migration.

$ rake db:drop db:create db:migrate

* It took me some time for my debug skill. Afterwards, I figured out that the add_index for user_id code worked well in Sqlite but not in PostgreSQL. So, I commented out the add_index line for model Articles and model Comments.
myapp2/db/migrate/20170812232746_add_user_ref_to_articles.rb

class AddUserRefToArticles < ActiveRecord::Migration[5.1]
def change
    add_reference :articles, :user, foreign_key: true
end

#    add_index :articles, [:user_id, :created_at]
end

Similarly,
myapp2/db/migrate/20170814052759_add_user_to_comments.rb

class AddUserToComments < ActiveRecord::Migration[5.1]
def change
add_reference :comments, :user, foreign_key: true
end
# add_index :comments, [:user_id, :created_at]
end

* After I had commented out the add_index to the database, I ran database migration again. The migration was successful.

$ rails db:migrate

* Then, I seeded the database.

$ rails db:seed

* Then, I deleted the old Sqlite database file in myapp2/db directory.
myapp2/db

deleted: test.sqlite3
deleted: development.sqlite3

* Finally, I pushed the changes to Github and deployed to Heroku.

$ rails test
$ git add -A
$ git commit -m "Changed to PostgreSQL in Development Env"
$ git checkout master
$ git merge attempt-switch-to-postgresql
$ git push

$ git push heroku
$ heroku pg:reset DATABASE
$ heroku run rails db:migrate
$ heroku run rails db:seed