How to Migrate a Database from MySQL to PostgreSQL: A Step-by-Step Guide with pgloader

Deciding to migrate MySQL to PostgreSQL is the easy part. The hard part is doing it without a five hour outage, without breaking half your application queries, and without discovering three weeks later that 40,000 rows quietly turned into NULL because of a zero date.

This guide is the checklist we actually use on client migrations at Coding4. It covers schema conversion, the data type mismatches that bite, running pgloader, verifying the data properly (row counts alone are not enough), the application code changes you must make after the switch, and a rollback plan for the moment the cutover goes sideways.

Before you touch anything: the pre-migration audit

Most failed migrations fail during planning, not during the data copy. Spend a day on this list before you install a single tool.

Inventory the schema. Count tables, views, stored procedures, triggers, events and foreign keys. pgloader moves tables, data and indexes. It does not convert stored procedures, triggers or views. Those are manual rewrites.
Measure the data. Total size, largest tables, and the number of rows in each. This drives your maintenance window estimate.
List every application that touches the database. Cron jobs, BI tools, reporting scripts and that one legacy PHP page nobody owns all count.
Find the raw SQL. ORMs abstract a lot, but raw queries with INSERT IGNORE, ON DUPLICATE KEY UPDATE or backticks will break instantly.
Check your MySQL version and charset. Tables still on utf8 (3 byte) instead of utf8mb4 may hold mangled emoji. Fix that before migrating, not after.
Decide the target version. PostgreSQL 17 and 18 are both solid production choices in 2026. Pick one and standardise your dev, staging and production on it.

Quick schema inventory queries

-- Table sizes and approximate row counts
SELECT table_name,
       table_rows,
       ROUND((data_length + index_length) / 1024 / 1024) AS size_mb,
       engine
FROM information_schema.tables
WHERE table_schema = 'appdb'
ORDER BY (data_length + index_length) DESC;

-- Objects pgloader will NOT migrate for you
SELECT routine_name, routine_type FROM information_schema.routines WHERE routine_schema = 'appdb';
SELECT trigger_name, event_object_table FROM information_schema.triggers WHERE trigger_schema = 'appdb';
SELECT table_name FROM information_schema.views WHERE table_schema = 'appdb';

-- Columns that are likely to cause type trouble
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'appdb'
  AND (column_type LIKE '%unsigned%'
       OR column_type LIKE 'enum%'
       OR column_type LIKE 'set%'
       OR column_type LIKE 'tinyint(1)%'
       OR data_type IN ('year','bit','datetime','timestamp'));

Step 1: Understand the data type mismatches

This is where silent corruption happens. pgloader has sensible defaults, but defaults are not always what your application expects. Review this mapping table and decide explicitly for each questionable column.

MySQL type	PostgreSQL target	What to watch out for
`TINYINT(1)`	`boolean`	pgloader converts it by default. If you store values other than 0 and 1 in it, you will lose data. Check first.
`INT UNSIGNED`	`bigint`	PostgreSQL has no unsigned types. Values above 2,147,483,647 overflow an `integer`. Promote to `bigint`.
`DATETIME`	`timestamptz` or `timestamp`	Decide on timezone semantics before loading. Mixing them later is painful.
`0000-00-00` dates	`NULL`	Invalid in PostgreSQL. Use the `zero-dates-to-null` transform and make sure app code handles NULL.
`ENUM`	native `enum` type or `text` + CHECK	pgloader creates a native enum. Adding values later is a DDL change, so `text` plus a check constraint is often more flexible.
`SET`	`text[]` or junction table	No direct equivalent. Plan a manual transformation.
`JSON`	`jsonb`	Key order is not preserved in `jsonb`. If anything depends on key order, use `json`.
`DOUBLE` for money	`numeric(18,2)`	Good moment to fix a legacy mistake. Rounding will change, so validate financial totals.
`YEAR`	`smallint`	No YEAR type in PostgreSQL.
`BIT(1)`	`boolean`	Check driver behaviour, some ORMs return bytes.
`TEXT`, `VARCHAR(n)`	`text`	In PostgreSQL `text` has no performance penalty. Keep length limits only where they are a real business rule.
`AUTO_INCREMENT`	identity column + sequence	Sequences must be reset after load or your first insert throws a duplicate key error.

