How to Migrate MySQL to PostgreSQL with pgloader

pgloader reads a live MySQL database and loads it into PostgreSQL in a single command: it creates the tables with converted data types, copies the data, then builds indexes, foreign keys and sequences. In this tutorial you will use pgloader on Ubuntu 24.04 to migrate a MySQL database called myapp to PostgreSQL, verify the result, and fix the MySQL-specific SQL that most applications contain.

Prerequisites

To follow this guide you need:

• A source MySQL (or MariaDB) server with the database to migrate.
• A target PostgreSQL server, for example a CubePath VPS running Ubuntu 24.04 with PostgreSQL installed.
• A machine to run pgloader from with network access to both servers. The PostgreSQL server itself is a good choice, and is what this guide assumes.
• A non-root user with sudo privileges on that machine.
• Access to the application's source code, to adapt its queries.

In the commands below, replace mysql_host with the address of your MySQL server and myapp with your database name.

Step 1 - Installing pgloader and the database clients

pgloader is packaged in Ubuntu 24.04. Install it together with the MySQL client, which you will use to compare the data:

sudo apt update
sudo apt install -y pgloader mysql-client

Check the installed version:

pgloader --version

pgloader version "3.6.10~devel"
compiled with SBCL 2.2.9.debian

The exact version string may differ; any 3.6.x release works for this guide.

Step 2 - Preparing a read-only MySQL user

pgloader only needs to read from MySQL. Create a dedicated user on the MySQL server so the migration cannot change the source. Replace your_pgloader_ip with the IP of the machine running pgloader and your_mysql_password with a strong password:

sudo mysql

CREATE USER 'pgloader'@'your_pgloader_ip' IDENTIFIED BY 'your_mysql_password';
GRANT SELECT, SHOW VIEW ON myapp.* TO 'pgloader'@'your_pgloader_ip';
EXIT;

On the pgloader machine, confirm the user can connect and see the tables. The same query gives you a quick inventory of the source:

mysql -h mysql_host -u pgloader -p -e "SELECT table_name, engine, table_collation FROM information_schema.tables WHERE table_schema = 'myapp';"

Look for tables that are not InnoDB or that use a collation other than utf8mb4. pgloader copies MyISAM tables fine, but a mix of latin1 and utf8mb4 columns is a common source of encoding errors later.

Step 3 - Creating the PostgreSQL database

Create a role that will own the migrated tables, and an empty database owned by it. Use a strong password in place of your_pg_password:

sudo -u postgres psql

CREATE ROLE myapp LOGIN PASSWORD 'your_pg_password';
CREATE DATABASE myapp OWNER myapp;
\q

pgloader creates every object as the user it connects with, so connecting as myapp means the application role owns all the tables without extra ALTER ... OWNER statements.

Step 4 - Writing the pgloader command file

You can call pgloader with two connection strings, but a command file lets you set options and keep the configuration under version control. Create it:

nano ~/myapp.load

LOAD DATABASE
     FROM mysql://pgloader:your_mysql_password@mysql_host/myapp
     INTO postgresql://myapp:your_pg_password@localhost/myapp

 WITH include drop, create tables, create indexes, reset sequences,
      foreign keys, downcase identifiers,
      workers = 4, concurrency = 1

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '512MB'

 ALTER SCHEMA 'myapp' RENAME TO 'public'
;

What each part does:

• FROM and INTO are connection URLs. If a password contains characters such as @, : or /, URL-encode them (@ becomes %40).
• include drop drops tables with the same name on the target first, so you can run the migration again after fixing a problem.
• reset sequences sets every sequence to the current maximum of its column, so new rows do not collide with migrated IDs.
• downcase identifiers converts table and column names to lowercase, which avoids having to quote them in every PostgreSQL query.
• ALTER SCHEMA 'myapp' RENAME TO 'public' puts the tables in PostgreSQL's default public schema. Without it, pgloader creates a schema named after the MySQL database.

pgloader's default type conversions cover most cases. The most relevant ones:

MySQL zero dates such as 0000-00-00 00:00:00 do not exist in PostgreSQL; pgloader converts them to NULL.

Warning

If your application stores values other than 0 and 1 in TINYINT(1) columns, the default boolean conversion loses them. Change those columns to TINYINT(4) or SMALLINT in MySQL first, or add a CAST rule to the command file.

Restrict the file's permissions, since it contains passwords:

chmod 600 ~/myapp.load

Step 5 - Running the migration

First check that pgloader can connect to both databases without loading anything:

pgloader --dry-run ~/myapp.load

If both connections succeed, the output ends without errors. Stop writes to the MySQL database (put the application in maintenance mode) so the copy is consistent, then run the migration:

