pgloader for MySQL to PostgreSQL Migration: Field Notes

Every MySQL to PostgreSQL migration I have been near eventually reaches the same question: do we write our own extract-and-load pipeline, or do we let pgloader do it? My answer after several rounds of both is that pgloader should almost always do the first pass. It connects to MySQL, reads the information schema, creates matching PostgreSQL tables, streams the data over in parallel, then builds indexes and foreign keys at the end. What would take a team weeks to script takes an afternoon to configure.

That convenience comes with sharp edges, and the edges are predictable. pgloader is excellent at tables, rows, indexes, constraints, and type conversion. It does nothing for stored procedures, triggers, or events, and it can be surprised by the charset archaeology that accumulates in a long-lived MySQL database. This post is the set of notes I wish someone had handed me before my first pgloader run: what the defaults do, when to override them, and how to verify the result before anyone calls the migration done.

Everything here assumes MySQL 8.x with InnoDB on the source and PostgreSQL 16 or 17 on the target. Older MySQL versions mostly behave the same for migration purposes, with more charset baggage.

What pgloader actually does well

The core loop is solid. pgloader discovers tables, translates column types using a cast rule table, creates the schema, copies data using PostgreSQL COPY for speed, and defers index and foreign key creation until after the rows land. Deferring the indexes matters: loading into an indexed table is dramatically slower, and pgloader builds indexes in parallel afterward instead.

It also handles the fiddly parts people forget. AUTO_INCREMENT columns become PostgreSQL sequences or identity-backed columns, and pgloader sets the sequence values to continue past the highest migrated id. MySQL ENUM columns become real PostgreSQL enum types, created per column. Unsigned integer types get widened so the values still fit, because PostgreSQL has no unsigned integers: an unsigned INT holds values a signed integer cannot, so it needs to land in bigint.

For a schema that is mostly tables and indexes, a default pgloader run gets you a startlingly usable database. The work is in the exceptions.

The default casts, and the ones I always review

pgloader ships with sensible default cast rules, but three of them deserve a deliberate decision rather than a shrug.

• tinyint(1) to boolean. The default treats tinyint(1) as boolean, which matches how most ORMs use it. But plenty of real schemas store small enums or counters in tinyint(1). If any column stores a 2, the boolean cast is wrong. Audit these columns before the run, not after.
• datetime and timestamp handling. MySQL TIMESTAMP is stored in UTC and rendered in the session time zone; DATETIME is a wall-clock value with no zone at all. Deciding which PostgreSQL type each column becomes, timestamp or timestamptz, is a data-model decision pgloader cannot make for you. The default cast to timestamptz is right for TIMESTAMP columns and a judgment call for DATETIME.
• Zero dates. Legacy MySQL data often contains 0000-00-00 values that PostgreSQL simply will not accept. pgloader has transformation functions to turn them into NULL, which usually also means dropping a NOT NULL constraint on the way through.

Custom rules go in the load command file. A typical block looks like this:

LOAD DATABASE
  FROM mysql://migrator@10.0.0.5/appdb
  INTO postgresql://migrator@10.0.0.9/appdb
CAST
  type datetime drop default drop not null
    using zero-dates-to-null,
  type date drop default drop not null
    using zero-dates-to-null,
  column orders.flags to smallint drop typemod
ALTER SCHEMA 'appdb' RENAME TO 'public';

The per-column override in that example is the pattern I use most: keep the global defaults, then pin down the specific columns where the default guess is wrong.

What it will not migrate

pgloader moves the relational core and stops there. Plan separate work for everything in this list.

• Stored procedures and functions. MySQL procedural SQL does not translate mechanically to PL/pgSQL. Every routine is a manual port or a candidate for moving into the application.
• Triggers. Same story. PostgreSQL triggers call trigger functions, and the body needs rewriting.
• Views. pgloader does not carry ordinary view definitions across. It has a MATERIALIZE VIEWS clause, but that loads the view output as a table, which is a different thing. Expect to re-create views by hand and fix dialect issues in each one.
• Events. MySQL scheduled events have no PostgreSQL equivalent in core. They become cron jobs, pg_cron entries, or application schedulers.
• Users, grants, and replication topology. All environment work, all manual.

