1. Test run
createdb -h pg01 -U postgres shop
pgloader mysql://migr:***@mysql01/shop postgresql://postgres:***@pg01/shop
pgloader creates schemas, tables, indexes and foreign keys, converts types, copies data and resets sequences. The summary table at the end shows rows read/imported and errors per table.
By default the MySQL database becomes a PostgreSQL schema with the same name (shop.orders). Use ALTER SCHEMA shop RENAME TO public patterns or the load file option below if you want public.
2. A load file for control
LOAD DATABASE
FROM mysql://migr:***@mysql01/shop
INTO postgresql://postgres:***@pg01/shop
WITH include drop, create tables, create indexes, reset sequences, foreign keys,
workers = 8, concurrency = 2, batch rows = 25000
CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
type date drop not null drop default using zero-dates-to-null,
type tinyint when (= precision 1) to boolean using tinyint-to-boolean
ALTER SCHEMA 'shop' RENAME TO 'public'
EXCLUDING TABLE NAMES MATCHING ~/^tmp_/, 'sessions';
pgloader shop.load
3. Type and behaviour differences
| MySQL | PostgreSQL | Watch out |
|---|---|---|
0000-00-00 dates | Not valid | Convert to NULL (zero-dates-to-null) and fix the app |
TINYINT(1) used as bool | boolean (with cast rule) | App code comparing to 1/0 |
UNSIGNED INT | bigint | Range |
AUTO_INCREMENT | Identity / sequence | Sequences reset by pgloader |
ENUM | Enum type or text + check | Changing enum values later |
Case-insensitive collation (utf8mb4_0900_ai_ci) | Case-sensitive by default | WHERE email = 'A@x' behaves differently. Use citext or lower() indexes |
Backticks `col` | Double quotes "col" | Identifiers: pgloader lower-cases names |
GROUP BY without all columns (loose mode) | Error | Fix queries |
LIMIT x, y | LIMIT y OFFSET x | App SQL |
IFNULL, NOW(), DATE_FORMAT | COALESCE, now(), to_char | App SQL |
INSERT ... ON DUPLICATE KEY UPDATE | INSERT ... ON CONFLICT ... DO UPDATE | App SQL |
Stored procedures, triggers and views are not converted reliably. Rewrite them in PL/pgSQL.
4. Validate
-- PostgreSQL row counts
SELECT schemaname, relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname; -- after ANALYZE
Compare with exact SELECT COUNT(*) on both sides for key tables, plus sums of amount columns. Run the application's test suite against PostgreSQL; ORM-based apps often need only the driver change, while hand-written SQL needs the fixes above.
5. Cut-over
| Downtime allowed | Approach |
|---|---|
| Hours | Freeze writes, run pgloader for the final copy, switch |
| Minutes | Initial pgloader load, then CDC (Debezium or AWS DMS from MySQL binlog) into PostgreSQL until switch |
Before switching:
-- confirm sequences are above max ids
SELECT setval(pg_get_serial_sequence('public.orders','id'), (SELECT max(id) FROM public.orders));
ANALYZE;
Warning
Case-insensitive comparisons are the most common post-migration bug: logins, email lookups and unique constraints that worked in MySQL behave differently. Test them explicitly.
Checklist
- Test run summary clean (no rejected rows)
- Cast rules for zero dates, booleans, unsigned
- App SQL differences fixed; routines rewritten
- Collation/case behaviour tested
- Cut-over method chosen and rehearsed