pgloader Configuration Generator

pgloader migrates MySQL, SQL Server, SQLite and CSV data into PostgreSQL in one command, but everything interesting lives in the .load file: the connection URIs, the CAST rules that decide whether tinyint(1) becomes boolean, the table include and exclude lists, and the batching that decides how long the run takes. This generator builds that file for you from a handful of fields, then gives you the command to run it with logging and a rejected-rows directory, plus the SQL to verify row counts, sequences and constraints once the load finishes. Everything is generated in your browser — no connection details are sent anywhere, and nothing is stored.

Source

Source engineHostPortDatabaseUserPassword

Target PostgreSQL

HostPortDatabaseUserPasswordTarget schema

What to migrate

Target actionInclude only tables (regex, comma separated)Exclude tables (regex, comma separated)Materialize views (name: SQL)

Type mapping

tinyint(1)Date and time typesENUM to textZero dates to NULLDowncase identifiers

Throughput and error handling

workersconcurrencybatch rowsbatch sizerows per rangeStop on first errorDisable triggers

pgloader .load file

LOAD DATABASE
     FROM mysql://migrator:s3cret@mysql.internal:3306/shop
     INTO postgresql://postgres:s3cret@postgres.internal:5432/shop

     WITH create tables,
          create indexes,
          reset sequences,
          downcase identifiers,
          on error stop,
          workers = 8,
          concurrency = 2,
          multiple readers per thread,
          rows per range = 100000,
          batch rows = 25000,
          batch size = 20MB,
          prefetch rows = 100000

      SET PostgreSQL PARAMETERS
          maintenance_work_mem to '512MB',
          work_mem to '64MB',
          search_path to 'public, public'

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

     CAST type tinyint when (= 1 precision) to boolean using tinyint-to-boolean drop typemod,
          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 enum to text drop typemod,
          type decimal when (and (= 18 precision) (= 6 scale)) to numeric drop typemod,
          type bigint when (= 20 precision) to bigint drop typemod

     EXCLUDING TABLE NAMES MATCHING 'audit_log', 'sessions'

     ALTER SCHEMA 'shop' RENAME TO 'public'

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

     AFTER LOAD DO
       $$ ANALYZE; $$
;

How to run it

# 1. Save the configuration above as mysql-to-postgres.load
#    Keep it out of git — it contains credentials.
chmod 600 mysql-to-postgres.load

# 2. Dry run: pgloader parses the file and connects, but loads nothing.
pgloader --dry-run mysql-to-postgres.load

# 3. Real run, with a log file and a rejected-rows directory you can inspect.
pgloader --verbose \
         --logfile pgloader.log \
         --summary pgloader-summary.log \
         --root-dir /tmp/pgloader \
         mysql-to-postgres.load

# Docker alternative (no local install; mount the working directory):
docker run --rm -v "$PWD":/work -w /work \
       ghcr.io/dimitri/pgloader:latest \
       pgloader --verbose /work/mysql-to-postgres.load

Post-migration verification SQL

-- 1. Row counts per table, to compare against the source.
SELECT relname AS table_name, n_live_tup AS approx_rows
FROM   pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC;

-- Approximate counts come from the stats collector. For an exact count of one table:
SELECT count(*) FROM public.orders;

-- 2. Sequences: pgloader's "reset sequences" should have done this, but verify.
--    Any row here where last_value < max_id will throw duplicate key errors on the next INSERT.
SELECT s.relname AS sequence_name, pg_sequence_last_value(s.oid) AS last_value
FROM   pg_class s
JOIN   pg_namespace n ON n.oid = s.relnamespace
WHERE  s.relkind = 'S' AND n.nspname = 'public';

-- Fix a single sequence by hand if it is behind:
SELECT setval(pg_get_serial_sequence('public.orders', 'id'),
              (SELECT COALESCE(max(id), 1) FROM public.orders));

-- 3. Constraints and indexes that did not come across.
SELECT conrelid::regclass AS table_name, conname, contype
FROM   pg_constraint
WHERE  connamespace = 'public'::regnamespace
ORDER  BY 1, 3;

