Migrating from MySQL to Postgres on Heroku

Last Updated: 13 May 2015

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 mysql2postgres import utility

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

Install the mysqltopostgres gem from source in your development environment by following the instructions on the project README and execute the mysqltopostgres command to generate the configuration file template:

$ git clone https://github.com/maxlapshin/mysql2postgres.git
$ cd mysql2postgres
$ bundle install
$ gem build mysqltopostgres.gemspec
$ sudo gem install mysqltopostgres-0.2.20.gem
$ mysqltopostgres
No configuration file found.
A new file has been initialized at: /Users/you/dev/app/mysqltopostgres.yml
Please review the configuration and retry..

This will generate a mysqltopostgres.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 mysqltopostgres.yml file to reference the appropriate local databases and resemble the following format:

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

  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 mysqltopostgres command again to initiate the data transfer from MySQL to PostgreSQL.

$ mysqltopostgres

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