--- title: "How to Migrate MySQL to PostgreSQL with pgloader" description: "Migrate a MySQL database to PostgreSQL on Ubuntu 24.04 with pgloader: convert schema and data in one run, verify row counts and adapt MySQL-specific queries." category: "Database Operations" language: en source: https://cubepath.com/docs/database-operations/mysql-to-postgresql-migration-guide updated: 2026-09-25 --- # 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: ```bash sudo apt update sudo apt install -y pgloader mysql-client ``` Check the installed version: ```bash pgloader --version ``` ```output 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: ```bash sudo mysql ``` ```sql 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: ```bash 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`: ```bash sudo -u postgres psql ``` ```sql 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: ```bash nano ~/myapp.load ``` ```text 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 type | PostgreSQL type | |------------|-----------------| | `INT AUTO_INCREMENT` | `integer` with a sequence (`serial`) | | `BIGINT AUTO_INCREMENT` | `bigint` with a sequence (`bigserial`) | | `TINYINT(1)` | `boolean` | | `DATETIME`, `TIMESTAMP` | `timestamptz` | | `ENUM(...)` | A dedicated `ENUM` type | | `TEXT`, `MEDIUMTEXT`, `LONGTEXT` | `text` | | `BLOB` variants | `bytea` | | `JSON` | `jsonb` | 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: ```bash chmod 600 ~/myapp.load ``` ## Step 5 - Running the migration First check that pgloader can connect to both databases without loading anything: ```bash 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: ```bash pgloader ~/myapp.load ``` When it finishes, pgloader prints a summary table: ```output 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: ```bash 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(*)`: ```bash 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: ```bash psql -h localhost -U myapp -d myapp -c "SELECT max(id), nextval(pg_get_serial_sequence('users', 'id')) FROM users;" ``` ```output 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: ```bash 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: | MySQL | PostgreSQL | |-------|------------| | `IFNULL(col, 0)` | `COALESCE(col, 0)` (also valid in MySQL) | | `GROUP_CONCAT(tag ORDER BY tag SEPARATOR ',')` | `STRING_AGG(tag, ',' ORDER BY tag)` | | `DATE_FORMAT(created_at, '%Y-%m-%d')` | `TO_CHAR(created_at, 'YYYY-MM-DD')` | | `LIMIT 20, 10` | `LIMIT 10 OFFSET 20` | | `` `order` `` (backticks) | `"order"` (double quotes) | | `INSERT IGNORE INTO ...` | `INSERT INTO ... ON CONFLICT DO NOTHING` | | `INSERT ... ON DUPLICATE KEY UPDATE name = VALUES(name)` | `INSERT ... ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name` | | `JSON_EXTRACT(data, '$.key')` | `data->'key'` (or `data->>'key'` for text) | Two behavioral differences cause most bugs that grep will not find: - **Case sensitivity.** MySQL's default collations compare strings case-insensitively, so `WHERE email = 'Alice@Example.com'` matches `alice@example.com`. 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.