The case sensitivity trap

MySQL string comparison is usually case insensitive (utf8mb4_general_ci). PostgreSQL is case sensitive. A login lookup like WHERE email = '[email protected]' that worked in MySQL will return zero rows in PostgreSQL. Options:

Normalise values at write time (lowercase emails and usernames).
Use citext for the affected columns: CREATE EXTENSION citext;
Use functional indexes: CREATE INDEX ON users (lower(email)); and query with lower(email) = lower($1).

Also note that unquoted identifiers are folded to lowercase in PostgreSQL. A MySQL table named UserProfiles becomes userprofiles after pgloader unless you force quoting. Consistent snake_case naming is the safest outcome.

Step 2: Install pgloader

pgloader is a single command line tool that reads directly from MySQL over the wire and writes into PostgreSQL using COPY. No intermediate dump file needed.

# Ubuntu / Debian
sudo apt update && sudo apt install -y pgloader

# macOS
brew install pgloader

# Docker (recommended for reproducibility and recent versions)
docker run --rm -it \
  -v "$PWD":/work -w /work \
  ghcr.io/dimitri/pgloader:latest pgloader --version

Tip for Windows users: run pgloader inside WSL2 or Docker. Native Windows builds are not maintained and you will waste hours.

Step 3: Prepare the PostgreSQL target

CREATE ROLE app_owner LOGIN PASSWORD 'strong-password';
CREATE DATABASE appdb OWNER app_owner ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

\c appdb
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

For the load itself, temporarily give the instance more room. These are session or restart level settings you revert afterwards:

-- postgresql.conf tweaks for the migration window only
maintenance_work_mem = '2GB'
max_wal_size = '16GB'
checkpoint_timeout = '30min'
autovacuum = off        -- turn it back ON straight after the load
synchronous_commit = off

On the MySQL side, create a read-only user for pgloader and make sure it can read from a replica rather than the primary if your dataset is large.

Step 4: Write the pgloader load file

Do not use the one line pgloader mysql://... postgresql://... form for a production migration. A load file is repeatable, reviewable and versionable in Git.

LOAD DATABASE
     FROM     mysql://migrator:[email protected]/appdb
     INTO postgresql://app_owner:[email protected]/appdb

 WITH include drop,
      create tables,
      create indexes,
      reset sequences,
      foreign keys,
      workers = 8,
      concurrency = 2,
      multiple readers per thread,
      rows per range = 50000,
      batch rows = 25000,
      batch size = 32MB,
      prefetch rows = 25000

  SET MySQL PARAMETERS
      net_read_timeout  = '600',
      net_write_timeout = '600'

  SET work_mem to '256MB',
      maintenance_work_mem to '2GB'

 CAST type datetime  to timestamptz drop default drop not null using zero-dates-to-null,
      type timestamp 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 (= 1 precision) to boolean using tinyint-to-boolean,
      type int   when unsigned to bigint drop typemod,
      type year  to smallint drop typemod,
      column users.email to citext,
      column orders.total to numeric using float-to-string

 EXCLUDING TABLE NAMES MATCHING 'schema_migrations_lock', ~/^tmp_/, ~/_archive$/

 ALTER SCHEMA 'appdb' RENAME TO 'public'

 BEFORE LOAD DO
   $$ CREATE SCHEMA IF NOT EXISTS public; $$

 AFTER LOAD DO
   $$ ANALYZE; $$;

What each key option really does

include drop: drops target tables first. Great for repeated test runs, dangerous on a live target. Remove it for the final cutover run if you pre-created anything.
reset sequences: sets each sequence to max(id) after the load. Without it your first insert fails.
ALTER SCHEMA … RENAME TO ‘public’: by default pgloader creates a schema named after the MySQL database. Most applications expect public.
EXCLUDING TABLE NAMES MATCHING: skip huge archive or log tables and migrate them separately after cutover. This alone can cut your maintenance window in half.
workers / concurrency: start with workers = number of CPU cores, concurrency = 2. Higher is not always faster because MySQL becomes the bottleneck.

Step 5: Do a rehearsal run (twice)

Never run pgloader against production for the first time on cutover night. Run it at least twice against a full copy of production data on a staging server.

pgloader --verbose --logfile=/var/log/pgloader-run1.log migrate.load

# Docker version
docker run --rm -v "$PWD":/work -w /work \
  ghcr.io/dimitri/pgloader:latest \
  pgloader --verbose --logfile=/work/pgloader-run1.log /work/migrate.load

At the end pgloader prints a summary table with rows read, rows imported and errors per table. Read every line of it. A table that reports fewer imported rows than read rows is your silent corruption warning, and rejected rows land in /tmp/pgloader/ as .dat and .log files. postgresql.org makes the same point with more data.

Record the total wall clock time. That number, plus 50 percent buffer, is your maintenance window estimate.

Step 6: Verify the data (row counts are only step one)

6.1 Compare row counts table by table

# counts.sh
DBM="mysql -N -B -h 10.0.0.10 -u migrator -psecret appdb"
DBP="psql -t -A -h 10.0.0.20 -U app_owner appdb"

for t in $($DBM -e "SELECT table_name FROM information_schema.tables WHERE table_schema='appdb' AND table_type='BASE TABLE'"); do
  m=$($DBM -e "SELECT COUNT(*) FROM \`$t\`")
  p=$($DBP -c "SELECT COUNT(*) FROM public.$t" 2>/dev/null || echo MISSING)
  if [ "$m" != "$p" ]; then
    echo "MISMATCH $t : mysql=$m postgres=$p"
  else
    echo "OK       $t : $m"
  fi
done

6.2 Compare aggregates, not just counts

Equal row counts do not prove equal data. A truncated string or a nulled date keeps the count identical. Run aggregate checks on the columns that matter:

-- Run the equivalent on both sides and compare
SELECT COUNT(*)             AS rows,
       SUM(total)           AS sum_total,
       MIN(created_at)      AS min_created,
       MAX(created_at)      AS max_created,
       COUNT(*) FILTER (WHERE created_at IS NULL) AS null_dates
FROM orders;

-- MySQL equivalent for the FILTER clause
SELECT COUNT(*), SUM(total), MIN(created_at), MAX(created_at),
       SUM(created_at IS NULL) FROM orders;

6.3 Spot check the ugly columns

Rows with emoji or non Latin characters, compared byte for byte.
Boolean columns: SELECT flag, COUNT(*) FROM t GROUP BY flag; on both sides.
The oldest and newest 20 rows of your busiest table.
Any column that had 0000-00-00 values, now expected to be NULL.
Decimal totals in financial tables, to the cent.

6.4 Verify structure, not just data

-- Are all foreign keys present?
SELECT conrelid::regclass AS table, conname, contype
FROM pg_constraint WHERE contype = 'f' ORDER BY 1;

-- Are all sequences correctly positioned?
SELECT sequencename, last_value FROM pg_sequences WHERE schemaname = 'public';

-- Any table left without a primary key?
SELECT c.relname FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
  AND NOT EXISTS (SELECT 1 FROM pg_constraint x WHERE x.conrelid = c.oid AND x.contype = 'p');

Then, always:

VACUUM ANALYZE;   -- planner statistics, non negotiable before benchmarking

Step 7: Fix the application code

pgloader gets you a database. It does not get you a working application. Here are the changes that come up on virtually every project.

MySQL	PostgreSQL
`table`.`column` (backticks)	`"table"."column"` or no quotes at all
`LIMIT 10, 20`	`LIMIT 20 OFFSET 10`
`INSERT IGNORE`	`INSERT ... ON CONFLICT DO NOTHING`
`ON DUPLICATE KEY UPDATE`	`ON CONFLICT (col) DO UPDATE SET ...`
`REPLACE INTO`	Upsert with `ON CONFLICT`, or DELETE then INSERT in a transaction
`IFNULL(a, b)`	`COALESCE(a, b)`
`GROUP_CONCAT(x)`	`string_agg(x, ',')`
`DATE_FORMAT(d, '%Y-%m')`	`to_char(d, 'YYYY-MM')`
`DATE_ADD(d, INTERVAL 7 DAY)`	`d + INTERVAL '7 days'`
`UNIX_TIMESTAMP()`	`EXTRACT(EPOCH FROM now())`
`RAND()`	`random()`
`LAST_INSERT_ID()`	`INSERT ... RETURNING id`
`FIND_IN_SET(x, list)`	`x = ANY(string_to_array(list, ','))`
`LIKE` (case insensitive by default)	`ILIKE`, or `citext`, or `lower()` index
`CONCAT(a, b)` with NULL	`a \|\| b` returns NULL if either is NULL, use `concat()` to keep MySQL behaviour
Implicit type casting (`WHERE id = '42'`)	Strict typing, cast explicitly: `id = '42'::int`
Loose `GROUP BY`	Every non aggregated column must be in GROUP BY, or use `DISTINCT ON`
FULLTEXT index + `MATCH AGAINST`	`tsvector` + GIN index, or `pg_trgm`

Framework and ORM notes

Laravel: change the connection driver to pgsql, review any DB::raw() usage, and check that migrations using unsignedBigInteger still behave. whereRaw is where bugs hide.
Django: switch to django.db.backends.postgresql, then run python manage.py makemigrations --check to catch schema drift between the ORM model and the pgloader output.
Rails / ActiveRecord: update database.yml, regenerate schema.rb and check any find_by_sql calls.
Node (Prisma, TypeORM, Sequelize): change the provider and regenerate the client. Watch for BigInt serialisation issues that did not exist with MySQL.
Everyone: connection pooling behaves differently. PostgreSQL connections are heavier, so put PgBouncer in transaction mode in front of it if you have more than a few hundred concurrent connections.

The most reliable way to find every broken query: run your full test suite against PostgreSQL in CI, and enable log_min_error_statement = error plus log_statement = 'all' on the staging database for a few days of real traffic.

Step 8: The cutover plan

Option A: short maintenance window (simplest, recommended for most)

Announce the window, typically 30 to 120 minutes depending on data size.
Put the application into read only or maintenance mode.
Stop all writers: web app, workers, cron, queue consumers, external integrations.
Confirm no writes are landing: check SHOW PROCESSLIST on MySQL and the binlog position stops advancing.
Take a final MySQL backup (mysqldump --single-transaction). This is your rollback insurance.
Run pgloader with the exact same load file used in rehearsal.
Run the verification scripts from step 6. Do not skip this because you are behind schedule.
Run VACUUM ANALYZE.
Flip the application configuration to PostgreSQL and restart services.
Smoke test: login, create a record, update a record, run a report, process one queue job.
Leave maintenance mode. Watch error rates and slow query logs for 60 minutes before declaring success.

Option B: near zero downtime with change data capture

For datasets where a full pgloader run takes hours, use a two phase approach:

Run pgloader against a consistent snapshot while MySQL stays live, and record the binlog file and position.
Stream ongoing changes from that binlog position into PostgreSQL with a CDC tool such as Debezium or a managed service like AWS DMS.
Wait until replication lag is near zero.
Freeze writes for 2 to 5 minutes, let the last events drain, verify counts, then flip the application.

This is significantly more moving parts. Use it when the business genuinely cannot accept an hour of downtime, not by default.

Step 9: The rollback plan

Write this down before cutover night and give it to whoever is on call. A rollback plan you invent at 3 AM is not a plan.

Keep MySQL running, untouched, in read only mode for at least 7 days after cutover. Do not decommission it. Do not let anyone “clean it up”.
Define a rollback deadline. Example: if PostgreSQL is not fully validated within 45 minutes of the flip, we roll back. Decide the number in advance so nobody debates it under pressure.
Rollback procedure: maintenance mode on, revert the config or environment variables to the MySQL connection string, restart services, run smoke tests, maintenance mode off. It should take under 10 minutes if the config is a single environment variable.
The point of no return is the first write that only exists in PostgreSQL. After that, a rollback means replaying those writes into MySQL by hand. That is why smoke testing happens before you reopen public traffic.
Optional safety net: for the first hours, keep the app in read only on PostgreSQL while you validate reads. Reads are trivially reversible.
Backups on both sides: a final mysqldump before cutover, and pg_dump of the freshly loaded PostgreSQL database once verification passes.

Step 10: The week after

