Heroku DB Migration from PostgreSQL to ClearDB

I have been recently working as the software architect of a Ruby on Rails application running on Heroku, and as part of the work, we decided to migrate off of Heroku's default PostgreSQL database into the MySQL based ClearDB.

To do so, I tried relying on Heroku's taps gem, but it produced mediocre results with some MySQL database columns not matching the same null or default value constraints in the source PostgreSQL database.

I ended up coming up with my own approach to perform a successful perfect database migration, and I would like to list the steps over here for anybody else who might undertake the same endeavor since the Heroku and ClearDB guides do not include any migration instructions as of today from the Heroku PostgreSQL database.

Prerequisites

• Setup PostgreSQL database ("brew install postgresql" then follow post-installation instructions. version 9 would do)
• Setup ClearDB on your Heroku app and add SSL keys to your application as per their instructions
• Obtain pg2mysql_cli.php tool from over here: http://www.lightbox.ca/pg2mysql.php and extract it to a local directory, under which you will run the commands mentioned under the instructions section.
• Obtain the URLs for the PostgreSQL and ClearDB databases from Heroku by running the command "heroku config -a appname"
• Extract the username, password, host, and database name from the database URLs as mentioned in the Heroku PostgreSQL guide and ClearDB guide
• For example: 'mysql2://someuser:somepassword@us-cdbr-east-02.cleardb.com/somedatabase?reconnect=true' should yield: username "someuser" password "somepassword" host "us-cdbr-east-02.cleardb.com" and database "somedatabase"

Migration Instructions

Turn on Heroku's maintenance page to stop write traffic into the database

heroku maintenance:on -a appname

Backup the Heroku PostgreSQL database

heroku pgbackups:capture -a appname

Download the Heroku PostgreSQL backup

curl -o b001.dump `heroku pgbackups:url -a appname`

Restore PostgreSQL backup locally in order to convert to uncompressed format

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U local_postgres_db_user -d postgresdbname b001.dump

Generate new uncompressed data-only backup (format most appropriate for ClearDB)

pg_dump -U local_postgres_db_user --data-only --column-inserts --format p -f b001-data.sql postgresdbname

Run pg2mysql_cli.php command from the pg2mysql tool directory to convert PostgreSQL backup to MySQL backup

php pg2mysql_cli.php b001-data.sql b001-data-mysql.sql

Clear ClearDB MySQL DB completely

MYSQL="mysql -h us-cdbr-east-02.cleardb.com -u someuser -psomepassword -D somedatabase"
 $MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `" $1 "`;"}' | $MYSQL
 unset MYSQL

Set ClearDB as the main DB for the Heroku app (assuming you are relying on SSL for security and followed the ClearDB instructions for settings it up)

heroku config:add DATABASE_URL='mysql2://someuser:somepassword@us-cdbr-east-02.cleardb.com/somedatabase?sslca=cleardb-ca.pem&sslcert=someuser-cert.pem&sslkey=someuser-key.pem&reconnect=true' -a appname

Initialize ClearDB schema from schema.rb in the Rails app

heroku run 'rake db:schema:load' -a appname

Clear any database tables that got populated in the schema initialization process

MYSQL="mysql -h us-cdbr-east-02.cleardb.com -u someuser -psomepassword -D somedatabase"
 $MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; delete from `" $1 "`;"}' | $MYSQL
 unset MYSQL

Load ClearDB with MySQL backup data obtained from the Heroku PostgreSQL DB

mysql -u someuser -psomepassword -h us-cdbr-east-02.cleardb.com -D somedatabase < b001-data-mysql.sql

Migrate

heroku run 'rake db:migrate' -a appname

Seed

heroku run 'rake db:seed' -a appname

Turn off Heroku's maintenance page

heroku maintenance:off -a appname

That's all folks! Questions and comments are welcome.