Migrating from MySQL to Postgres on Heroku
Last updated 15 November 2017
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
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.
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
destination sections of the generated
mysqltopostgres.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.
mysqltopostgres command again to initiate the data transfer from MySQL to PostgreSQL.
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.