Migrating from MySQL to Postgres on Heroku

Last Updated: 31 March 2014

migrate mysql postgres

Table of Contents

MySQL is a popular relational database maintained by Oracle. Though Heroku Postgres is the recommended database on Heroku because of its tight-integration with the platform and excellent management services and tools, there are many options for applications currently running on MySQL.

Migrate to Heroku Postgres

Most applications that utilize an ORM library to access the database will find little trouble in switching to a PostgreSQL database. Follow the following steps to switch from MySQL to PostgreSQL.

Run PostgreSQL locally

To ensure dev/prod parity you should run your application in development with PostgreSQL first. The Heroku Postgres docs have instructions for installing PostgreSQL locally

Install mysql2psql import utility

The mysql2psql ruby gem is the most mature utility for converting databases from MySQL to Postgres. Although using this tool requires that you have RubyGems installed, it is entirely independent from your application language.

Install the mysql2psql gem in your development environment and execute the mysql2psql command to generate the configuration file template:

$ gem install mysql2psql
Fetching: mysql2psql-0.1.0.gem (100%)
Successfully installed mysql2psql-0.1.0
1 gem installed

$ mysql2psql
No configuration file found.
A new file has been initialized at: /Users/you/dev/app/mysql2psql.yml
Please review the configuration and retry..
...

This will generate a mysql2psql.yml file which you will need to edit to specify both your existing MySQL database and new PostgreSQL db.

Specify db locations

Edit the mysql and destination sections of the generated mysql2psql.yml file to reference the appropriate local databases and resemble the following format:

mysql:
 hostname: localhost
 port: 3306
 socket: /tmp/mysql.sock
 username: user
 password: password
 database: mysql-db

destination:
 postgres:
  hostname: localhost
  port: 5432
  username: you
  password: password
  database: pg-db

This configuration file uses the same format as ActiveRecord, so you can copy and paste the connection settings from your application’s config/database.yml if you’re using Rails.

Migrate data

Execute the mysql2psql command again to initiate the data transfer from MySQL to PostgreSQL.

$ mysql2psql

mysql2psql tool is capable of migrating approximately 100,000 rows per minute. Migration time will depend on the size and nature of your database.

Once you’ve migrated the data to PostgreSQL locally you can use PG Backups to import this data to your Heroku Postgres database.

Run your application against the new PostgreSQL database to identify and resolve any common errors.

Provision a MySQL Add-on

If your application is using MySQL-specific features, there are a number of MySQL providers in the Heroku Add-ons marketplace you can use instead of Heroku Postgres. Please see the add-on documentation for provisioning instructions as they vary by provider.