How do I migrate from MySQL to PostgreSQL without downtime?

Migrating from MySQL to PostgreSQL without downtime is usually done as a live, phased migration with continuous replication and a short cutover window (often seconds to minutes). In practice, “zero downtime” means no user-visible outage or only a brief write pause while you switch traffic.

Typical approach

1) Prepare PostgreSQL to match your app

• Recreate schema in PostgreSQL
• Convert data types and SQL differences
• Replace MySQL-specific features:
  • AUTO_INCREMENT → SERIAL/IDENTITY
  • TINYINT(1) → boolean
  • DATETIME/TIMESTAMP nuances
  • ON DUPLICATE KEY UPDATE → ON CONFLICT DO UPDATE
  • LIMIT offset,count → LIMIT count OFFSET offset (both work in Postgres with different syntax expectations)
• Review app SQL for compatibility

2) Seed initial data

Copy the current MySQL dataset into PostgreSQL using a bulk export/import tool:

• pgloader is commonly used for MySQL → PostgreSQL
• Or dump CSV/data and load into Postgres

3) Keep PostgreSQL in sync with MySQL

Use change data capture (CDC) or replication so ongoing MySQL writes are applied to PostgreSQL.

Common options:

• AWS Database Migration Service (DMS) if you’re on AWS
• Debezium + Kafka for event-based CDC
• pgloader for initial load only (not continuous sync)
• Custom binlog-based replication tooling

4) Validate

Before cutover:

• Compare row counts
• Check checksums / sampled records
• Test critical queries
• Run the app in read-only or shadow mode against PostgreSQL if possible

5) Cut over traffic

At cutover:

1. Temporarily stop writes to MySQL or put app in maintenance/read-only mode for a very short time
2. Let replication catch up fully
3. Switch application connections from MySQL to PostgreSQL
4. Monitor closely
5. Keep MySQL as fallback until stable

---

Common migration patterns

Pattern A: Blue/green with dual write

• App writes to both MySQL and PostgreSQL
• Reads gradually move to PostgreSQL
• Once confident, fully switch to PostgreSQL

Pros: minimal cutover risk
Cons: complex, hard to guarantee consistency, especially with failures

Pattern B: CDC replication + brief write freeze

• MySQL remains primary during migration
• Changes stream to PostgreSQL
• At cutover, briefly stop writes, wait for lag to hit zero, switch app

Pros: most common, simpler, reliable
Cons: tiny cutover pause

Pattern C: Shadow reads only

• App still writes to MySQL
• Some reads also query PostgreSQL in parallel for validation
• Once validated, switch reads, then switch writes

Pros: good validation
Cons: more engineering effort

---

Recommended tooling

Fastest/easiest

• pgloader for initial load
• AWS DMS for ongoing replication (if available)

More control

• Debezium to capture MySQL binlog events
• Consumer that applies changes to PostgreSQL

Schema conversion

• pgloader can also help with type mapping
• Manual review is still needed

---

Things that usually break

• Different SQL dialects and reserved words
• Case sensitivity and collations
• Transaction isolation differences
• LAST_INSERT_ID() behavior
• Stored procedures/triggers
• Implicit casts and date handling
• JSON behavior and indexing differences
• Auto-generated IDs and sequences

---

Minimal downtime cutover runbook

1. Deploy PostgreSQL schema and replication pipeline
2. Perform full initial data load
3. Start CDC sync from MySQL binlog
4. Run validation checks until lag is near zero
5. Enter short write-freeze mode
6. Wait for replication to fully catch up
7. Switch app DB config / service endpoint
8. Unfreeze writes
9. Monitor errors, latency, data consistency
10. Keep MySQL read-only for rollback window

---

Best practice

If you want the safest “no downtime” migration:

• Use continuous replication
• Do a small cutover pause
• Have a rollback plan
• Test in staging with production-like data first

If you want, I can also give you:

1. a step-by-step migration plan,
2. a tool recommendation by cloud/provider, or
3. a sample runbook/checklist for your exact setup.