Import Heroku Postgres to SQLite3 in your local Rails

heroku_pg_to_sqlite3.txt

### Disclaimer,
- datetime is not copied with the timezone difference. seems like the dates are saved in UTC so there will be difference with the datetime columns

---


in this guide,
  postgres username = "jep"
  postgres password = "jepjep"


1. download postgres database backup from your heroku postgres add-on
  1.1 Go to your heroku dashboard
  1.2 Select your heroku application
  1.3 Under "Installed add-ons", click "Heroku Postgres"
  1.4 You will be redirected to another page, click the tab link "Durability"
  1.5 You will see list of database backups at the bottom, download the latest or whichever is needed
  1.6 copy this downloaded file to your sqlite3 app (/path/to/your/sqlite3app)


2. import the database backup to a postgres database in your LOCAL env. we will name the postgres database as "newlocal_pg_dev"
  cd /path/to/your/sqlite3app
  sudo -u jep createdb newlocal_pg_dev
  pg_restore --clean --no-acl --no-owner -h localhost -U jep -d newlocal_pg_dev <downloaded_backup_filename>
  * this will take a while to finish depending how big the backup is


3.a. import all data to sqlite3 database using rails console
  cd /path/to/your/sqlite3app
  bundle exec rails db:drop db:create db:schema:load
  bundle exec rails db:migrate:up VERSION=20191111060623  ->> the latest migrate
  rails console
    ActiveRecord::Base.connection.data_sources.each do |table_name|
      FileUtils.mkdir_p("db/to_import") unless File.directory?("db/to_import")
      filename = "db/to_import/#{table_name}_data.sql"
      system("PGPASSWORD=jepjep pg_dump --no-owner -U jep --attribute-inserts -t #{table_name}  -a -b newlocal_pg_dev > " + "#{filename}")

      # sed commands will change some known postgres commands to their sqlite3 equal
      # some just comment the ones that are not for sqlite3
      system("sed -i \'\' \'s/true/\"t\"/g\' #{filename}")
      system("sed -i \'\' \'s/false/\"f\"/g\' #{filename}")
      system("sed -i \'\' \'s/public.//g\' #{filename}")
      system("sed -i \'\' \'s/SET/-- SET/g\' #{filename}")
      system("sed -i '' 's/SELECT pg_catalog/-- SELECT pg_catalog/g' #{filename}")

      system("sqlite3 db/development.sqlite3 < #{filename}")
    end
    exit


3.b. import SPECIFIC query result to sqlite3 database
  * this is if we dont want to import ALL the records if for example there are so many and we only need some
  * for example the query will be searching for a specific Created At only

  example: "posts" table

  cd /path/to/your/sqlite3app
  bundle exec rails db:drop db:create db:schema:load
  bundle exec rails db:migrate:up VERSION=20191111060623  ->> the latest migrate

  psql -U jep -d newlocal_pg_dev
  drop table export_table;
  create table export_table as
  select *
  from posts
  WHERE (created_at >= '2019-11-01' AND created_at <= '2019-11-30');
  \q

  PGPASSWORD=jepjep pg_dump --no-owner -U jep --attribute-inserts -t export_table -a -b newlocal_pg_dev > /path/to/your/sqlite3app/db/to_import/posts_data.sql
  sed -i '' 's/true/"t"/g' /path/to/your/sqlite3app/db/to_import/posts_data.sql
  sed -i '' 's/false/"f"/g' /path/to/your/sqlite3app/db/to_import/posts_data.sql
  sed -i '' 's/public.//g' /path/to/your/sqlite3app/db/to_import/posts_data.sql
  sed -i '' 's/SET/-- SET/g' /path/to/your/sqlite3app/db/to_import/posts_data.sql
  sed -i '' 's/SELECT pg_catalog/-- SELECT pg_catalog/g' /path/to/your/sqlite3app/db/to_import/posts_data.sql
  sed -i '' 's/export_table/posts/g' /path/to/your/sqlite3app/db/to_import/posts_data.sql
  
  sqlite3 /path/to/your/sqlite3app/db/development.sqlite3 < /path/to/your/sqlite3app/db/to_import/posts_data.sql

If some issues happen with replacing true or false using sed, check the sql file generated.
Sometimes it should be replaced with "t" (or "f") rather than t (or f), depending on the table.
Sometimes it even needs to be 1 or 0. Just be aware of what you need.

The gist above replaces using the one with quotes.