The short version
A MySQL to PostgreSQL migration, step by step: dump the schema and data with mysqldump, rewrite the DDL for PostgreSQL by hand (UNSIGNED, AUTO_INCREMENT, TINYINT(1), ENUM, DATETIME and ON UPDATE have no direct equivalent), turn zero dates into NULL, load the data, then reset every identity sequence with setval. Skip that last step and the first new row fails with a duplicate key error. Finish by comparing row counts and a couple of checksums on both sides.
Everything below I ran on MySQL 8.4.11 and PostgreSQL 16.14, on a small two-table schema built to contain the usual traps. The outputs are pasted as they came.
The MySQL schema, with every trap in it
Two tables, customers and orders. The column choices are the ones I meet in real MySQL databases:
CREATE TABLE `customers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`email` VARCHAR(190) NOT NULL,
`full name` VARCHAR(100) NOT NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`tier` ENUM('free','pro','enterprise') NOT NULL DEFAULT 'free',
`signup_date` DATE NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB;
CREATE TABLE `orders` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`customer_id` INT UNSIGNED NOT NULL,
`total` DECIMAL(10,2) NOT NULL,
`qty` SMALLINT UNSIGNED NOT NULL,
`note` TEXT,
`shipped_at` DATETIME NULL,
PRIMARY KEY (`id`),
KEY `ix_customer` (`customer_id`),
CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB;
The data has a name with an apostrophe, one with a rocket emoji, one with double quotes and a backslash, a note with a newline, café ☕, and one zero date in each table. The default SQL mode in MySQL 8.4 includes NO_ZERO_DATE and strict mode, so to get zero dates in I set sql_mode = 'NO_ENGINE_SUBSTITUTION' for the session, the way older servers ran for years. I also deleted order 4, so the ids have a gap like a real table.
Two behaviours to note before moving anything. The default collation is utf8mb4_0900_ai_ci, so string comparisons are case-insensitive, and the unique key on email enforces that:
mysql> INSERT INTO customers (email,`full name`) VALUES ('ANA@example.com','Ana again');
ERROR 1062 (23000) at line 1: Duplicate entry 'ANA@example.com' for key 'customers.uq_email'
mysql> SELECT id, email FROM customers WHERE email = 'ANA@EXAMPLE.COM';
id email
1 ana@example.com
And updated_at changes by itself: an ON UPDATE CURRENT_TIMESTAMP column is set to the current time when any other column in the row changes value. Updating tier on row 1 moved it from 2024-03-01 10:00:00 to 2026-10-25 20:47:33. PostgreSQL has neither behaviour unless you build it.
Step 1: dump MySQL with mysqldump
mysqldump -h 127.0.0.1 -P 13306 -u root -p \
--single-transaction --default-character-set=utf8mb4 \
mysql_to_postgresql_step_by_step > dump.sql
The client on my laptop is the MariaDB build of mysqldump (11.8.2), talking to a MySQL 8.4 server. That pairing put a /*M!999999\- enable the sandbox mode */ line at the top of the file, and with --compatible=ansi it also failed on tablespaces until I added --no-tablespaces:
mysqldump: Error: 'Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.FILES.EXTRA' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' when trying to dump tablespaces
--no-tablespaces drops the tablespace statements, which PostgreSQL could not use anyway. The dump also starts with SET TIME_ZONE='+00:00': that is --tz-utc, which my dump got without my asking for it, and it means every TIMESTAMP value in the file is written in UTC. Remember that for the load.
Why the raw dump does not load into PostgreSQL
I fed the file to PostgreSQL as it was, then fixed one error at a time to find the next. The loader is a ten-line psycopg2 script that sends the file in one transaction, the same as psql -1 -v ON_ERROR_STOP=1 -f dump.sql would (psql -1). Query results further down come from a similar script: a => line echoes the statement, then the result follows. The errors, in the order they came:
ERROR: syntax error at or near "`"
LINE 23: DROP TABLE IF EXISTS `customers`;
Backtick identifiers. In MySQL 8.4 the only value --compatible accepts is ansi, which switches them to double quotes. With that:
ERROR: syntax error at or near "unsigned"
LINE 12: "id" int unsigned NOT NULL AUTO_INCREMENT,
ERROR: syntax error at or near "ON"
LINE 19: ..._at" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE ...
ERROR: syntax error at or near "KEY"
LINE 21: UNIQUE KEY "uq_email" ("email")
ERROR: syntax error at or near "Neil"
LINE 29: (2,'bo@example.com','Bo O\'Neil',0,'free','0000-00-00','2024...
ERROR: type "enum" does not exist
LINE 16: "tier" enum('free','pro','enterprise') NOT NULL DEFAULT 'f...
ERROR: type "datetime" does not exist
LINE 18: "created_at" datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
ERROR: unrecognized configuration parameter "autocommit"
ERROR: date/time field value out of range: "0000-00-00"
LINE 28: (2,'bo@example.com','Bo O''Neil',0,'free','0000-00-00','2024...
AUTO_INCREMENT went out in the same edit as UNSIGNED, so it never got an error of its own. Nine errors from a two-table dump, and the last one is a data problem that no amount of DDL editing fixes. 'Bo O\'Neil' fails because MySQL treats backslash as an escape character in strings while PostgreSQL, with standard_conforming_strings on by default since 9.1, treats it as an ordinary character. Sed-editing a full dump this way is a dead end. Write the schema yourself and load only the data.
Step 2: write the PostgreSQL schema by hand
This is the mapping I used, and why:
| MySQL | PostgreSQL | Reason |
|---|---|---|
INT UNSIGNED AUTO_INCREMENT |
bigint GENERATED BY DEFAULT AS IDENTITY |
unsigned INT goes to 4294967295, past integer |
BIGINT UNSIGNED |
bigint, or numeric(20) if values pass 2^63 |
unsigned range ends at 18446744073709551615 |
SMALLINT UNSIGNED |
integer plus CHECK (qty >= 0) |
65000 does not fit smallint |
TINYINT(1) |
boolean |
MySQL's BOOL is a synonym for TINYINT(1) |
ENUM(...) |
text with a CHECK |
or a type made with CREATE TYPE |
DATETIME |
timestamp |
no zone conversion in MySQL |
TIMESTAMP |
timestamptz |
MySQL stores TIMESTAMP as UTC and converts on read |
ON UPDATE CURRENT_TIMESTAMP |
BEFORE UPDATE trigger | no column option for it |
| case-insensitive unique key | unique index on lower(email) |
CREATE TABLE customers (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
email varchar(190) NOT NULL,
full_name varchar(100) NOT NULL, -- was `full name`
is_active smallint NOT NULL DEFAULT 1, -- boolean after the load
tier text NOT NULL DEFAULT 'free'
CHECK (tier IN ('free', 'pro', 'enterprise')),
signup_date date,
created_at timestamp NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX uq_email ON customers (lower(email));
CREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (id),
total numeric(10,2) NOT NULL,
qty integer NOT NULL CHECK (qty >= 0),
note text,
shipped_at timestamp
);
CREATE INDEX ix_customer ON orders (customer_id);
CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER customers_touch BEFORE UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
BY DEFAULT matters: with it, a value you supply takes precedence over the sequence, so the old ids load unchanged. A BEFORE trigger that assigns to NEW and returns it replaces ON UPDATE. Mine stamps every UPDATE, even one that changes nothing, where MySQL leaves the column alone when no value actually changed. The lower(email) index is the PostgreSQL docs' own example of a constraint an expression index can enforce.
is_active stays smallint for the load, because the dump writes 1 and 0 and PostgreSQL refuses an integer in a boolean column:
ERROR: column "is_active" is of type boolean but expression is of type integer
LINE 1: INSERT INTO t VALUES (1)
^
HINT: You will need to rewrite or cast the expression.
Step 3: dump the data only and load it
mysqldump -h 127.0.0.1 -P 13306 -u root -p \
--no-create-info --compatible=ansi --no-tablespaces \
--skip-add-locks --skip-comments --single-transaction \
--default-character-set=utf8mb4 mysql_to_postgresql_step_by_step > data.sql
Then three edits: drop the /*!...*/ lines and the set autocommit=0; and commit; lines, replace zero dates with NULL, and put three settings on top.
grep -v '^/\*' data.sql | grep -v -e '^set autocommit' -e '^commit' > data_pg.sql
sed -i '' -e "s/'0000-00-00 00:00:00'/NULL/g" -e "s/'0000-00-00'/NULL/g" data_pg.sql
SET standard_conforming_strings = off;
SET escape_string_warning = off;
SET timezone = 'UTC';
With standard_conforming_strings off, PostgreSQL reads \', \\ and \n in ordinary literals the way MySQL wrote them, and escape_string_warning only silences the warning that would follow every backslash. timezone = 'UTC' matches the --tz-utc dump, so the timestamptz values arrive as the same instants. The sed on zero dates is blunt: a text column that contains the string '0000-00-00' would be hit too, so grep for it first. The zero dates article has the options other than NULL.
One catch I hit: my first loader sent the whole file as one string, and the load still failed on 'Bo O\'Neil', because PostgreSQL parses the full string before the SET runs. Sent statement by statement, as psql does, it loaded:
loaded OK, 5 statements
Then the boolean, dropping the default first as the ALTER TABLE page advises when the old default cannot be cast:
ALTER TABLE customers
ALTER COLUMN is_active DROP DEFAULT,
ALTER COLUMN is_active TYPE boolean USING is_active <> 0,
ALTER COLUMN is_active SET DEFAULT true;
Step 4: fix the sequences with setval
The ids came from the dump, so the identity sequences never moved. The first ordinary insert:
=> INSERT INTO orders (customer_id, total, qty) VALUES (2, 12.50, 1) RETURNING id
ERROR: duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(1) already exists.
Each retry burns a sequence value and fails again on 2, then 3. The fix is one setval per identity column. pg_get_serial_sequence returns the sequence behind an identity column, and the two-argument setval makes the next nextval return the value plus one:
=> SELECT setval(pg_get_serial_sequence('customers', 'id'), (SELECT max(id) FROM customers))
setval
4
=> SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders))
setval
5
=> INSERT INTO orders (customer_id, total, qty) VALUES (2, 12.50, 1) RETURNING id
id
6
On a real database, write one line for every identity column, not only the big tables. The same error after an import has its own article: duplicate key violates unique constraint after import.
Step 5: check what changed on the way
The traps, tested on the PostgreSQL side:
=> SELECT count(*) FROM customers WHERE email = 'ANA@EXAMPLE.COM'
count
0
=> INSERT INTO customers (email, full_name) VALUES ('ANA@example.com', 'Ana again')
ERROR: duplicate key value violates unique constraint "uq_email"
DETAIL: Key (lower(email::text))=(ana@example.com) already exists.
=> INSERT INTO customers (email, full_name, tier) VALUES ('x@example.com', 'X', 'gold')
ERROR: new row for relation "customers" violates check constraint "customers_tier_check"
DETAIL: Failing row contains (6, x@example.com, X, t, gold, null, 2026-10-25 20:50:23.718506, 2026-10-25 20:50:23.718506+00).
=> SELECT length(full_name), octet_length(full_name) FROM customers WHERE id = 3
length | octet_length
10 | 13
The unique index behaves like MySQL's, but the plain WHERE email = ... lookup now returns nothing. Every query in the application that relied on the case-insensitive collation needs lower() on both sides, or a different design. The emoji came through as one character of four bytes. The backslash, quotes and newline came back exactly as they went in.
Step 6: verify with row counts and checksums
Fresh load, then the same queries on both servers. PostgreSQL:
t | n | max_id
customers | 4 | 4
orders | 4 | 5
total | qty | shipped
4200119.59 | 65006 | 1
md5
52ca733a2a3875961270ca8bd8c72cd2
MySQL:
t n max_id
customers 4 4
orders 4 5
total qty shipped zero_dates
4200119.59 65006 2 1
md5
52ca733a2a3875961270ca8bd8c72cd2
Counts, sums and the hash of every email and name match. shipped differs by exactly the one zero date that became NULL, which is the change I chose. Write that kind of difference down before you start, or it looks like data loss in the sign-off meeting. One more MySQL surprise while checking: in strict mode the server refuses to compare against the zero date it stores, shipped_at = '0000-00-00 00:00:00' gave ERROR 1525 (HY000) at line 1: Incorrect DATETIME value: '0000-00-00 00:00:00', so I counted with CAST(shipped_at AS CHAR) LIKE '0000%'. The full method is in how to validate data after a migration.
Is there a free MySQL to PostgreSQL converter?
Two I can speak to. pgloader reads a live MySQL server and writes to PostgreSQL in one command (pgloader mysql://user@host/db pgsql://user@host/db). Its documented defaults cover most of the list above: tinyint(1) to boolean, unsigned types widened, ENUM to a created type, zero dates to NULL, and sequences reset after the load. It also maps both DATETIME and TIMESTAMP to timestamptz, which I would override for DATETIME. I did not run it for this article.
Our MySQL dump to PostgreSQL converter takes the .sql file instead of a server connection. I ran the same dump.sql from Step 1 through its reader and PostgreSQL writer, with no type changes, and it reported three warnings:
UNSIGNED_IDENTITY_NARROWED | orders.id: BIGINT UNSIGNED AUTO_INCREMENT became a signed BIGINT identity (largest id 5; new ids can go up to 9223372036854775807)
ZERO_DATE_TO_NULL | customers.signup_date: 1 zero-date value ('0000-00-00') converted to NULL
ZERO_DATE_TO_NULL | orders.shipped_at: 1 zero-date value ('0000-00-00') converted to NULL
The orders part of the output, trimmed:
CREATE TABLE "orders" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"customer_id" BIGINT NOT NULL,
"total" NUMERIC(10,2) NOT NULL,
"qty" INTEGER NOT NULL,
"note" TEXT,
"shipped_at" TIMESTAMP,
PRIMARY KEY ("id")
);
INSERT INTO "orders" ("id", "customer_id", "total", "qty", "note", "shipped_at") VALUES
(1, 1, 19.99, 1, 'first order', TIMESTAMP '2024-03-02 12:00:00'),
(2, 1, 4200000.50, 65000, NULL, NULL),
...
SELECT setval(pg_get_serial_sequence('"orders"', 'id'), COALESCE(MAX("id"), 1), MAX("id") IS NOT NULL) FROM "orders";
The unsigned columns get the next signed type up, as in the Step 2 table (SMALLINT UNSIGNED to INTEGER, INT UNSIGNED to BIGINT), the strings are re-escaped for PostgreSQL, and each identity column gets the setval from Step 4 right after its rows. Loaded into an empty PostgreSQL 16 database in one transaction, then the same insert that failed in Step 4:
loaded OK
=> INSERT INTO orders (customer_id, total, qty) VALUES (2, 12.50, 1) RETURNING id
id
6
=> SELECT id, signup_date FROM customers WHERE id = 2
id | signup_date
2 | null
=> SELECT id, qty, shipped_at FROM orders WHERE id = 2
id | qty | shipped_at
2 | 65000 | null
What it does not do: is_active stays SMALLINT, tier is plain TEXT with no CHECK, updated_at is TIMESTAMP rather than timestamptz (holding the UTC values --tz-utc wrote), and full name becomes full_name. The output carries tables, primary keys and data, with no unique index, foreign key, defaults or trigger. Use it for the data and a first draft of the DDL, then add the rest of Step 2 yourself.
If PostgreSQL is still an open question, MySQL vs PostgreSQL as a migration target covers that choice.
Sources
- MySQL 8.4: mysqldump
- MySQL 8.4: Server SQL modes
- MySQL 8.4: Case sensitivity in string searches
- MySQL 8.4: TIMESTAMP and DATETIME initialization
- MySQL 8.4: DATE, DATETIME and TIMESTAMP
- MySQL 8.4: Numeric type syntax
- MySQL 8.4: String literals
- PostgreSQL: CREATE TABLE
- PostgreSQL: ALTER TABLE
- PostgreSQL: Sequence manipulation functions
- PostgreSQL: System information functions
- PostgreSQL: Version and platform compatibility settings
- PostgreSQL: Indexes on expressions
- PostgreSQL: Trigger functions in PL/pgSQL
- PostgreSQL: psql
- pgloader: Migrating a MySQL database to PostgreSQL