pgloader migrates MySQL, SQL Server, SQLite and PostgreSQL databases into PostgreSQL with schema discovery, default type casting and a fault-tolerant COPY. Covers the v3 (Common Lisp) vs v4 (Clojure JAR) choice, a verified load file, memory tuning, and how to check the result.

pgloader: Migrating Databases to PostgreSQL in a Single Command

PostgreSQL's COPY stops at the first bad row. The docs are blunt about it: "By default, COPY will fail if it encounters an error during processing", and the rows it already inserted are "left in a deleted state" until you vacuum. PostgreSQL 17 added ON_ERROR ignore, but only for COPY FROM text and CSV, which does not help when your source is a live MySQL or SQL Server instance with ten years of zero dates in it.

pgloader exists for exactly that gap. It connects to the source, discovers the schema, creates the PostgreSQL tables, streams the rows over COPY, and when a batch is rejected it isolates the bad rows into a reject file and keeps loading. One command covers schema and data. The decision you have to make in 2026 is which pgloader to run, and whether your schema is clean enough for the one-liner or needs a load file first.

Which pgloader: v3 or v4

pgloader reads CSV, fixed-width, COPY text, DBF and IXF files, plus SQLite, MySQL, MS SQL Server, PostgreSQL and Redshift databases, and loads into PostgreSQL, Citus or Redshift (intro). It ships under the PostgreSQL Licence.

There are two implementations today. The Common Lisp v3 line is what apt-get install pgloader gives you; its last tag is v3.6.10 from November 2023, and the GitHub Releases page still shows 3.6.9 as "Latest" because 3.6.10 was only tagged. The README now leads with v4: "a full rewrite in Clojure, distributed as a single self-contained JAR requiring Java 21 or later", which "accepts the same .load file syntax and command-line flags". v4 is published as automated pre-release builds under the v4-dev tag (latest build June 2026) and as ghcr.io/dimitri/pgloader:latest. A v4 Debian package is "planned".

pgloader v3 pgloader v4
Runtime Common Lisp (SBCL) native binary Clojure, single JAR, Java 21+
Install apt-get install pgloader, Docker curl -L -o pgloader.jar .../v4-dev/pgloader.jar, Docker
Last release 3.6.10, Nov 2023 Rolling v4-dev pre-release builds
Memory limit SBCL heap fixed at build time (make DYNSIZE=8192) JVM -Xmx
Connection strings mysql://, mssql://, postgresql:// Same, plus jdbc: URIs with driver params passed through
SSL --no-ssl-cert-verification ?sslmode=require in the URI
Load file and CLI Reference Drop-in compatible

My rule: use the distro v3 for a small or medium schema where you want a package manager to own the binary. For anything that has ever printed "Heap exhausted", go straight to the v4 JAR and give it a heap.

What the one-liner actually does

createdb pagila
  pgloader mysql://user@localhost/sakila postgresql:///pagila
  # v4:
  java -Xmx8g -jar pgloader.jar mysql://user@localhost/sakila postgresql:///pagila

