Migrating PostgreSQL to MySQL: Hard-won Lessons and a Real-world Workflow – ITFROMZERO

Table of Contents

Why Go Against the Grain: From PostgreSQL to MySQL?

While PostgreSQL is often more highly regarded for its features, reality sometimes forces our hand. A year ago, I managed a 50GB system on AWS running MySQL 8.0. The new dev team was well-versed in MySQL but struggled with PostgreSQL. Maintenance and query optimization became incredibly slow. To unify the tech stack, I decided to make this “u-turn.”

The main reason often lies in the ecosystem. Perhaps your application uses a CMS or Framework with better MySQL support. Sometimes, the cost of Managed Services for MySQL on cloud providers is significantly lower. However, migrating isn’t as simple as exporting and importing. These two systems have very different philosophies regarding data types and SQL syntax.

Migration Tools: Don’t Do It Manually

To minimize risk, you should use tools instead of writing custom scripts. MySQL Workbench Migration Wizard is the most stable option I’ve tried. It handles data type mapping quite smoothly.

Before you start, prepare the following:

The latest version of MySQL Workbench on your local machine or an intermediary server.
The ODBC driver for PostgreSQL (psqlODBC) so Workbench can connect to Postgres.
SUPER privileges, or at least CREATE and INSERT, on the destination MySQL instance.

# Install ODBC driver on Ubuntu:
sudo apt-get install odbc-postgresql

Once installed, open Workbench and go to Database -> Migration Wizard. The official process begins here.

3 Critical Migration Stages

Here is the roadmap I used to successfully migrate 50GB of data in about 4 hours.

1. Schema Mapping

PostgreSQL uses Schema for hierarchy within a Database. Conversely, MySQL treats each Database as a Schema. Pay close attention to these data types:

Boolean: Postgres has BOOLEAN; MySQL will automatically map this to TINYINT(1).
Serial: Postgres’s SERIAL must be converted to AUTO_INCREMENT in MySQL.
JSON: PostgreSQL excels with JSONB, while MySQL 8.0 supports JSON, but query speeds will differ.
Large Data: TEXT in Postgres is very flexible. For MySQL, consider using MEDIUMTEXT or LONGTEXT if the data exceeds 64KB.

2. SQL Syntax Refactoring

When Workbench generates the CREATE TABLE script, don’t just click Next. Stop and check the Constraints. PostgreSQL allows identical Index names across different tables, but MySQL requires Index names to be unique within the entire schema.

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    metadata JSONB
);

-- Convert to MySQL, needs to be changed to:
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    metadata JSON
) CHARACTER SET utf8mb4;

A hard-won lesson: Rewrite Stored Procedures and Triggers by hand. The syntax for PL/pgSQL and MySQL is worlds apart. Automated tools often produce buggy code in this area.

3. Data Migration (The Push)

I usually split the data into batches of about 5,000 – 10,000 rows to avoid timeouts. If doing this manually via SQL files, double-check date formats and escape characters. MySQL is stricter about sql_mode, which can easily trigger errors if Postgres data contains unhandled special characters.

Post-Migration Testing and Optimization

Never trust tools blindly. The first thing you must do is compare the Row Count between both sides.

-- Run this command on both sides to compare
SELECT count(*) FROM orders;

Next, randomly pick about 100 critical records to compare every data field. A small tip: use the CHECKSUM TABLE function in MySQL to compare against the corresponding hash value from Postgres.

Regarding performance, don’t forget to run ANALYZE TABLE immediately after finishing. This command helps MySQL update statistics for the query optimizer.

ANALYZE TABLE users, orders, products;

During the first week, enable the Slow Query Log. Some queries run very fast on Postgres thanks to specific indexes but might cause a Full Table Scan on MySQL. Early detection allows you to add indexes promptly, preventing system hangs during high traffic.

Migrating a database is like moving house. Belongings are easily lost or damaged without a careful inventory. As long as you understand the differences between the two systems, you will get your data to its destination safely.