MySQL → PostgreSQL
Migrate from MySQL to PostgreSQL — retaining all data types, triggers, stored procedures, and optionally streaming live changes via binlog CDC.
Why migrate from MySQL to PostgreSQL?
- ✓Full ACID compliance and true transactional DDL
- ✓Advanced types: JSONB, ARRAY, UUID, HSTORE, range types
- ✓Better parallel query execution and query planner
- ✓Rich extension ecosystem (PostGIS, pg_trgm, pgvector)
- ✓Proper window functions, CTEs, and materialised views
- ✓Reduced replication lag with logical decoding
Prerequisites
- →MySQL 5.7 or 8.x (source)
- →SHOW MASTER LOGS privilege for binlog CDC
- →binlog_format = ROW in my.cnf (required for CDC)
- →PostgreSQL 13+ target
Run the Assessment Report
Click Assess Schema to generate the pre-migration report. For MySQL migrations, pay attention to: ENUM/SET columns (converted to constrained TEXT or TEXT[]), TINYINT(1) auto-mapped to BOOLEAN, tables using MyISAM engine (migrated to InnoDB-equivalent in PostgreSQL), and triggers with complex DELIMITER syntax.
Schema Conversion
DBMigrateAIPro reads INFORMATION_SCHEMA and generates CREATE TABLE, CREATE INDEX, and CONSTRAINT DDL for PostgreSQL. AUTO_INCREMENT columns become SERIAL or GENERATED ALWAYS AS IDENTITY. ENUM types are converted to TEXT with CHECK constraints — or optionally to a PostgreSQL ENUM type (set in migration settings).
Bulk Data Migration
Tables are streamed via MySQL's protocol using parameterised SELECT and loaded into PostgreSQL via COPY. FK constraints are deferred during load. Default parallel workers: 8. Set parallel_workers in migration settings to increase throughput.
Stored Procedures & Triggers
MySQL stored procedures and triggers are transpiled to PL/pgSQL. DELIMITER declarations are stripped. IF NOT EXISTS is preserved. Procedures that use MySQL-specific functions (LAST_INSERT_ID, FOUND_ROWS) are flagged for manual review.
Enable Binlog CDC (Optional)
After bulk migration completes, enable binlog streaming in the CDC tab. DBMigrateAIPro reads the MySQL binary log (format: ROW) and replicates INSERT/UPDATE/DELETE to PostgreSQL in near real-time. Typical lag: < 3 seconds.
Validate & Cutover
Run the Comparison Report to validate row counts and optional checksum sampling. When satisfied, update your application connection string to PostgreSQL, stop CDC, and run ANALYZE on the target to refresh query statistics.
Type Mapping Reference
| MySQL Type | PostgreSQL Type |
|---|---|
INT / INTEGER | INTEGER |
BIGINT | BIGINT |
TINYINT(1) | BOOLEAN |
TINYINT(n) | SMALLINT |
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 |
DATE | DATE |
TIME | TIME |
JSON | JSONB |
ENUM(...) | TEXT with CHECK constraint |
SET(...) | TEXT[] |
AUTO_INCREMENT | SERIAL / GENERATED ALWAYS AS IDENTITY |
Common Issues
ERROR 1227Binary log not enabled
Enable binary logging in my.cnf: log_bin = /var/log/mysql/mysql-bin.log and binlog_format = ROW.
DataError: zero dateMySQL '0000-00-00' date values
Set sql_mode to exclude 'NO_ZERO_DATE'. DBMigrateAIPro converts zero dates to NULL by default.
FK constraint violationForeign keys fail during bulk load
DBMigrateAIPro defers FK constraints during load and re-enables after. Verify with: SELECT conname FROM pg_constraint;
CHARACTER SET mismatchUTF8MB4 vs UTF8 encoding
MySQL UTF8MB4 maps to PostgreSQL UTF8. Set client_encoding = UTF8 on the PostgreSQL target.
Trigger syntax errorMySQL DELIMITER syntax in triggers
DBMigrateAIPro strips DELIMITER declarations automatically. Review triggers flagged as COMPLEX in the assessment report.