MySQL → PostgreSQL

MySQL to PostgreSQL Migration

Binlog-based CDC, full schema and type conversion, and a validated zero-downtime cutover — from a single MySQL instance or a read replica, without interrupting production traffic.

98.5%

Schema conversion accuracy

50,000

Rows/sec throughput

Binlog CDC

Change capture method

Zero downtime

Cutover mode

Key Differences to Handle

MySQL and PostgreSQL diverge in subtle ways that cause silent data issues or application errors if not handled explicitly. DBMigrateAIPro addresses all of them.

AUTO_INCREMENT → SERIAL / SEQUENCE

MySQL AUTO_INCREMENT is converted to PostgreSQL SERIAL (shorthand) or an explicit SEQUENCE with DEFAULT nextval(). BIGINT AUTO_INCREMENT maps to BIGSERIAL. The sequence value is synced to max(id) + 1 post-bulk-copy.

TINYINT(1) → BOOLEAN

MySQL uses TINYINT(1) as its boolean type. DBMigrateAIPro detects this pattern and maps it to PostgreSQL BOOLEAN, converting stored 0/1 values to false/true during the copy phase.

TEXT / BLOB Size Limits

MySQL TEXT variants (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) have different max sizes. PostgreSQL TEXT has no size limit, so all variants map to TEXT. MySQL BLOB variants map to BYTEA.

ENUM Handling

MySQL ENUM is mapped to a PostgreSQL CREATE TYPE … AS ENUM or a CHECK constraint depending on your config. Custom types are reusable across tables; CHECK constraints are simpler but per-column.

ON DUPLICATE KEY → ON CONFLICT

MySQL's INSERT … ON DUPLICATE KEY UPDATE becomes INSERT … ON CONFLICT (column) DO UPDATE SET … in PostgreSQL. DBMigrateAIPro rewrites these in application query exports and stored procedures.

GROUP BY Strictness

MySQL permits non-aggregated columns in SELECT that are not in GROUP BY. PostgreSQL (SQL standard compliant) does not. All such queries are flagged in the assessment report with suggested fixes.

Migration Steps

Connect

Enter MySQL host, port, database, and credentials. DBMigrateAIPro scans all tables, views, indexes, triggers, stored procedures, and functions. Binary log position is captured for CDC baseline.

Assess

Risk report covers: ENUM columns, TINYINT(1) usage, non-standard GROUP BY queries, zero-date values (0000-00-00), charset mismatches (latin1 vs utf8mb4), and stored procedures with MySQL-specific syntax.

Migrate

Parallel bulk copy over multiple worker threads. Foreign key checks are deferred until all tables are loaded. AUTO_INCREMENT sequences are reconciled against actual max values after bulk load completes.

Validate & Cut Over

Row-count and checksum validation per table. CDC then tails MySQL binlog events (INSERT, UPDATE, DELETE) and applies them to PostgreSQL in real time. When replication lag is below threshold, flip the connection string.

CDC Setup

MySQL Source — Enable Binlog

Add the following to my.cnf (Linux) or my.ini (Windows) and restart MySQL:

[mysqld]
log_bin            = ON
binlog_format      = ROW
binlog_row_image   = FULL
server_id          = 1          # must be unique
expire_logs_days   = 7          # retain 7 days of logs
max_binlog_size    = 500M

On RDS / Aurora: set binlog_format via Parameter Group — no restart needed.

PostgreSQL Target — Logical Replication

DBMigrateAIPro applies binlog events to PostgreSQL via a direct write connection — no PostgreSQL logical replication slots are required on the target. This keeps setup simple and works with managed services (RDS, Cloud SQL, Supabase).

# Grant required privileges on target PG
GRANT INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public
  TO migret_user;

ALTER DEFAULT PRIVILEGES
  IN SCHEMA public
  GRANT INSERT, UPDATE, DELETE
  ON TABLES TO migret_user;

Type Mapping Reference

MySQL Type	PostgreSQL Type
`INT`	`INTEGER`
`BIGINT`	`BIGINT`
`TINYINT(1)`	`BOOLEAN`
`FLOAT`	`REAL`
`DOUBLE`	`DOUBLE PRECISION`
`DECIMAL(p, s)`	`NUMERIC(p, s)`
`VARCHAR(n)`	`VARCHAR(n)`
`TEXT / MEDIUMTEXT / LONGTEXT`	`TEXT`
`BLOB / MEDIUMBLOB / LONGBLOB`	`BYTEA`
`DATETIME`	`TIMESTAMP`
`TIMESTAMP`	`TIMESTAMPTZ`
`JSON`	`JSONB`
`ENUM(…)`	`TEXT + CHECK or custom ENUM type`
`SET(…)`	`TEXT[] (array)`

Prerequisites

→MySQL 5.7+ or 8.0 (both tested; 8.0 recommended for full JSON and CTE support)
→Binary logging enabled: log_bin = ON in my.cnf / my.ini
→Binary log format set to ROW: binlog_format = ROW
→REPLICATION SLAVE (MySQL 5.7) or REPLICATION CLIENT (MySQL 8.0) privilege
→SELECT privilege on all source schemas
→pymysql or mysql-connector-python installed on the DBMigrateAIPro agent host
→Network access from the agent to MySQL port (default 3306)

Start your MySQL migration today

The full technical runbook covers binlog position tracking, conflict resolution, and charset normalization edge cases.

Read the docs View pricing