Migrating from MySQL to Postgres on Heroku
Last updated 17 August 2018
MySQL is a popular relational database maintained by Oracle. Although Heroku Postgres is the recommended relational database for Heroku apps because of its tight integration with the platform, there are options for applications that currently run on MySQL.
Provision a MySQL add-on
If your application uses 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 your chosen add-on’s documentation for provisioning instructions, as they vary by provider.
Migrate to Heroku Postgres
Most applications that use an ORM library to access their database can easily switch to a PostgreSQL database. Follow these steps to switch from MySQL to PostgreSQL:
Run PostgreSQL locally
Create a Postgres database and use the CLI to migrate your MySQL data to it with
createdb pgdbname pgloader mysql://username:password@localhost/mysqldbname postgresql:///pgdbname
Migration time depends on the size and nature of your database.
pgloader includes a
bytes metric in its default report to help you estimate the migration’s progress. On older hardware, a migration rate of approximately 3 million rows per minute is common. On some production hardware,
pgloader reaches a hard limit of 6MB per second, which is bottlenecked by the CPU.
Run your application against the new PostgreSQL database to identify and resolve any common errors.
After you migrate your data to PostgreSQL locally, you can use PG Backups to import this data to your Heroku Postgres database.