I have migrated Quizzpot.com from Heroku to Digital Ocean, I been looking for a good solution to migrate my data from Heroku PostgresSQL database to a MySQL database. I tried with MySqlWorkbench but failed setting up the ODBC driver on my MacOS system. I also tried the pg2mysql php script to convert my dump file but it also fails.
Since Im using rails, the database schema gets created easily using the migrations, therefore I wrote a ruby program to get the data from Postgresql and generate a mysql dump file with all my data. Here are the steps I did.
Maintenance mode on in the heroku app to stop writing new data.
$ heroku maintenance:on
Generate a new backup with the latest data.
$ heroku pgbackups:capture --expire
Download the backup with the given URL.
$ heroku pgbackups:url
If the Postgresql server is not running already, start it; otherwise continue to the next step.
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
Restore the backup from heroku to our local Posgresql.
$ pg_restore -O -d dbname latest.dump
Generate the mysql dump file with all the data, this script will not create the tables, it only contains the data as sql inserts.
$ ruby migrate.rb
In your rails app, generate an empty database by running the migrations. This will prepare the mysql database.
$ rake db:migrate
Load the dump into the mysql server.
$ mysql -u root quizzpot < dumps/data.sql
Start you rails app
$ rails s
Now you should have you data from heroku in your local mysql, you can now generate a dump file and import it to your production database on digital ocean or maybe in Amazon RDS.