How to Migrate MySQL to PostgreSQL: A Step-by-Step Guide (2026)

Migrating from MySQL to PostgreSQL is straightforward until you hit the real-world issues: data-type differences, collation and encoding mismatches, SQL syntax that does not port cleanly, large tables, and cutover downtime. This guide is a complete, step-by-step MySQL to PostgreSQL migration runbook. It covers the pre-migration checklist, a full data-type mapping, the SQL syntax differences that break naive migrations, a pgloader walkthrough, validation queries, and troubleshooting.

If you need minimal downtime or want to keep MySQL and PostgreSQL in sync during the cutover, the guide also covers a continuous change data capture (CDC) approach using Estuary, plus alternatives for smaller databases.

Why migrate from MySQL to PostgreSQL

MySQL is a widely used relational database management system (RDBMS) owned by Oracle, valued for its speed and simple setup. As applications grow more complex, teams often reach its limits in analytics, advanced data types, and concurrency, and look to PostgreSQL for a richer feature set.

PostgreSQL is an open-source, object-relational database that supports both relational and JSON querying. Teams typically move for five reasons:

Advanced data types. Native JSONB, arrays, hstore, XML, user-defined types, and geospatial support through PostGIS.

Performance on complex queries. A mature query planner, parallel execution, and multiple index types (B-tree, GIN, GiST, BRIN) handle heavy joins and analytics that strain MySQL.

Data integrity. Full ACID compliance, strict constraint enforcement, and cascading foreign keys.

Concurrency. Multi-Version Concurrency Control (MVCC) lets reads and writes proceed without blocking each other.

Standards and extensibility. Close ANSI SQL alignment plus custom functions and procedural languages such as PL/pgSQL.

PostgreSQL is not always the right move. The learning curve is steeper, MySQL can still win on ultra-high-write or simple OLTP workloads, and PostgreSQL's case-sensitive identifiers can surface issues when migrating a MySQL application that relied on case-insensitive names. For a feature-by-feature breakdown, see PostgreSQL vs MySQL: key differences and when to use each.

Choose your MySQL to PostgreSQL migration method

Not every MySQL to PostgreSQL migration is the same. The right approach depends on how much downtime you can tolerate, how large your database is, and whether you need to keep both systems in sync during the cutover.

Pick pgloader for speed and simplicity when you can accept downtime. Pick CDC plus cutover when you need minimal downtime and a safer, staged transition. Pick dump and restore only when the database is small and downtime is not a concern.

What needs to be converted

A migration is more than copying rows. You are transferring the structure and logic of the database, and each component carries its own risks. Before you start, inventory the following:

Schema objects. Tables, columns, primary and unique keys, indexes (including composite and full-text), and constraints. Note that PostgreSQL enforces a 63-character identifier limit, which can collide with long MySQL index or constraint names.

Data types. MySQL and PostgreSQL implement similar concepts differently; see the mapping below.

Views, triggers, and stored procedures. MySQL procedural code does not port automatically and is typically rewritten in PL/pgSQL.

Auto-increment behavior. MySQL's AUTO_INCREMENT becomes a PostgreSQL sequence via SERIAL or IDENTITY, which changes how primary keys are generated under concurrent inserts.

Charset and collation. Standardize on an encoding (UTF-8 is common) and confirm sorting and comparison behavior matches.

Prerequisites

Confirm these basics before you start. They prevent the most common "it worked halfway and then failed" scenarios.

Versions and access

• A MySQL source you can read from, ideally with a maintenance window if you are doing a one-time migration. If your source is a managed service, the same approach applies to Amazon RDS for MySQL, Amazon Aurora for MySQL, and Google Cloud SQL for MySQL.
• A PostgreSQL instance ready, whether managed or self-hosted.
• Network connectivity from wherever you run the migration (laptop, VM, or CI runner) to both databases.

Permissions checklist

• MySQL (source): a user that can read schema and data across every database you plan to migrate, plus access to information_schema so tools can discover tables and types.
• PostgreSQL (destination): a role that can create database objects (tables, indexes, sequences), write data, and create schemas if you are migrating more than one; and a target database that already exists or that the role can create.

Network and SSL/TLS

