SwapSQL

pgloader vs manual migration vs SwapSQL

There are three practical ways to move a MySQL database to PostgreSQL: the pgloader tool, a hand-written migration with mysqldump and psql, or a hosted converter like SwapSQL. None of them is "best" in the abstract — they trade setup effort against control. Here is how to pick, with no marketing gloss.

pgloader — scriptable, great for repeated bulk loads

pgloader connects to a live MySQL server, reads the schema, casts the types and streams the rows straight into PostgreSQL. For a one-line load it is hard to beat: 

pgloader mysql://user:pass@localhost/sourcedb \
  postgresql://user:pass@localhost/targetdb

For anything real you write a command file so the casting rules are version-controlled: 

LOAD DATABASE
  FROM     mysql://user:pass@localhost/sourcedb
  INTO     postgresql://user:pass@localhost/targetdb

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

  CAST type tinyint to boolean using tinyint-to-boolean,
       type datetime to timestamptz;

Strengths: it is fast on large tables (parallel streaming), it resets sequences for you, and a command file makes the migration repeatable in CI. Weaknesses: it needs a working Common Lisp runtime, the Debian/Ubuntu package bundles an old MySQL driver that chokes on MySQL 8 collations, and it needs direct network access to both live databases — awkward when the source is behind a cloud provider's firewall. You also need to read its cast rules carefully: defaults like tinyint(1) → boolean are usually right but occasionally wrong for your data.

Manual migration — maximum control, maximum effort

The DIY route is mysqldump for the data, a hand-translated schema, and psql to load it: 

mysqldump --no-create-info --compatible=postgresql \
  --skip-extended-insert mydb > data.sql
# then hand-write the CREATE TABLE statements for PostgreSQL
psql -d targetdb -f schema.sql
psql -d targetdb -f data.sql

The reason this is more work than it looks: MySQL's dump is not valid PostgreSQL. Backticks, AUTO_INCREMENT, ENGINE=InnoDB, UNSIGNED, zero dates (0000-00-00), tinyint(1) booleans, ENUM columns and ON UPDATE CURRENT_TIMESTAMP all have to be rewritten by hand. The --compatible=postgresql flag helps with quoting but does not translate types or DDL. Strengths: total control, no third-party tool, and you understand every line that runs against production. Weaknesses: it is slow, error-prone, and easy to get subtly wrong on a schema with dozens of tables. It only makes sense when the schema is small, or when you have a requirement that forbids any data leaving your network and you have time to do it carefully.

SwapSQL — upload a dump, get a clean PostgreSQL file

SwapSQL takes the file you already have — a mysqldump .sql or .sql.gz — and returns a PostgreSQL-ready dump. It handles the same rewrites you would do by hand (type mapping, sequences, zero dates, ENUM, definer clauses) and reports the warnings instead of failing silently. No Lisp toolchain, no driver compile, and the source database never needs to be reachable from the internet, because you work from a dump file: 

mysqldump --single-transaction --no-tablespaces mydb > dump.sql
# upload dump.sql at swapsql.com → download the PostgreSQL dump
psql -d targetdb -f converted.sql

Strengths: zero setup, output is generated with --no-owner --no-privileges so it restores under any role, and the conversion report tells you exactly what changed. Weaknesses: it works from a dump rather than a live connection, so for a continuously-changing database you still need a cutover plan, and the largest files belong on a paid plan.

Quick decision guide

A common pragmatic path: use SwapSQL to get a correct schema and a first full load, then keep its output in version control as the canonical translation. You get pgloader-grade type handling without owning the toolchain.

Already have a dump? Convert MySQL to PostgreSQL now →