How to import a big database from Heroku to local mysql or sqlite3?

Question

As per title I need to import, but PG backups is giving me strict Postgres SQL that doesn't work with MySQL, also with a non-specified encoding that I guess is UTF-16. Using db:pull takes ages and errors before finishing. I'd appreciate any suggestion. Thanks.

Answer 1

Heroku has instructions on how to do this: https://devcenter.heroku.com/articles/heroku-postgres-import-export#restore-to-local-database, which boil down to:

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

where myuser is the current user and mydb is the current database.

If you're using Postgres.app, it's pretty trivial to copy your production database locally. You can leave out -U myuser unless you have configured it otherwise, and create a database by running $ psql -h localhost and then CREATE DATABASE your_database_name; (from the Postgres.app documentation. Then run the above command and you're set.

Answer 2

Set up PostgreSQL locally, use PG backups to copy the data from Heroku to your local machine, then pg_restore to import it into your new local PostgreSQL. Then you can copy it from PostgreSQL to MySQL or SQLite locally without having to worry about timeouts. Or, since you'd have a functional PostgreSQL installation after that, just start developing on top of PostgreSQL so that your development stack better matches your deployment stack; developing and deploying on the same database is a good idea.

You're probably getting binary dumps (i.e. pg_dump -Fc) from Heroku, that would explain why the dump looks like some sort of UTF-16 nonsense.

You can use the pgbackups addon to export the database dump

$ heroku addons:add pgbackups # To install the addon
$ curl -o latest.dump `heroku pgbackups:url` # To download a dump