MySQL to PostgreSQL Migration Guide

Before you migrate from MySQL to PostgreSQL

A MySQL to PostgreSQL migration is mostly mechanical for tables and data and mostly manual for code. Before starting, list each MySQL database and its size, the views, triggers, stored procedures and events, the SQL modes the server runs with, and every application and report that connects. Read the MySQL vs PostgreSQL comparison for the engine differences that affect design, and the database migration guide for the overall process.

One structural difference shapes the result: a MySQL database maps naturally to a PostgreSQL schema. pgloader's own example migrates the MySQL sakila database and renames the resulting schema with ALTER SCHEMA 'sakila' RENAME TO 'pagila', so decide early whether each MySQL database becomes a schema in one PostgreSQL database or a database of its own.

Migrating with pgloader, step by step

pgloader is open source; the latest stable release is 3.6.9. Its documentation now recommends a Java-based v4 for new deployments, published so far only as a development build, so the steps below use the v3 packages.

1. Install pgloader on a machine that can reach both servers: from the PostgreSQL apt repository or Debian with apt-get install pgloader, from yum.postgresql.org on RPM systems, or with the dimitri/pgloader Docker image.
2. Create the empty target database in PostgreSQL and a user that owns it.
3. Run a first migration with the one-line command below against a copy or a quiet source. By default pgloader drops and recreates the target tables (include drop), creates indexes and foreign keys, downcases identifiers and resets sequences.
4. Read the summary report pgloader prints at the end: for each table, the rows read, rows imported, errors and time taken.
5. Refine with a load file when you need to rename the schema, change casting rules, include or exclude tables, or materialise views as tables.
6. Recreate what pgloader skips: views, triggers, procedures and events, written in PostgreSQL SQL or PL/pgSQL.

pgloader --version

# one command: MySQL database "shop" into PostgreSQL database "shop"
pgloader mysql://<mysql-user>:<your-password>@<mysql-host>/shop \
         pgsql://<pg-user>:<your-password>@<pg-host>/shop

# the same with the official Docker image
docker run --rm -it dimitri/pgloader:latest pgloader --version

LOAD DATABASE
     FROM mysql://<mysql-user>:<your-password>@<mysql-host>/shop
     INTO postgresql://<pg-user>:<your-password>@<pg-host>/shop

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1

CAST type date drop not null drop default using zero-dates-to-null

ALTER SCHEMA 'shop' RENAME TO 'public';

What pgloader does not migrate

The pgloader documentation lists the limits for MySQL sources: views are not migrated (you can materialise chosen views as tables with MATERIALIZE VIEWS), triggers are not migrated, and of the geometric types only POINT is fully covered. Stored procedures and functions are outside pgloader's scope. For a managed alternative that converts code as well, DMS Schema Conversion supports a MySQL to PostgreSQL path, and AWS DMS can then copy the data with ongoing change capture; see the AWS DMS guide.

MySQL to PostgreSQL data types

These are pgloader's default casting rules for MySQL, which you can override with CAST in a load file. If you write the PostgreSQL schema yourself, modern PostgreSQL prefers GENERATED BY DEFAULT AS IDENTITY to the older serial types.

Converting MySQL queries to PostgreSQL

There is no official MySQL to PostgreSQL query converter from either project, and online converters are unofficial; treat their output as a draft. The differences below cover most application queries. In particular, MySQL quotes identifiers with backticks, while PostgreSQL uses double quotes and folds unquoted names to lower case, which is why pgloader downcases identifiers by default.

Worked example: a table, an upsert and validation queries

Compare row counts and key ranges for every table, and a few aggregates for important columns. MySQL's CHECKSUM TABLE reports a checksum for a table's contents, but there is no matching PostgreSQL command and the values would not be comparable across engines, so rely on counts, sums and sampled rows instead. After a pgloader run with reset sequences, check that the next generated id is above the highest copied id before the application starts inserting.

CREATE TABLE customers (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email      VARCHAR(255) NOT NULL,
    is_active  TINYINT(1)   NOT NULL DEFAULT 1,
    plan       ENUM('free','pro','team') NOT NULL DEFAULT 'free',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO customers (email, plan) VALUES ('ana@example.com', 'pro') AS new
ON DUPLICATE KEY UPDATE plan = new.plan;

CREATE TYPE customer_plan AS ENUM ('free', 'pro', 'team');

CREATE TABLE customers (
    id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email      varchar(255)  NOT NULL UNIQUE,
    is_active  boolean       NOT NULL DEFAULT true,
    plan       customer_plan NOT NULL DEFAULT 'free',
    created_at timestamp     NOT NULL DEFAULT now()
);

INSERT INTO customers (email, plan) VALUES ('ana@example.com', 'pro')
ON CONFLICT (email) DO UPDATE SET plan = EXCLUDED.plan;

SELECT COUNT(*)  AS row_count,
       MIN(id)   AS min_id,
       MAX(id)   AS max_id,
       SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_rows   -- MySQL
FROM customers;

SELECT COUNT(*)  AS row_count,
       MIN(id)   AS min_id,
       MAX(id)   AS max_id,
       COUNT(*) FILTER (WHERE is_active) AS active_rows                -- PostgreSQL
FROM customers;

Cutover, and the next steps

• Rehearse the full pgloader run on a copy and time it; that time, plus validation, is your downtime for an offline cutover.
• Freeze writes on MySQL, run the final load, validate, then switch the application's driver and connection string to PostgreSQL.
• Keep MySQL unchanged until the agreed rollback window closes; take a mysqldump backup before you start.
• For little downtime, use a replication-based tool instead, such as AWS DMS with full load plus CDC into Amazon RDS or Aurora PostgreSQL. The RDS MySQL vs RDS PostgreSQL comparison covers the managed-service side.

To work with the new database, see pgAdmin or DBeaver, and for a refresher on the SQL used in these checks, the SQL beginner course.

Frequently asked questions

Sources

• pgloader documentation: MySQL to Postgres
• pgloader documentation: Installing pgloader
• pgloader documentation: introduction
• pgloader documentation: tutorial
• PostgreSQL: Date/time functions
• pgloader releases (GitHub)
• AWS DMS User Guide: MySQL to PostgreSQL conversion settings
• MySQL 8.4 Reference Manual: Using AUTO_INCREMENT
• MySQL 8.4 Reference Manual: Numeric type syntax
• MySQL 8.4 Reference Manual: Schema object names
• MySQL 8.4 Reference Manual: INSERT ... ON DUPLICATE KEY UPDATE
• MySQL 8.4 Reference Manual: The ENUM type
• MySQL 8.4 Reference Manual: DATE, DATETIME and TIMESTAMP
• MySQL 8.4 Reference Manual: Server SQL modes
• MySQL 8.4 Reference Manual: Flow control functions
• MySQL 8.4 Reference Manual: Aggregate functions
• MySQL 8.4 Reference Manual: CHECKSUM TABLE
• PostgreSQL: CREATE TABLE
• PostgreSQL: INSERT (ON CONFLICT)
• PostgreSQL: Enumerated types
• PostgreSQL: Lexical structure (identifiers)
• PostgreSQL: Aggregate functions
• PostgreSQL: Conditional expressions
• PostgreSQL: Returning data from modified rows

Checked 8 October 2026.

How we research guides: our editorial method. We link only to official downloads and never host installers.