pgLoader - Handles the Garbage Parts of Database Migration So You Don't Have To

Dimitri Fontaine built pgLoader because database migrations suck. If you've ever tried to move data from MySQL to PostgreSQL manually, you know what I'm talking about. The endless charset issues, the 0000-00-00 datetime values that PostgreSQL hates, the schema differences that make you want to cry.

Why pgLoader Actually Works (Unlike Most Migration Tools)

Uses PostgreSQL's COPY Protocol (Which is Actually Fast)
Instead of doing row-by-row INSERTs like an amateur, pgLoader uses PostgreSQL's native COPY command for bulk loading.

This leverages PostgreSQL's bulk loading capabilities and efficient data streaming protocols. We're talking about loading millions of rows without your terminal hanging for hours, unlike slower row-by-row INSERT methods.

Doesn't Die When Bad Data Shows Up
Here's the thing: your source database has shit data. MySQL lets you store 0000-00-00 dates and empty strings where they shouldn't be. Instead of failing completely, pgLoader saves the garbage to `.reject.dat` and `.reject.log` files so you can deal with it later.

Actually Converts Your Schema
pgLoader reads your MySQL/SQLite/Oracle schema and creates equivalent PostgreSQL tables automatically. It handles the type conversions, foreign keys, indexes - the stuff that would take you weeks to figure out manually. Check the MySQL type mapping documentation and PostgreSQL data types to see what it converts. It also handles character set conversions and collation mapping.

Continuous Migration That Doesn't Suck
You can run continuous migrations during development so you're not scrambling to figure out what broke when you finally migrate production. Test your migration script daily instead of praying it works on go-live day.

What Can This Thing Migrate?

pgLoader supports way more than just databases:

• Databases: MySQL, SQLite, MS SQL Server, Oracle
• Files: CSV, fixed-width text, dBase3 (.dbf), IBM IXF
• Compressed: ZIP, TAR, GZIP archives (it extracts them automatically)
• Remote: HTTP/HTTPS URLs - just point it at a CSV on the web

The current version is 3.6.9 (released October 24, 2022) and it's actively maintained. Not like those abandoned SourceForge projects from 2008. Check the release history and commit activity to see it's still being developed.

How Much Time Will This Actually Save?

Look, I've been through MySQL-to-PostgreSQL hell three times. First migration took 16 hours because we didn't know about the charset issues. Second time the migration 'succeeded' but half our datetime fields were fucked. Third time with pgLoader: estimated 4 hours, took 11 hours because MySQL kept timing out on large tables.

Our 'simple' 200GB migration turned into a 3-day nightmare when we discovered our source database had 15 different character encodings. The 0000-00-00 dates were just the beginning. We ended up with 47,000 rejected rows and had to manually clean data that should have been garbage 5 years ago.

But here's the thing - pgLoader at least tells you when shit breaks instead of silently corrupting your data like the custom scripts I wrote before. The parallel loading and optimized PostgreSQL configuration can turn a week-long manual migration into an afternoon. Just don't expect miracles if your source data is complete garbage.

How to Actually Install This Thing

Docker (Just Use This One)

docker pull ghcr.io/dimitri/pgloader:latest

Seriously, just use Docker. The package manager versions are usually outdated, and compiling from source is a nightmare of Common Lisp dependencies that will make you question your life choices. Check the Docker installation guide if you need Docker first.

Package Managers (If You Hate Yourself)

## Debian/Ubuntu (probably an old version)
apt-get install pgloader

## macOS with Homebrew (might work, might not)
brew install pgloader

The Ubuntu package is typically 2-3 versions behind. The Homebrew version works better but still might have dependency issues on newer macOS versions. Check Debian package tracking and macOS compatibility first.

Test Your Installation:

docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version

Your First Migration (That Might Actually Work)

Dead Simple Database Migration:

pgloader mysql://user:pass@source-host/dbname postgresql://user:pass@target-host/dbname

This works about 60% of the time. When it doesn't, you'll need the config file approach below.

CSV File Migration:

pgloader ./data.csv postgresql://localhost/targetdb

Reality Check: Your CSV probably has encoding issues. UTF-8 BOM will break things. Excel exports often have hidden characters. Plan for 2x your estimated time.

When the Simple Approach Fails (Config Files)

Create a .load file because the one-liner approach broke:

LOAD DATABASE
    FROM mysql://source-user:password@source-host/source-db
    INTO postgresql://target-user:password@target-host/target-db

-- More workers = faster (until it doesn't)
WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 2

-- PostgreSQL needs more RAM during migration
SET PostgreSQL PARAMETERS
    maintenance_work_mem to '1GB',
    work_mem to '256MB'

-- MySQL timeouts (because MySQL)
SET MySQL PARAMETERS
    net_read_timeout  = '31536000',
    net_write_timeout = '31536000';

Save this as migration.load and run:

pgloader migration.load

Check the configuration documentation for all the options you'll inevitably need.

Things That Will Break (And How to Fix Them)

Memory Issues: pgLoader will eat all your RAM. Set maintenance_work_mem appropriately - 1GB minimum for large databases. Check PostgreSQL memory settings and shared_buffers configuration before starting. Monitor with pg_stat_activity.

Network Timeouts: Remote migrations timeout constantly. Those MySQL timeout settings above aren't optional - they're survival. Set them to a year (31536000 seconds) and call it a day. Also check PostgreSQL connection settings and TCP keepalive parameters.

Character Encoding Hell: MySQL's latin1 charset will bite you. Add this to your config:

SET MySQL PARAMETERS
    mysql_charset = 'utf8mb4';

Bad Data: Your 0000-00-00 datetime values and empty strings in NOT NULL columns will create .reject.dat files. This is normal. Review the MySQL casting rules to understand what gets converted.

Performance Tuning: More workers isn't always better. Start with 2-4 workers and increase if your network and databases can handle it. Monitor with PostgreSQL stats and adjust accordingly.

The Horror Story Nobody Tells You: I spent an entire weekend figuring out that our MySQL server was using latin1 charset for 'UTF-8' data. Good times.

The migration kept succeeding but produced garbage. By Monday morning I had to explain to the CEO why our 'successful' migration corrupted half the customer data.

Don't be the engineer who discovers data corruption in production 3 months after migration. Check those .reject.dat files. Set up monitoring. Test your app thoroughly. I've seen 'successful' migrations destroy businesses because nobody validated the results.

Reality check: Plan for at least 2x your estimated migration time. Something always breaks, usually at the worst possible moment.