None of this is a criticism. It is scope. The mistake I see is teams treating the successful pgloader run as the migration being 90% done, when the procedural and operational remainder is where the calendar time goes.

Charsets: where old databases bite

PostgreSQL databases are typically UTF-8 and strict about it. MySQL has spent two decades being flexible, and long-lived databases carry the scars. The classic traps: the MySQL charset named utf8 is the three-byte utf8mb3, so genuinely four-byte characters like most emoji only exist in utf8mb4 columns; the charset named latin1 is really closer to Windows cp1252; and permissive historical configurations let applications write bytes that do not match the declared column charset at all.

pgloader decodes using the declared charset per column, which is correct behavior, and exactly why mis-declared data surfaces during the load. When a table has latin1 columns that actually contain UTF-8 bytes written by a careless client, the load produces mojibake or rejected rows. Before the real migration, run a full-volume rehearsal load and diff string samples from both sides. Charset problems found at rehearsal are a cleanup task; found after cutover, they are an incident.

Verify the load like you do not trust it

After the load finishes, pgloader prints a summary table with per-table row counts and error counts. Read it, but do not stop there. My minimum verification pass has four steps. First, compare row counts independently on both sides for every table, from a quiescent source. Second, confirm sequences are ahead of the data, because a sequence left behind the maximum id causes duplicate key errors on the first insert:

SELECT c.relname AS table_name,
       a.attname AS column_name,
       pg_get_serial_sequence(c.relname, a.attname) AS seq
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relkind = 'r'
  AND pg_get_serial_sequence(c.relname, a.attname) IS NOT NULL;

-- then for each pair:
SELECT last_value FROM orders_id_seq;
SELECT max(id) FROM orders;

Third, check that constraints actually exist. pgloader creates indexes and foreign keys after the copy, and failures at that stage can leave a table loaded but unconstrained. Compare index and constraint counts against the source schema. Fourth, spot-check the data types that went through interesting casts, especially booleans, timestamps near daylight-saving boundaries, and any column with a custom rule:

SELECT conrelid::regclass AS table_name,
       count(*) FILTER (WHERE contype = 'f') AS fkeys,
       count(*) FILTER (WHERE contype = 'p') AS pkeys,
       count(*) FILTER (WHERE contype = 'u') AS uniques
FROM pg_constraint
GROUP BY conrelid
ORDER BY table_name::text;

For the full pre-cutover pass, including query-shape and application-behavior checks that go beyond the data itself, I keep a longer list in the PostgreSQL migration review checklist.

Treat pgloader as the first mile

The honest framing: pgloader compresses the schema-and-data phase of a MySQL to PostgreSQL migration from weeks to days. It does not shorten the application-porting phase, the procedural-code phase, or the operational-readiness phase. Teams that plan around that split do well. Teams that demo the pgloader result to leadership and commit to a cutover date the same week do not.

It is also worth saying that a one-shot pgloader load implies downtime equal to the load duration, plus verification. For small databases that is a maintenance window. For large ones you will want a continuous replication approach layered on top, which is a different architecture with its own checklist. And once you land, the operational model changes too: PostgreSQL has its own maintenance rhythms, autovacuum being the big one, that have no MySQL muscle-memory equivalent.

After you land: baseline the new PostgreSQL from day one

The riskiest week of a migration is the first week on the new engine, because nobody knows yet what normal looks like. Queries that were fine under MySQL's optimizer can behave differently under PostgreSQL's, and you want to catch that from evidence, not from support tickets.

This is where MonPG fits. MonPG monitors PostgreSQL only, and that focus is exactly what a fresh migration needs: pg_stat_statements history from the first hour, wait events, lock chains, autovacuum behavior on the newly loaded tables, and index usage that tells you which migrated indexes actually earn their keep. Stand it up before cutover so the baseline starts at zero, then let the first weeks of history turn "is this normal for us now?" into a question with an answer. The PostgreSQL monitoring guide covers the signals worth watching from day one.