postgresql - How do I move my existing rails app onto heroku? (sqlite to postgres)

Question

I have an existing Ruby on Rails app that already has data loaded into it.

I used the default SQLite database setup, so that is where all my data is located, but I need all my data to go into my Postgres database on heroku.

How do I do that?

Answer 1

10 Minutes Move from Local SQLite to a Heroku Postgres

-- updates your local dev to postgres along the way --

This is assuming you have a development database in sqlite and you want to move the structure and data to heroku. You will be first changing your local environment to postgres, then moving it all up.

Why change? You should always have your development environment mirror your production environment. Using Postgres is the default on heroku.

You will need to install and configure Postgres locally first with a user that has your username

---

Software needed: postgresql, pgloader, heroku-cli

---

Steps

Move from SQLite to Postgres on your dev environment

1. install heroku / pgloader / postgres, and make sure postgresql is running on your system
2. backup sqlite - copy development.sql to development_old.sql
3. add gem 'pg' to main section of your Gemfile
4. bundle install
5. update config/database.yml (see sample below)
6. rake db:setup
7. cd [application root]
8. load postgres db with data - pgloader ./db/development.sqlite3 postgresql:///[name of postgres dev db]
9. remove gem 'sqlite3'
10. bundle install
11. start server - rails server
12. test by visiting app at localhost:3000

Setup new app on heroku

Follow these instructions from heroku

Move data to heroku

1. find heroku db info - heroku pg:info
2. erase and reset remote db - heroku pg:reset DATABASE_URL --app [name of app]
3. push local data to heroku - heroku pg:push [name of postgres dev db] DATABASE_URL --app [name of app]

NOTE: if that database has greater than 10k rows, you will also need to upgrade to a hobby-basic tier on heroku

Upgrading Heroku to Hobby Tier Basic

1. create new tier - `heroku addons:create heroku-postgresql:hobby-basic --app [name of app]
2. get the new database url - heroku pg:info
3. turn on maintenance - heroku maintenance:on --app [name of app]
4. copy data - heroku pg:copy DATABASE_URL [HEROKU_POSTGRESQL_COLOR_URL] --app [name of app]
5. promote new db - heroku pg:promote [HEROKU_POSTGRESQL_COLOR_URL] --app [name of app]
6. turn off maintenance
7. test by visiting heroku app

---

In case you run into issues or edge cases, here are some resources to help.

Resources:

• https://pgloader.io
• postgres install docs
• heroku new rails install
• heroku cli info
• using the heroku cli

database_sample.yml

default: &default
  adapter: postgresql
  encoding: unicode
  host: localhost
  port: 5432
  # For details on connection pooling, see Rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: [name of app]_dev

test:
  <<: *default
  database: [name of app]_test

staging:
  <<: *default
  database: [name of app]

production:
  <<: *default
  database: [name of app]