MySQL to PostgreSQL Migration with Zero Downtime: Step-by-Step Guide 2026

Introduction: Why Companies Are Migrating from MySQL to PostgreSQL in 2026

By 2026, PostgreSQL has firmly claimed the top spot among open-source relational databases on the DB-Engines popularity index. The reasons for switching from MySQL to PostgreSQL are both technical and strategic.

First, PostgreSQL offers a richer type system: native JSONB with indexing support, arrays, range types, full-text search, and extensibility through extensions (PostGIS, pgvector, TimescaleDB). MySQL falls significantly short in this regard.

Second, PostgreSQL strictly adheres to the SQL standard. This eliminates surprises with complex queries: window functions, recursive CTEs, and LATERAL JOIN all behave predictably. MySQL has historically had weak support for these constructs and a number of odd defaults (such as strict mode being disabled by default in older versions).

Third, Oracle's licensing policies around MySQL are prompting companies to reconsider vendor lock-in risks. PostgreSQL is licensed under its own permissive open-source license with no restrictions on commercial use.

Finally, cloud providers (AWS Aurora PostgreSQL, Google AlloyDB, Azure Flexible Server) are actively investing in the PostgreSQL ecosystem, offering increasingly mature managed solutions.

The main challenge of migration isn't the syntax differences — it's the need to do it without any service interruption. Production systems cannot afford even an hour of downtime. This article walks through the full cycle of zero-downtime migration from MySQL to PostgreSQL: from scoping the work to the final traffic cutover.

Assessing the Migration Scope

Before writing the first command, you need to audit your current database. This is the most important and often underestimated phase.

What to Inventory

Data types: TINYINT(1) is used in MySQL as a boolean — PostgreSQL has a native BOOLEAN type. DATETIME vs TIMESTAMP WITH TIME ZONE. ENUM is implemented differently in MySQL and PostgreSQL. UNSIGNED INT does not exist in PostgreSQL.
Stored procedures and functions: MySQL procedures are written in a dialect incompatible with PL/pgSQL. Every procedure will need to be rewritten by hand.
Triggers: the syntax is fundamentally different. In PostgreSQL, a trigger calls a separate function rather than containing logic inline.
Dialect-specific syntax: GROUP BY is less strict in MySQL, INSERT ... ON DUPLICATE KEY UPDATE becomes INSERT ... ON CONFLICT, and LIMIT x, y becomes LIMIT y OFFSET x.
Character encoding: utf8 in MySQL is actually a 3-byte UTF-8 variant. The true 4-byte encoding is utf8mb4. PostgreSQL uses standard UTF-8.
Collation: string sorting rules may differ, which affects indexes and query result ordering.

To quickly assess the scope of work, run the following in MySQL:

-- List all tables with data types
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- Stored procedures
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_db';

Based on the audit, create a risk matrix: which objects require manual work, which can be migrated automatically, and which carry the highest regression risk.

Zero-Downtime Migration Strategy

For production systems, there are two primary strategies that can be combined.

Dual-Write

The application writes to both databases simultaneously — MySQL and PostgreSQL — while continuing to read from MySQL (the old database). Data accumulates in PostgreSQL. After verification, reads are switched to PostgreSQL, and then writes to MySQL are disabled.

Pros: full control at the application level, easy rollback. Cons: requires application code changes, risk of data divergence if writes to one database fail.

CDC (Change Data Capture)

This approach is based on reading the MySQL binary log (binlog) and applying changes to PostgreSQL in real time. It does not require application code changes at the initial stage.

A typical CDC migration stack:

Take an initial snapshot: transfer data from MySQL to PostgreSQL using pgLoader or AWS DMS.
Start a CDC agent (Debezium) that reads the MySQL binlog and publishes change events to Kafka.
A Kafka Consumer applies changes to PostgreSQL.
Once PostgreSQL has caught up with MySQL — switch traffic over.

Combined strategy: use CDC for data synchronization + dual-write at the final stage as a safety net before cut-over.

Migration Tools: Comparison

pgLoader

pgLoader is a specialized tool for loading data into PostgreSQL. It converts data types on the fly and performs well thanks to the COPY protocol.

LOAD DATABASE
  FROM mysql://user:password@mysql-host/source_db
  INTO postgresql://user:password@pg-host/target_db

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

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

ALTER SCHEMA 'source_db' RENAME TO 'public';

pgLoader is well suited for initial loads but is not designed for live CDC.

AWS DMS (Database Migration Service)