Per the quickstart and README, that single invocation:

  1. Reads the MySQL catalog and creates tables, indexes, foreign keys and comments in PostgreSQL.
  2. Downcases table, index and column names, except PostgreSQL reserved keywords (the downcase identifiers option; quote identifiers keeps MySQL's case instead).
  3. Applies the default MySQL casting rules: int auto_increment becomes serial when precision is below 10 and bigserial otherwise, bigint auto_increment becomes bigserial, tinyint(1) becomes boolean via tinyint-to-boolean, datetime and timestamp become timestamptz, 0000-00-00 dates become NULL via zero-dates-to-null, unsigned int becomes bigint, and each enum column gets its own CREATE TYPE named after the table and column.
  4. Loads tables in parallel: workers defaults to 4 for database sources, each with a reader task feeding a queue and writer tasks batching rows into COPY (batches).
  5. Creates the indexes after the data lands, then resets each sequence to the current maximum of its column (reset sequences).

SQL Server defaults are different and worth reading before you trust them (MSSQL reference): every char, varchar, nvarchar and xml column becomes plain text with the length dropped, float, real, numeric, decimal and money all go through float-to-string, tinyint becomes smallint, bit becomes boolean, and uniqueidentifier becomes uuid. If your application relies on varchar(50) constraints, you are adding them back by hand.

The tinyint(1) rule is the one that bites most often. MySQL has no boolean type, so pgloader assumes tinyint(1) is a flag. If you stored 0-9 ratings in one, half your data disappears into true. That is the first thing I check in the source schema before running anything.

When the one-liner is not enough: the load file

The load file is a small DSL. Its parser is order-sensitive, and the documented clause order for MySQL is FROM, INTO, WITH, SET, CAST, MATERIALIZE VIEWS, INCLUDING/EXCLUDING, ALTER TABLE, ALTER SCHEMA, BEFORE LOAD. A file with ALTER SCHEMA at the top does not parse. This one does:

LOAD DATABASE
       FROM mysql://root@localhost/legacy_app
       INTO postgresql://deploy@localhost/new_app
  
   WITH include drop, create tables, create indexes, reset sequences,
        workers = 8, concurrency = 1,
        multiple readers per thread, rows per range = 50000
  
    SET PostgreSQL PARAMETERS
        maintenance_work_mem to '512MB',
        work_mem to '48MB'
  
   CAST type tinyint when (= 1 precision) to smallint drop typemod,
        type int with extra auto_increment to bigserial drop typemod,
        type date drop not null drop default using zero-dates-to-null
  
   MATERIALIZE VIEWS active_customers
  
   INCLUDING ONLY TABLE NAMES MATCHING 'users', 'orders', ~/^product/
  
   ALTER SCHEMA 'legacy_app' RENAME TO 'public';

The CAST rules are the working part. The first one turns off the boolean guess for tinyint(1). The second forces every auto-increment int to bigserial regardless of width, which you want if the table will keep growing after the move. MATERIALIZE VIEWS matters because pgloader's own limitations section says "Views are not migrated"; materializing runs the view's query at the source and lands the result as a table. Triggers and stored procedures are not migrated either, and for PostgreSQL-to-PostgreSQL the only supported objects are extensions, schemas, tables, indexes and constraints. Port procedural code by hand, or with ora2pg if the source is Oracle or MySQL.

Encoding problems get their own clause: DECODING TABLE NAMES MATCHING ~/messed/ AS utf8 tells pgloader how to read tables whose declared charset is wrong. For file sources use the --encoding flag instead.

Flat files use the same machinery from the command line. Note the target table has to exist already for file loads (quickstart):

pgloader --type csv \
           --field "id,name,email,created_at" \
           --with "skip header = 1" \
           --with "fields terminated by ','" \
           - postgresql:///mydb?users < users.csv

Memory, rejected rows, and checking the result

Memory is where v3 and v4 diverge. v3 runs inside an SBCL image whose heap is fixed when the binary is built (make DYNSIZE=8192 pgloader in the install docs); the packaged binary uses whatever the packager chose. What you can tune at runtime are the batch options: prefetch rows (default 100,000 per reader thread), batch rows (default 25,000) and batch size (default 20 MB), passed as --with "prefetch rows = 10000" or in the WITH clause. Wide longtext and blob columns are what blow the heap, so lower prefetch rows first. On v4 all of this collapses into java -Xmx16g -jar pgloader.jar, which is the README's headline reason for the rewrite.

Errors are handled per batch. When COPY rejects a batch, pgloader parses the line number out of PostgreSQL's CONTEXT message, reloads the rows before it, writes the bad row out, and retries the remainder (batches). Rejected rows end up in <root-dir>/<dbname>/<table>.dat with the server error in the matching .log; --root-dir defaults to /tmp/pgloader. Pass --on-error-stop if you would rather abort on the first refusal.

Two flags people misread. --dry-run only parses the load file and checks the connections; it does not print the DDL it would generate. To review the schema, run with --with "schema only", inspect the tables in psql, then run again with --with "data only".

Verification is the part the intro promised. pgloader prints a per-table summary of rows read, rows rejected and elapsed time, and --summary report.json writes it to a file (.csv and .copy work too). My checklist after every load:

  1. grep -c . /tmp/pgloader/<db>/*.dat to count rejected rows per table, then read the .log next to each.
  2. Diff SELECT COUNT(*) per table between source and target; the summary's "read" column should match the source.
  3. Spot-check the columns the cast rules touched: tinyint, enum, timestamps that were zero dates.

One limitation to plan around: pgloader is a full-pass, one-shot copy with no resume and no change capture. If the source keeps taking writes, you either freeze it for the duration or pair pgloader with a CDC tool such as pg_chameleon for MySQL, AWS DMS, or pgcopydb for PostgreSQL-to-PostgreSQL.

If the source schema is sane, the one-liner really is an afternoon's work. If it has tinyint(1) flags carrying real numbers, views the application reads, or tables you need renamed, write the load file first, run it schema only, and only then move the rows.