Change from SQLite3 to PostgreSQL in a new Ruby on Rails project

This is a summary of the steps to change the database of a new project created with Ruby on Rails.


Reasons to change PostgreSQL from SQLite

The standard database for Ruby on Rails is SQLite3 </ b>, but when deploying to Heroku, SQLite3 gives an error. </ b> If you change to PostgreSQL at the time of rails new, you can save the trouble of migrating data later.

Ready to use Postgres

Install Homebrew on mac

First install the xcode required to install Homebrew.

install xcode

xcode-select --install

Install Homebrew

/bin/bash -c "$(curl -fsSL"

Click here for the official page: Homebrew

Install postgres on mac via brew

You can download the latest stable version by specifying postgresql.

brew install postgresql

Postgres initial settings

Update .bash_profile

ECHO 'export PGDATA=/usr/local/var/postgres' >> .bash_profile

Reload .bash_profile

source ~/.bash_profile

Create a user

Create a user to manage the database.

 createuser <username>

Use Postgres in Rails projects

Change the part that is SQLite3 in Gemfile to PostgreSQL

Update the Gemfile and change the description of SQLite3 to PostgreSQL.

Change before


# Use sqlite3 as the database for Active Record
gem 'sqlite3', '~> 1.4'

After change


# Use Postgres as the database for Active Record
gem 'pg'

Install the gem with bundle install.

bundle install

Edit database.yml to configure database


 default: &default
   adapter: postgresql
   encoding: unicode
   pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
   timeout: 5000

   <<: *default
   database: myapp_dev #Arbitrary database name

   <<: *default
   database: myapp_sandbox #Arbitrary database name

   <<: *default
   database: myapp_production #Arbitrary database name
   username: <username> #Any username
   password: request.env['DB_PASSWORD'] #Any password
   adapter: postgresql
   encoding: unicode
   pool:  <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

Write password in .env

If you write the password as it is in database.yml, the password will be exposed when you update it on GitHub with Public. Create a .env file to manage your environment variables. Add the .env file to .gitignore to prevent it from being uploaded to GitHub.


DB_PASSWORD = '<password>'



Create a database

bundle exec rails db:create

If the following message appears, PostgreSQL has completed the database creation.

Created database 'myapp_dev'
Created database 'myapp_sandbox'

Recommended Posts