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.
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.
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 databaseheroku 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