Migrating from MySQL to Postgres on Heroku
Last updated December 01, 2022
Table of Contents
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. 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
To ensure dev/prod parity, run your application in development with PostgreSQL first. The Heroku Postgres docs have instructions for installing PostgreSQL locally.
Install pgloader
The pgloader project is the most mature utility for converting databases from MySQL to Postgres. Install pgloader by following the instructions in the project README.
Migrate data
Create a Postgres database and use the CLI to migrate your MySQL data to it with pgloader:
createdb pgdbname
pgloader mysql://username:password@localhost/mysqldbname postgresql:///pgdbname
See the usage section of the README or the pgloader quick start for more details.
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 6 MB 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.