Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Import Heroku Postgres to SQLite3 in your local Rails
### 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"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}")
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');
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

This comment has been minimized.

Copy link
Owner Author

@jeffreycastro jeffreycastro commented Nov 29, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment