MySQL and PostgreSQL are the two most widely used open source relational databases, and the comparison keeps coming up — in benchmarks, in conference talks, and in migration tickets. Recent head-to-head tests continue to show PostgreSQL pulling ahead on advanced query features, JSON handling, and indexing flexibility. If your team has been considering a move, here is the good news: it does not have to mean a rewrite. With pgloader, you can migrate the schema, the data, and the indexes in a single pass. This tutorial walks through the whole migration — from installing pgloader to verifying the data on the other side — so you can plan the switch with confidence.

What you will need

Gather these before you start:

• A MySQL instance you can read from, with a user that has at least SELECT and SHOW VIEW privileges
• PostgreSQL 13 or newer installed and accepting TCP connections
• pgloader installed on a machine that can reach both database servers
• A maintenance window long enough to copy your largest tables

Step 1: Install pgloader

pgloader ships in most package managers. On Debian or Ubuntu:

sudo apt update
sudo apt install -y pgloader
pgloader --version

On macOS, use Homebrew: brew install pgloader. On other distributions, check the pgloader documentation for the package that matches your system. Any recent version works; the command file syntax below is stable across 3.x releases.

Step 2: Create the target database

pgloader creates tables and indexes for you, but the database and application user should exist first. On the PostgreSQL server:

sudo -u postgres createdb myapp
sudo -u postgres createuser --pwprompt myapp_user
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;"

Note that pgloader connects over TCP for this workflow, so make sure listen_addresses in postgresql.conf is not set to localhost only.

Step 3: Write a pgloader command file

pgloader uses a .load file to describe the source, the target, and the conversion rules. This is where you handle the type differences between MySQL and PostgreSQL:

LOAD DATABASE
     FROM mysql://myapp_user:secret@localhost/myapp
     INTO postgresql://myapp_user:secret@localhost/myapp

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 4, concurrency = 1,
      multiple readers per thread, rows per range = 50000

 SET PostgreSQL PARAMETERS
     maintenance_work_mem = '128MB',
     work_mem = '12MB'

 CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
      type tinyint to smallint,
      type year to integer;

The CAST rules matter most: MySQL DATETIME becomes PostgreSQL timestamptz, TINYINT becomes smallint, and zero dates are converted to NULL so the load does not abort on invalid values. Adjust the list to match the types actually used in your schema.

Step 4: Run the migration

Run pgloader with the command file: pgloader myapp.load.

pgloader prints a table-by-table report showing rows read, rows written, and elapsed time. Watch for tables with errors — pgloader continues past individual failures, so a non-zero error count means you have cleanup work before you switch traffic.

Step 5: Verify the data

Compare row counts between the two systems for a few key tables:

SELECT 'users' AS tbl, count(*) FROM users
UNION ALL
SELECT 'orders', count(*) FROM orders;

Then insert a test row to confirm the primary key sequences were reset correctly, and run ANALYZE; so the PostgreSQL planner has fresh statistics. Finally, run the application against the staging copy and watch the logs for type or syntax errors.

Post-migration checklist

• Replace MySQL-specific syntax: INSERT ... ON DUPLICATE KEY UPDATE becomes INSERT ... ON CONFLICT (id) DO UPDATE
• Convert ENUM columns to CHECK constraints or lookup tables
• Update the application connection string and driver (JDBC, psycopg, or your ORM’s PostgreSQL dialect)
• Set up backups with pg_dump before you go live
• Test the full application flow against a staging copy, not production

Next steps

Benchmarks comparing the two databases continue to drive migration decisions — the PostgreSQL vs MySQL benchmark roundup covers the performance angle in detail. For the tooling, the pgloader MySQL reference documents every CAST and WITH option, and the PostgreSQL INSERT documentation explains ON CONFLICT handling. Once your data lives in PostgreSQL, the SQL you write can take advantage of window functions, CTEs, and JSONB — the features that make the migration worth doing in the first place.