• Confirm you can connect to both databases from the migration machine.
• If either database requires SSL/TLS, verify certificates and SSL modes in advance and test a simple connection before running any migration job.

Time, disk, and performance planning

• Downtime window: for a one-time migration, decide how long you can pause writes (minutes vs. hours).
• Storage: ensure PostgreSQL has disk for the full data plus indexes, with headroom during load.
• Performance: for large tables, plan parallel loading (workers/concurrency), create indexes and constraints after the bulk load rather than during it, and run the migration from a machine close to the databases to reduce network bottlenecks.

MySQL to PostgreSQL data-type mapping

Most data types have a direct PostgreSQL equivalent; a handful need conversion. Understand these mappings before you load, because they directly affect data integrity, performance, and query behavior.

Watch for MySQL "zero dates" such as 0000-00-00, which PostgreSQL rejects. Cast them to NULL or a sentinel value during conversion.

Key differences in SQL syntax

Both databases speak SQL, but application queries, functions, and stored logic often need rework. Plan for these conversions:

Stored procedures, triggers, and functions written in MySQL syntax must be reviewed and rewritten in PL/pgSQL. Because PostgreSQL uses MVCC, a COUNT(*) that was fast on a MySQL MyISAM table requires a full scan in PostgreSQL, so revisit any code that assumed a cached count.

Method 1: Migrate MySQL to PostgreSQL using pgloader

pgloader is a free, battle-tested command-line tool for one-time migrations. It connects to both databases, creates tables, loads data, builds indexes, and resets sequences. Use it when you can schedule a maintenance window and want the fastest path to a complete initial migration.

Step 1: Install pgloader

On Debian/Ubuntu:

plaintext language-bash
sudo apt-get update
sudo apt-get install -y pgloader

On macOS (Homebrew)

plaintext language-bash
brew install pgloader

On Windows, run pgloader through WSL (install Ubuntu in WSL, then apt-get) or Docker. WSL is usually the most reliable option for a Windows-based migration.j

Step 2: Create the PostgreSQL database and user

Create a dedicated migration user with permissions to create tables and write data.

plaintext language-sql
CREATE USER migrate_user WITH PASSWORD 'strong_password';
CREATE DATABASE target_db OWNER migrate_user;
GRANT ALL PRIVILEGES ON DATABASE target_db TO migrate_user;

Step 3: Prepare MySQL for a consistent migration

For production data, decide how to handle writes during the load. The simplest path is a maintenance window with writes paused. If you cannot pause writes, use the CDC approach in Method 2. Confirm connectivity to both databases and review the data-type and identifier checks above before running.

Step 4: Create a pgloader load file

A .load file makes migrations repeatable and easier to tune.

Create mysql_to_postgres.load:

plaintext language-lisp
LOAD DATABASE
  FROM mysql://MYSQL_USER:MYSQL_PASSWORD@MYSQL_HOST:3306/MYSQL_DB
  INTO postgresql://PG_USER:PG_PASSWORD@PG_HOST:5432/PG_DB

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

SET work_mem to '64MB',
    maintenance_work_mem to '512MB'

CAST
  type tinyint when (= precision 1) to boolean using tinyint-to-boolean,
  type datetime to timestamptz

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

The CAST section is where you handle MySQL patterns such as TINYINT(1) and datetime behavior. Use include drop only when you are certain you are pointed at the correct target. If you hit constraint issues on the first run, remove foreign keys and add constraints after load.

Step 5: Run pgloader (with logging)

Run the migration using the .load file:

plaintext language-bash
pgloader mysql_to_postgres.load

Capture logs for debugging:

plaintext language-bash
pgloader mysql_to_postgres.load 2>&1 | tee pgloader.log

Method 2: Minimal-downtime migration with CDC

When you cannot afford a long maintenance window, a one-time tool is not enough on its own. The safer production approach is to backfill historical data, keep changes flowing from MySQL to PostgreSQL during the transition, then cut over only after you have validated that PostgreSQL is correct and current.

Estuary is the right-time data platform: you choose when data moves—sub-second, near real-time, or batch—so you can start with a backfill and switch to continuous replication while you validate and plan the cutover.