-- 4. Columns that ended up as text but hold only numbers or timestamps —
--    usually a missing CAST rule.
SELECT table_name, column_name, data_type
FROM   information_schema.columns
WHERE  table_schema = 'public' AND data_type = 'text'
ORDER  BY table_name, ordinal_position;

-- 5. Refresh planner statistics for the whole database after the load.
-- (shell)  vacuumdb --analyze-in-stages --dbname shop

Notes

Credentials live in the .load file in plain text. Either chmod 600 it and delete it after the migration, or replace the URIs with environment-driven ones and pass them on the command line.

MySQL specifics: pgloader converts zero dates (0000-00-00) to NULL, which requires dropping NOT NULL on those columns — that is what "drop not null" in the date CAST rules does. Re-add the constraint after the load once you have cleaned the data.

MySQL ENUM columns become PostgreSQL enum types by default. Casting them to text is usually easier to evolve later, but you lose the value check — add a CHECK constraint afterwards if you want it back.

Unsigned integers have no PostgreSQL equivalent. pgloader promotes them to the next larger signed type (unsigned int -> bigint), so a column that was 4 bytes in MySQL becomes 8 bytes here.

"on error stop" is the safe default for a rehearsal: you want to see the first failure. Switch to "on error resume next" for the real cutover only if you are prepared to reconcile the rejected rows in /tmp/pgloader afterwards.

Foreign keys and indexes are created after the data loads, which is why "create no indexes" plus a manual index build can be faster on large databases — you control the parallelism.

Run the whole migration twice: once as a rehearsal against a copy to measure duration and collect errors, once for real. The duration from the rehearsal is your downtime estimate.

Do more than pgloader configuration generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Pick the source engine and fill in the source and target connection details, plus the schema you want the tables to land in.
Choose what pgloader should create (tables and indexes, tables only, or data into existing tables), set the cast rules for booleans and timestamps, and list any tables to include or exclude.
Copy the generated .load file, run the dry-run command first, then the real command — and finish with the verification SQL to confirm row counts and sequences.

Frequently asked questions

What is a pgloader .load file and why do I need one?

pgloader can run a simple migration straight from the command line — pgloader mysql://user@host/db postgresql://user@host/db — but that form gives you no control over type mapping, table filtering or batching. A .load file is pgloader's command language: it holds the FROM and INTO connection strings, a WITH block of options such as 'create tables', 'downcase identifiers' and 'on error stop', a CAST block that overrides the default type mapping, and optional INCLUDING ONLY / EXCLUDING table filters. Because it is a file, it can be reviewed, put under version control (with the credentials substituted out) and rerun identically for the rehearsal and the real cutover.

How do I stop pgloader turning MySQL tinyint(1) into a smallint?

Add an explicit cast rule: type tinyint when (= 1 precision) to boolean using tinyint-to-boolean drop typemod. The 'when (= 1 precision)' guard means only tinyint(1) columns are affected, so a real tinyint(4) counter still becomes a small integer rather than a boolean. The same pattern handles the other common MySQL surprises — casting datetime to timestamptz with 'using zero-dates-to-null' so 0000-00-00 values become NULL instead of failing the load, and casting enum to text so you get a plain string column rather than a PostgreSQL enum type you have to ALTER TYPE every time a value is added.

How long does a pgloader migration take, and how do I make it faster?

Throughput is bounded by the source's read speed, the network, and how much work PostgreSQL does per row. The biggest wins are: raise workers and concurrency so several tables load in parallel, raise batch rows and batch size so fewer round trips are made, use 'create no indexes' and build indexes afterwards with CREATE INDEX CONCURRENTLY at your own parallelism, and raise maintenance_work_mem on the target for the duration. Always rehearse against a copy first — the rehearsal time is your downtime estimate. Once the data is over, Chat2DB is a quick way to eyeball the migrated schema, compare row counts and fix up the types that came across wrong: https://app.chat2db.ai or download it from https://chat2db.ai/download.