A managed service from Amazon. Supports both full load and CDC mode from MySQL to PostgreSQL (including Aurora). Easy to configure through the UI, but has limitations: it does not migrate stored procedures, and some data types require manual mapping configuration. It makes sense if you're already operating within the AWS ecosystem.

Debezium

An open-source CDC platform built on Apache Kafka Connect. It reads the MySQL binlog and publishes change events. It is the most flexible and production-proven tool for continuous synchronization.

Custom Scripts

Justified only for small databases or specific transformation logic. For large production systems, the risk of errors is too high.

Recommendation: for most projects, the optimal stack is pgLoader (initial load) + Debezium + Kafka (CDC). If you're on AWS, consider DMS as an alternative to Debezium.

Schema Migration: Key Differences

AUTO_INCREMENT vs SEQUENCES

In MySQL, a primary key with auto-increment is declared as follows:

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

In PostgreSQL, the SERIAL type or the more modern GENERATED ALWAYS AS IDENTITY is used:

CREATE TABLE orders (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- Or using a separate sequence:
CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
  id INTEGER NOT NULL DEFAULT nextval('orders_id_seq') PRIMARY KEY
);

After the initial load, don't forget to reset the sequence to the correct value:

SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));

JSON vs JSONB

MySQL stores JSON as text with validation. PostgreSQL offers two types: JSON (stored as text, parsed on every query) and JSONB (binary format, supports GIN indexes). Always use JSONB in production:

-- MySQL
CREATE TABLE events (
  payload JSON
);

-- PostgreSQL
CREATE TABLE events (
  payload JSONB
);

-- Creating a GIN index for fast JSONB lookups
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Querying JSONB
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';

Other Critical Data Type Differences

TINYINT(1) → BOOLEAN
DATETIME → TIMESTAMP (be mindful of timezone!)
TEXT / MEDIUMTEXT / LONGTEXT → TEXT (PostgreSQL has no size limit on TEXT)
UNSIGNED INT → consider BIGINT or NUMERIC
ENUM('a','b') → PostgreSQL ENUM (requires creating a type) or VARCHAR with a CHECK constraint

Data Synchronization via CDC with Debezium

Make sure MySQL is configured for binlog replication:

# my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7

Debezium MySQL Connector configuration (published to Kafka Connect):

{
  "name": "mysql-source-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "mysql-host",
    "database.port": "3306",
    "database.user": "debezium",
    "database.password": "secret",
    "database.server.id": "184054",
    "topic.prefix": "myapp",
    "database.include.list": "source_db",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-changes.myapp",
    "include.schema.changes": "true",
    "snapshot.mode": "initial"
  }
}

A Kafka Consumer on the PostgreSQL side applies the changes. You can use the ready-made JDBC Sink Connector for this, or write a consumer in Go/Python that translates Debezium events into SQL commands for PostgreSQL.

Running everything in Docker for development and testing:

version: '3.8'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.5.0
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181

  kafka:
    image: confluentinc/cp-kafka:7.5.0
    depends_on: [zookeeper]
    environment:
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092

  kafka-connect:
    image: debezium/connect:2.5
    depends_on: [kafka]
    ports:
      - "8083:8083"
    environment:
      BOOTSTRAP_SERVERS: kafka:9092
      GROUP_ID: 1
      CONFIG_STORAGE_TOPIC: connect_configs
      OFFSET_STORAGE_TOPIC: connect_offsets

Adapting the Application

Even when using an ORM, changes will be required. The main issues are:

Identifier Case Sensitivity

MySQL is case-insensitive for table names (on Windows/macOS). PostgreSQL is case-sensitive and folds all unquoted identifiers to lowercase. If your code has SELECT * FROM Users — this will work in MySQL but fail in PostgreSQL (which expects the table users).

Strict GROUP BY Mode

PostgreSQL requires all non-aggregated columns in SELECT to appear in the GROUP BY clause:

-- Works in MySQL (non-strict), but not in PostgreSQL
SELECT user_id, email, COUNT(*) FROM orders GROUP BY user_id;

-- Correct for PostgreSQL
SELECT user_id, MAX(email), COUNT(*) FROM orders GROUP BY user_id;

INSERT ... ON CONFLICT

-- MySQL
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- PostgreSQL
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

ORM-Specific Considerations

If you use Laravel with Eloquent — after switching to PostgreSQL, review all raw queries (DB::raw()). In Django, make sure there are no MySQL-specific annotations. In Go with GORM or sqlx, check your placeholders: MySQL uses ?, PostgreSQL uses $1, $2, ....