Estuary also handles much of the conversion work covered above. It automatically creates the destination tables and columns in PostgreSQL and maps each field to an appropriate PostgreSQL column type. Where you need precise control—say, a high-precision NUMERIC or a JSONB column—it supports custom column types via per-field DDL. Its change data capture delivers exactly-once guarantees, so the cutover does not drop or duplicate rows.

Setting up the migration

1. Sign up for Estuary. Create a free account; contact Estuary for organizational-scale needs.

2. Capture MySQL in real time. In the Captures tab, create a new capture, choose the MySQL capture connector, enter your server address and credentials, select tables, and publish.

3. Materialize to PostgreSQL. In the Materialize section, choose the PostgreSQL materialization connector, provide the target credentials, map the captured tables, and publish.

Estuary then handles the continuous replication, keeping new MySQL writes flowing into PostgreSQL until you are ready to switch. You can set this up as a managed MySQL-to-PostgreSQL pipeline; for configuration details, see the Estuary documentation on creating a data flow and the MySQL and PostgreSQL connectors.

This is the same MySQL CDC that runs in production at scale: Connect&GO uses Estuary to move MySQL data with up to 180x lower latency and 4x better productivity. And because pricing is a transparent per-GB rate rather than a monthly-active-rows (MAR) model, the cost stays predictable whether you run the pipeline for a one-week cutover or keep it in place for ongoing sync.

Choose this method when you need near-zero downtime, your database is large enough that re-running a full load is costly, you want time to validate PostgreSQL while data keeps updating, or you need ongoing MySQL to PostgreSQL replication after migration.

Method 3: Alternative approaches

pg_chameleon replicates MySQL into PostgreSQL with a load-plus-replay model. Consider it if you want an open-source replication workflow and are comfortable operating another service. Skip it if you want the simplest one-time move (pgloader is usually faster) or a managed production cutover.

Dump and restore (mysqldump to transform to psql) works for small databases with simple schemas and acceptable downtime. For anything medium-to-large it becomes slow, hard to validate, and painful to retry.

AWS DMS is a common managed option for RDS and Aurora sources; see how it compares in AWS DMS vs Estuary. 

Migrating the other direction? See the Postgres to MySQL migration guide.

Post-migration validation and performance

Do not cut over until you validate. Confirm the migration is complete and correct, then tune for production.

Validate data completeness

• Compare table counts between source and target using information_schema.tables.
• Compare row counts for your largest and most business-critical tables.
• Spot-check 5 to 10 representative application queries against PostgreSQL.
• Verify aggregates (sums and averages) on key tables.
• Check edge cases: NULLs, special characters, large fields, and boolean conversions.

Validate constraints, keys, and sequences

• Confirm primary keys, unique constraints, and foreign keys are present and enforced.
• Check for orphaned records.
• Confirm sequences (the AUTO_INCREMENT equivalents) start above the current maximum so inserts do not collide.

Test performance

• Review execution plans with EXPLAIN / EXPLAIN ANALYZE and adjust indexes or rewrite queries as needed.
• Run REINDEX to optimize indexes and ANALYZE to refresh planner statistics.
• Run realistic workloads to surface slow queries and bottlenecks before traffic moves.

Cutover and rollback

Update application connection strings to PostgreSQL, run a read-and-write smoke test, and monitor error rates and slow queries. Keep MySQL available in read-only mode during the initial cutover window as your rollback option. If critical issues appear, point the application back to MySQL and fix PostgreSQL offline before retrying. For keeping PostgreSQL continuously in sync with downstream systems after cutover, see the complete guide to PostgreSQL change data capture.

Troubleshooting MySQL to PostgreSQL migrations

Conclusion

Migrating from MySQL to PostgreSQL is a strategic upgrade when you need richer data types, stronger integrity, and better performance on complex queries. Whether you choose a fast one-time move with pgloader, a minimal-downtime transition with CDC, or a dump and restore for a small database, the fundamentals are the same: map your data types, account for SQL syntax differences, and validate thoroughly before cutover.

If you need minimal-downtime migration or ongoing MySQL to PostgreSQL sync, the Estuary platform gives you one platform for all data movement: backfill historical data, continuously replicate changes with exactly-once CDC, and cut over on your own schedule. Start building for free or book a demo to scope your migration.

• Create a free account
• Read the docs (Getting Started)
• Join Slack for implementation help
• Contact us if you want guidance