Crysfel Villa Feb 24, 2014

How to migrate from PostgreSQL to MySQL

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.