Turn autovacuum back on and revert the aggressive migration settings (synchronous_commit, checkpoint_timeout).
Enable pg_stat_statements and review the top 20 slowest queries. Query plans differ from MySQL, and a query that used an index there may sequential scan here.
Add the indexes PostgreSQL wants, which is not always the same set MySQL wanted. Partial and expression indexes are your new friends.
Check for unused indexes migrated blindly from MySQL: SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
Set up backups (pgBackRest or your cloud provider’s snapshots) and test a restore.
Rewrite the stored procedures, triggers and views that pgloader did not migrate, in PL/pgSQL.
Reconnect monitoring: connection count, replication lag, transaction ID wraparound age, cache hit ratio, bloat.

Common pgloader errors and how to fix them

Error	Cause and fix
`date/time field value out of range`	Zero dates. Add the `zero-dates-to-null` cast rule.
`invalid byte sequence for encoding "UTF8"`	Latin1 bytes stored in a utf8 column. Fix the source encoding first, or set the source encoding explicitly in the load file.
`value out of range for type integer`	An unsigned INT above 2.1 billion. Cast to `bigint`.
`duplicate key value violates unique constraint` on first insert	Sequences not reset. Add `reset sequences`, or run `setval()` manually per table.
Load hangs or connection times out	Increase `net_read_timeout` / `net_write_timeout`, lower `workers`, or exclude the largest table and load it separately.
Table exists but is empty	Check the rejected rows files in `/tmp/pgloader/`. Every row failed a cast.
Tables landed in schema `appdb` instead of `public`	Add the `ALTER SCHEMA 'appdb' RENAME TO 'public'` clause.

FAQ

Is PostgreSQL harder than MySQL?

It is stricter, not harder. PostgreSQL rejects things MySQL silently accepts, such as invalid dates, loose GROUP BY and implicit type casts. That strictness is exactly why data quality tends to improve after a migration. Day to day administration (backups, replication, upgrades) requires slightly more deliberate setup, but tooling in 2026 is mature and the documentation is excellent.

Can pgloader migrate MySQL to PostgreSQL with zero downtime?

Not by itself. pgloader performs a one time bulk load, it does not do continuous replication. For near zero downtime, combine a pgloader snapshot with a change data capture tool like Debezium or AWS DMS to replay changes until you flip the application.

Does pgloader migrate views, triggers and stored procedures?

No. It migrates tables, data, indexes, primary keys and foreign keys. Views, triggers, stored procedures, functions and events must be rewritten in PL/pgSQL manually. Inventory them before you plan the project timeline. umami.is makes the same point with more data.

How do I migrate MariaDB to PostgreSQL?

The same way. pgloader speaks the MySQL wire protocol, so a MariaDB source uses the identical mysql:// connection string and load file. Watch for MariaDB specific types such as UUID and dynamic columns, which need manual handling.

How long does a MySQL to PostgreSQL migration take?

The pgloader run itself typically moves 10 to 50 GB per hour depending on hardware, network and index count. The project as a whole is dominated by application code changes and testing, not the data copy. For a mid sized application, budget two to four weeks of engineering time, with a cutover window of one to two hours.

Are MySQL and PostgreSQL similar?

They both speak SQL and cover roughly the same use cases, so the mental model transfers. The differences that matter in a migration are strict typing, case sensitive string comparison, MVCC and vacuum instead of purge threads, sequences instead of AUTO_INCREMENT, and a much richer set of index types, data types and extensions on the PostgreSQL side.

Should I use an online MySQL to PostgreSQL converter instead?

Online converters are fine for a toy schema. For anything containing customer data, uploading a dump to a third party website is a compliance problem and the conversion quality is usually worse than a properly written pgloader load file. Keep the migration inside your own infrastructure.

Final checklist

Schema inventory done, non migratable objects listed
Data type cast rules written and reviewed
pgloader load file in version control
Two full rehearsal runs on production sized data
Row count, aggregate and structure verification scripts ready
Application code changes merged and passing CI against PostgreSQL
Cutover runbook with timings and named owners
Rollback deadline agreed and written down
Final MySQL backup taken before the flip
MySQL kept read only for 7 days after cutover

Migrating from MySQL to PostgreSQL is a very solvable problem when it is treated as an engineering project rather than a single command. The tooling is mature. The risk lives in the details: zero dates, unsigned integers, case sensitivity and the queries nobody remembered were there.

Need a hand? The team at Coding4 plans and executes MySQL to PostgreSQL migrations, from schema audit to post cutover tuning. Get in touch if you want a second pair of eyes on your migration plan.