pgloader ~/myapp.load

When it finishes, pgloader prints a summary table:

             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          0         42                     0.180s
         Create Schemas          0          0                     0.001s
       Create SQL Types          0          2                     0.010s
          Create tables          0         24                     0.121s
         Set Table OIDs          0         12                     0.008s
-----------------------  ---------  ---------  ---------  --------------
           public.users          0      15230     2.1 MB          0.842s
          public.orders          0     184502    31.4 MB          4.910s
...
-----------------------  ---------  ---------  ---------  --------------
        Total import time          ✓     412880    58.7 MB          9.604s

Check the errors column. Any row that failed to load is written, together with the reason, to a reject file under /tmp/pgloader/, which is the first place to look when the counts do not match.

Step 6 - Verifying the migrated data

Start with the structure. List the tables and inspect one of them:

psql -h localhost -U myapp -d myapp -c '\dt'
psql -h localhost -U myapp -d myapp -c '\d users'

Compare exact row counts for your most important tables on both sides. information_schema.tables.table_rows in MySQL is only an estimate for InnoDB, so use count(*):

mysql -h mysql_host -u pgloader -p myapp -e "SELECT count(*) FROM users; SELECT count(*) FROM orders;"
psql -h localhost -U myapp -d myapp -c "SELECT count(*) FROM users;" -c "SELECT count(*) FROM orders;"

The numbers must be identical. Then check that sequences continue after the highest existing ID:

psql -h localhost -U myapp -d myapp -c "SELECT max(id), nextval(pg_get_serial_sequence('users', 'id')) FROM users;"

  max  | nextval
-------+---------
 15230 |   15231
(1 row)

Calling nextval consumes one value, which is harmless.

Step 7 - Adapting MySQL-specific queries

The data is now in PostgreSQL, but queries written for MySQL often fail against it. Search the codebase for the most common MySQL-only constructs:

grep -rnE 'IFNULL|GROUP_CONCAT|DATE_FORMAT|REPLACE INTO|INSERT IGNORE|ON DUPLICATE KEY|LIMIT [0-9]+ *, *[0-9]+|`' ./src

Rewrite each match using the PostgreSQL equivalent:

Two behavioral differences cause most bugs that grep will not find:

• Case sensitivity. MySQL's default collations compare strings case-insensitively, so WHERE email = '[email protected]' matches [email protected]. PostgreSQL does not. Use lower(email) = lower($1) with an index on lower(email), or the citext type for such columns.
• Strict types. PostgreSQL does not silently convert 'abc' to 0 or truncate strings that are too long; it raises an error. Code that relied on MySQL's permissive mode will now fail loudly, which is usually what you want.

If you use an ORM (Django, SQLAlchemy, Laravel Eloquent, Prisma), most generated SQL works after changing the database driver; focus the review on raw queries.

Run your application's test suite against the PostgreSQL database before going further.

Step 8 - Cutting over and keeping a rollback path

Once the tests pass:

1. Put the application in maintenance mode and stop writes to MySQL.
2. Run pgloader ~/myapp.load one last time so PostgreSQL has the latest data (include drop makes the run repeatable).
3. Repeat the row count checks from Step 6.
4. Switch the application's database configuration to PostgreSQL and bring it back online.

pgloader never writes to MySQL, so the source database stays intact. Keep it untouched for a while: if a serious problem appears, you can point the application back to MySQL. Remember that anything written to PostgreSQL after the cutover would have to be copied back manually, so decide on a rollback deadline in advance.

Troubleshooting

Authentication errors against MySQL 8. Older pgloader builds do not support MySQL 8's default caching_sha2_password plugin. On MySQL 8.0, create the migration user with IDENTIFIED WITH mysql_native_password BY '...'. On MySQL 8.4, that plugin is disabled by default and must be enabled with mysql_native_password=ON in the server configuration first.

Heap exhausted or pgloader crashing on large tables. Lower the memory use by reducing workers and adding prefetch rows = 10000 to the WITH clause, then run again.

Encoding errors such as invalid byte sequence for encoding "UTF8". The MySQL table or column declares one character set but contains bytes from another. Check the column collations from Step 2 and fix the data in MySQL, or convert the affected tables to utf8mb4 before migrating.

Missing rows. Read the reject files in /tmp/pgloader/ for the table in question; each rejected row is listed with the PostgreSQL error that caused it.

Conclusion

You migrated a MySQL database to PostgreSQL with pgloader, confirmed that the tables, row counts and sequences match, and adapted the SQL that does not carry over. Next, review the indexes on your busiest queries with EXPLAIN ANALYZE, set up regular backups of the new database with pg_dump, and consider moving JSON columns to jsonb indexes (GIN) where your application filters on them.