Migrate a MySQL DB to Postgres on Heroku

gistfile1.md

Migrating a MySQL DB to a Heroku Postgres Instance

1. Create a MySQL Dump

mysqldump -h<host> --compatible=postgresql -u<user> -p <database_name> > /tmp/my_dump.sql

2. Import MySQL dump locally

cat my_dump.sql | mysql -h<host> -u<user> -p <database_name>

3. Transfer to local Postgres instance

Use the mysql2psql gem as recommended by Heroku:

$ gem install mysql2psql

...

$ mysql2psql
No configuration file found.
A new file has been initialized at: /Users/you/dev/app/mysql2psql.yml
Please review the configuration and retry..

Edit the generated config:

mysql:
 hostname: localhost
 port: 3306
 socket: /tmp/mysql.sock
 username: <username>
 password: <password>
 database: <db_name>

destination:
 # if file is given, output goes to file, else postgres
 file: 
 postgres:
  hostname: localhost
  port: 5432
  username: <username>
  password: <password>
  database: <db_name>

Run the transfer using the specified config:

$ mysql2psql

4. Import Dump into Heroku Postgres Instance

Following Heroku's instructions here:

4a. Dump Local Postgres Instance

PGPASSWORD=<password> pg_dump -Fc --no-acl --no-owner -h <host> -U <user> <db_name> > my_dump.dump

4b. Upload Dump to a Location Heroku Can Access Over HTTP

e.g. S3

4c. Import Data

Using the pgbackups add-on:

heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/foo/bar/my_dump.dump'

The value of DATABASE should be the Heroku database env variable. You can find this with:

$ heroku config | grep HEROKU_POSTGRESQL

hey @tjmw how do you manage a postgres database in mac?

@juliocanare I know it's a two-year-old question, but for future googlers reference: Best to use Docker, for your local setup.