Migrating a production app from MySQL to PostgreSQL with minimal downtime is usually done as a two-phase migration:

1. Bulk copy existing data
2. Keep MySQL and PostgreSQL in sync until cutover
3. Switch traffic with a short read-only or maintenance window

The safest approach depends on your app’s write volume, schema complexity, and acceptable downtime. Here’s a practical plan.

---

1) Assess compatibility first

Before moving anything, identify MySQL-specific features that won’t translate directly:

• AUTO_INCREMENT → PostgreSQL SERIAL/IDENTITY
• TINYINT(1) booleans
• DATETIME / TIMESTAMP behavior differences
• UNSIGNED types
• ENUM usage
• ON UPDATE CURRENT_TIMESTAMP
• MySQL collations and case-sensitivity differences
• JSON differences and function syntax
• GROUP BY and SQL mode differences
• Backticks vs double quotes
• LIMIT offset, count vs LIMIT count OFFSET offset

Also inspect:

• stored procedures
• triggers
• views
• raw SQL in the application
• ORM-generated SQL
• foreign key behavior and cascade rules

If your app relies heavily on MySQL-specific SQL, plan time for refactoring.

---

2) Choose a migration method

Option A: Dual-write at the application layer

Your app writes to both databases during transition.

Pros

• Simple conceptually
• No special replication infrastructure needed

Cons

• Hard to get correct
• Risk of drift if one write fails
• More app complexity

Best only if your app is already built for this.

---

Option B: Logical replication / CDC

Use a change-data-capture tool to replicate ongoing changes from MySQL to PostgreSQL after an initial bulk load.

Common tools:

• AWS DMS
• Debezium + Kafka
• pgloader for initial load plus separate CDC strategy
• Custom binlog consumer

Pros

• Minimal downtime
• Lower app changes
• Good for production migrations

Cons

• More operational complexity
• Need careful validation and cutover planning

This is usually the best choice.

---

Option C: Downtime migration

Stop writes, export data, import into PostgreSQL, switch app.

Pros

• Simplest

Cons

• More downtime

Only acceptable if the app can tolerate it.

---

3) Build the PostgreSQL schema first

Do not just copy tables blindly. Recreate the schema in PostgreSQL thoughtfully:

• Convert data types appropriately
• Recreate indexes, constraints, foreign keys
• Review default values and generated columns
• Ensure sequence/identity settings match the max existing IDs
• Migrate views, triggers, and procedures separately
• Rework any MySQL-specific SQL logic

Tools that can help:

• pgloader
• MySQL Workbench migration
• Schema conversion scripts
• ORMs/migrations if you already use them

But always review the generated schema manually.

---

4) Do an initial bulk data load

Load the current MySQL data into PostgreSQL.

Typical approaches:

• pgloader: popular for MySQL → PostgreSQL
• CSV export/import
• ETL tools
• Cloud migration services

Important details:

• Disable or defer foreign key checks during load if needed
• Load parent tables before child tables
• After load, reset sequences to the correct max values
• Verify row counts and checksums

Example sequence:

1. Create schema in PostgreSQL
2. Bulk load tables
3. Rebuild indexes if needed
4. Reset sequences
5. Run validation

---

5) Set up ongoing sync

To minimize downtime, keep PostgreSQL updated after the initial load.

Common strategies

MySQL binlog → PostgreSQL

Use a CDC tool to stream changes from MySQL binlogs into PostgreSQL.

Requirements:

• MySQL binary logging enabled
• Row-based binlog format preferred
• Reliable replication slot / connector / job

Application dual-write

Start writing to both systems while validating PostgreSQL in parallel.

This is riskier, but sometimes useful if you can’t use CDC tools.

---

6) Validate the PostgreSQL copy

Before cutover, compare MySQL and PostgreSQL:

• Table row counts
• Sampled data comparisons
• Checksums/hashes by chunk or primary key ranges
• Key business queries and reports
• Edge cases: nulls, dates, decimals, Unicode, blobs

Also test:

• login/session flows
• writes and updates
• deletes and cascades
• background jobs
• search/reporting queries
• performance under load

If possible, run the app in a staging environment against PostgreSQL first.

---

7) Prepare the app for cutover

Make the application database-agnostic as much as possible:

• Use a connection abstraction / feature flag
• Externalize DB config
• Remove MySQL-only SQL
• Add PostgreSQL-specific connection settings
• Check transaction isolation assumptions
• Verify retry logic and deadlock handling

A feature flag or runtime config switch is ideal so you can swap DB endpoints quickly.

---

8) Cutover plan

A typical low-downtime cutover looks like this:

Step-by-step

1. Announce a short maintenance window
2. Stop or pause writes in the app
3. Wait for replication lag to reach zero
4. Run final sync / apply remaining binlog events
5. Verify data consistency
6. Point the app to PostgreSQL
7. Bring the app back up
8. Monitor closely

If you can’t pause writes cleanly, at least make the app read-only briefly during the final sync.

---

9) Monitor after cutover

Watch:

• error rates
• slow queries
• connection pool exhaustion
• deadlocks
• replication/CDC health if still running
• CPU, memory, disk I/O
• unexpected data anomalies

Keep MySQL around for rollback for a while, but once writes move to PostgreSQL, rollback becomes harder because MySQL will no longer be current unless you keep dual-writing.

---

10) Rollback strategy

Have a rollback plan before you start.

Typical safe rollback:

• Keep MySQL source of truth until cutover
• If PostgreSQL fails before writes are switched, revert app config to MySQL
• If you switch writes to PostgreSQL, rollback is much harder unless you maintain reverse replication or dual-write

For production migrations, define:

• rollback trigger conditions
• who decides
• exact steps
• how long to wait before declaring success

---

Recommended practical migration path

For most production apps, the best balance of safety and low downtime is:

1. Fix MySQL-specific SQL in the app
2. Create PostgreSQL schema
3. Bulk load data
4. Enable CDC from MySQL to PostgreSQL
5. Validate continuously
6. Freeze writes briefly
7. Catch up replication
8. Switch app to PostgreSQL
9. Monitor and keep MySQL as fallback for a short period

---

Common pitfalls

• Forgetting to convert timestamps/time zones correctly
• Not resetting sequences after bulk load
• Missing triggers or stored procedures
• Assuming identical SQL behavior
• Ignoring collations and string comparison differences
• Poor validation on decimals and large integers
• Not testing under production-like load
• Underestimating query plan differences in PostgreSQL
• Using SELECT * everywhere and then getting surprises from type changes

---

Tool suggestions

Depending on your environment:

• pgloader: great for initial MySQL → PostgreSQL loads
• AWS DMS: strong for managed CDC and minimal downtime
• Debezium: good if you already use Kafka
• Schema conversion tools: helpful, but always review output manually

---

A simple rule of thumb

If the app must stay online and you need low risk:

• Use CDC for ongoing sync
• Do a short read-only freeze for final cutover
• Validate heavily before switching traffic

---

If you want, I can give you:

1. a concrete migration checklist,
2. a MySQL-to-PostgreSQL compatibility cheat sheet, or
3. a cutover runbook you can follow in production.