Migrate Data with pgloader

You have a MySQL or SQLite database that's been running your application for years, and now the time has come to move it into PostgreSQL. Manually exporting tables, transforming data types, and importing them one by one is a recipe for late nights, broken foreign keys, and silent data corruption. This lesson shows you how to migrate data with pgloader, a purpose-built tool that automates the entire process, saving you hours and eliminating the most common migration pitfalls.

The Problem This Lesson Solves

Migrating between database systems is rarely as simple as dumping and restoring. You face data type mismatches (MySQL's TINYINT(1) vs PostgreSQL's BOOLEAN), incompatible SQL dialects, encoding issues, and index/constraint differences. Doing this by hand means writing custom scripts for every table, debugging subtle conversion errors, and praying you didn't miss a foreign key relationship.

The pain is real:

• Manual data export with SELECT ... INTO OUTFILE or sqlite3 .dump produces files that PostgreSQL can't consume directly.
• Schema translation requires knowledge of both systems' type systems and syntax.
• Data validation falls on you — a single bad row can abort an entire import.
• Downtime grows because you're moving data over multiple steps with verification gaps.

pgloader solves all this by reading your source schema, mapping it to PostgreSQL, transforming data on the fly, and loading it with parallelism — all in one command.

Core Concept / Mental Model

Think of pgloader as a smart moving truck for your data. You tell it the pickup location (source database), the drop-off destination (PostgreSQL), and any special instructions (like "convert this column type" or "ignore that table"). The truck unpacks the entire move itself: it inspects the furniture (schema), wraps fragile items (data types), and loads everything in order (dependencies).

Under the hood, pgloader:

• Reads your source schema — tables, columns, types, constraints, indexes.
• Generates a PostgreSQL-compatible schema — it knows how to map MySQL types like DATETIME to TIMESTAMP WITH TIME ZONE.
• Transforms data while loading — using CAST and SET rules, it can change values (e.g., convert MySQL TINYINT(1) to PostgreSQL BOOLEAN).
• Loads with concurrency and transaction control — it honors FOREIGN KEY dependencies while keeping throughput high.

Pro tip: pgloader is not a one-shot script; it's a declarative tool. You write migration rules in a .load file (or just point it at a URI) and it handles the rest. This makes migrations reproducible and reviewable.

How It Works Step by Step

Here's the logical sequence of a pgloader migration:

1. Install pgloader — on macOS via brew install pgloader, on Debian/Ubuntu via apt install pgloader, or with the provided binaries on Windows.
2. Inspect your source — confirm the database URI (e.g., mysql://user:pass@host/db) and note the tables you want to move.
3. Write a command — either a simple one-liner or a .load file with fine-grained control.
4. Run pgloader — it reads the schema, creates tables in PostgreSQL, and loads data in parallel.
5. Verify the results — check row counts, run sample queries, and validate constraints.
6. Fix issues — use pgloader --dry-run to preview changes, or adjust casting rules to resolve type conversions.

Key concepts to understand before you start:

• Source URI — how you specify reading from MySQL, SQLite, or CSV files.
• Target URI — a PostgreSQL connection string.
• Cast rules — how to override automatic type conversions.
• Materialized views / section commands — advanced loading options for handling dependencies.

Hands-On Walkthrough

Let's migrate a sample MySQL database to PostgreSQL. We'll use a simple users and orders schema.

Step 1: Install pgloader

# On macOS
brew install pgloader

# On Debian/Ubuntu
sudo apt install pgloader

Step 2: Basic Migration from a MySQL Database

Assume your MySQL database is app_db and you have PostgreSQL ready:

pgloader mysql://user:pass@localhost/app_db postgresql://postgres:secret@localhost/app_pg

That single command connects to MySQL, reads the schema, creates the same tables in PostgreSQL, and copies all rows. pgloader prints a summary table with the number of rows loaded and errors.

Output preview:

             table name     errors       rows      bytes      total time
--------------------  ----------  ---------  ---------  --------------
                    users          0         10        1.2 kB        0.2s
                   orders          0         25        3.8 kB        0.3s

Step 3: Using a .load File for Control

For more complex migrations, create a migrate.load file:

LOAD DATABASE
     FROM mysql://user:pass@localhost/app_db
     INTO postgresql://postgres:secret@localhost/app_pg

 WITH include drop, create tables, create indexes, reset sequences

 SET work_mem to '128MB', maintenance_work_mem to '512MB'

 CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
      type tinyint
       when unsigned to boolean;

Then run:

pgloader migrate.load

This .load file does the following:

• WITH include drop — drops existing tables in the target to start fresh.
• CREATE TABLES and CREATE INDEXES — recreates schema and indexes automatically.
• CAST — explicitly maps MySQL's DATETIME to TIMESTAMPTZ and converts unsigned TINYINT to BOOLEAN.

Step 4: Migrating from SQLite

pgloader also supports SQLite:

pgloader sqlite:///path/to/your.db postgresql://postgres:secret@localhost/app_pg

Or with a .load file:

LOAD DATABASE
     FROM sqlite:///old_app.db
     INTO postgresql://postgres:secret@localhost/app_pg
 WITH include drop, create tables, create indexes, reset sequences

Compare Options / When to Choose What

When to choose what:

• Use pgloader for most migrations — it's the fastest, most reliable path from MySQL/SQLite to PostgreSQL, especially for production-grade moves.
• Use pg_dump when migrating between PostgreSQL versions or databases — it's native and efficient.
• Use ETL tools if your migration is part of a larger data pipeline with constant updates.
• Use FDWs if you only need to query data across databases temporarily.

Troubleshooting & Edge Cases

Common errors and how to fix them:

• Connection refused — check that PostgreSQL accepts connections (listen_addresses and pg_hba.conf).
• Cast error — e.g., MySQL DATETIME '0000-00-00' can't be stored. Use WITH CAST type datetime using zero-dates-to-null.
• Memory issues — large tables may need higher work_mem. Adjust with SET work_mem to '256MB'.
• Foreign key validation — pgloader loads data in dependency order by default, but if you see FK violations, use ALTER TABLE ... VALIDATE CONSTRAINT afterward or WITH disable triggers.
• Encoding problems — add SET client_encoding to 'UTF8' or specify encoding in the source URI.

Pro tip: Always run pgloader --dry-run first to see what schema changes and casts pgloader will apply. It won't load data, but it's great for catching issues early.

What You Learned & What's Next

You now know how to migrate data with pgloader — from understanding the mental model of a smart moving truck to writing simple one-liners and complex .load files. You can handle MySQL and SQLite sources, apply custom casting rules, and troubleshoot common issues.

The next logical step in the PostgreSQL Tutorial track is to explore pg_dump and backup strategies — essential for protecting the data you've just migrated. You'll also learn about incremental backups and point-in-time recovery, so your PostgreSQL deployments stay resilient.

Keep practicing — the more migrations you do, the faster you'll spot type conversion gotchas and optimize your load commands for massive datasets.