Saturday, November 24, 2012

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.


  • 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: 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://' should yield: username "someuser" password "somepassword" host "" 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 -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://' -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 -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 -D somedatabase < b001-data-mysql.sql


heroku run 'rake db:migrate' -a appname


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.


Unknown said...

What motivated you to move off of Postgres onto MySql? The opposite seems to be the more popular route. What advantages did you see on MySql or weaknesses on Postgres?

Andy Maleh said...

Several reasons relevant to my current company:
- Ease of hiring for MySQL devs in Chicago
- Pricing for ClearDB
- Dual-master replica fault tolerance in ClearDB
- Upgrade path to Amazon RDS MySQL
- Friction-free painless vertical scaling with no need to take the site down for DB upgrades (it is more friction free than Heroku Postgres, which required a backup and a restore when we tested it).

Heroku PostgreSQL is definitely good too. Heroku now supports a nice Follow feature that lets you generate read-replicas for reporting/off-master queries. They added other useful features too, but ClearDB was the better fit for us.

Cameron Askew said...

funny, I am about to switch from ClearDB to Heroku Postgres because you cannot have stored procedures or triggers with ClearDB unless you pay for one of their packages that's at least 400/month.

Topher Hunt said...

Thanks for sharing @Annas. I'm in a similar situation, fortunately I've realized this pretty early on (before the site is big enough to hit performance issues) but the upgrade path and the staggering price of Heroku Postgres is really turning us off, so we're about to upgrade.

We're using Rails` `yaml_db` gem to export and import the database content. I think it's easier than trying to wrangle with database-native dumps.