For safe code adaptation, add a dedicated CI/CD stage that runs tests against PostgreSQL before the final cutover.

Testing the Migration

Data Comparison

After the initial load and once CDC synchronization has stabilized, you need to verify data consistency:

-- Compare row counts
SELECT 'mysql' as source, COUNT(*) FROM orders
UNION ALL
SELECT 'postgres' as source, COUNT(*) FROM orders;

-- Compare checksums (sampled)
SELECT MD5(CAST(id AS TEXT) || amount || status)
FROM orders
ORDER BY id
LIMIT 10000;

Use tools like pt-table-checksum (Percona Toolkit) adapted to your setup, or write a custom comparison script that spot-checks records by primary key between the two databases.

Load Testing

Before the cut-over, run a load test against PostgreSQL using a realistic traffic profile. Use tools such as k6, Gatling, or pgbench. Verify:

Response time for key queries (should be no worse than MySQL).
Behavior under peak load.
Memory and connection usage (pg_stat_activity).
Presence of slow queries (pg_stat_statements, auto_explain).

Final Cutover

The cut-over is the most critical moment. Minimize the risk window by preparing a thorough plan.

Cut-Over Plan

T-7 days: CDC is running, replication lag is consistently under 1 second. All tests are green.
T-1 day: notify the team. Confirm that an on-call DBA and backend developer are available.
T=0 (cut-over window): enable read-only mode at the application level (via a feature flag or maintenance mode). Wait for CDC to fully apply any pending events (lag = 0). Switch the connection string in the configuration (or via service discovery) to PostgreSQL. Disable read-only mode. Check key metrics and health checks.
T+15 minutes: monitor error rates and response times. If everything looks good — you're done.

Rollback If Issues Arise

If critical issues are discovered after the switch:

Immediately switch the connection string back to MySQL.
MySQL has remained in a working state — data written during the PostgreSQL window will be lost, but this amounts to only minutes of data.
If dual-write was used — there is no data loss at all.

Keep MySQL running for at least 2 weeks after a successful cut-over — in case any hidden issues surface later.

Post-Migration: Optimization and Monitoring

PostgreSQL requires a different maintenance approach than MySQL.

VACUUM and ANALYZE

PostgreSQL uses MVCC, which causes dead rows to accumulate. Make sure autovacuum is properly tuned for your workload profile:

-- Check autovacuum statistics
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Indexes

Revisit your indexing strategy. PostgreSQL supports: B-tree, Hash, GIN (for JSONB, arrays, full-text search), GiST, and BRIN (for time-series data). Using the right index type can yield significant performance gains.

Monitoring

Enable pg_stat_statements to analyze the top slow queries. Set up alerts for table bloat, long-running transactions, and WAL size. Prometheus + postgres_exporter is the standard PostgreSQL monitoring stack in 2026.

Cleanup

After 2 weeks of stable operation on PostgreSQL:

Stop the CDC pipeline.
Remove the Debezium connector and migration Kafka topics.
Shut down the MySQL server.
Remove MySQL from the infrastructure or move it to an archive state.

Conclusion: Checklist for a Successful Migration

✅ Full schema audit completed: data types, stored procedures, dialect-specific syntax.
✅ Strategy defined: CDC (Debezium) + optional dual-write at the final stage.
✅ MySQL binlog configured (ROW format).
✅ Initial load completed via pgLoader, sequences reset to correct values.
✅ CDC synchronization is running and lag is being monitored.
✅ PostgreSQL schema adapted: JSONB instead of JSON, IDENTITY instead of AUTO_INCREMENT, correct data types.
✅ Application code adapted: placeholders, GROUP BY, ON CONFLICT, identifier casing.
✅ CI/CD pipeline includes a testing stage against PostgreSQL.
✅ Load test on PostgreSQL passed with results on par with MySQL.
✅ Cut-over plan documented and agreed upon by the team.
✅ Rollback tested in the staging environment.
✅ Post-cut-over: autovacuum, monitoring, and pg_stat_statements configured.
✅ MySQL kept running for at least 2 weeks after the successful switch.

Migrating from MySQL to PostgreSQL is an investment that pays off: a richer type system, strict SQL standard compliance, an active extension ecosystem, and strong support from cloud providers. With proper planning, it is entirely achievable without a single minute of downtime for your users.

Migrating from MySQL to PostgreSQL: A Step-by-Step Zero-Downtime Guide for Production Systems