MySQL → PostgreSQL
Move a production MySQL database into PostgreSQL while translating schemas, types, identifiers, SQL, constraints, indexes, views, procedures, users, application behavior, replication, performance, and recovery.
Should you make this move?
Both platforms have a case. Compare what you gain with what you give up before scheduling the cutover.
MySQL
- Ubiquity, operational familiarity, and broad hosting support reduce adoption risk
- A mature data model and query ecosystem supports serious production workloads
- Advanced SQL, extensibility, and consistency features can lag PostgreSQL
- Schema design, migration, tuning, and operations require specialist attention
PostgreSQL
- Powerful SQL, extensions, integrity, and open governance support demanding workloads
- A mature data model and query ecosystem supports serious production workloads
- Tuning, upgrades, and sophisticated features require experienced operators
- Schema design, migration, tuning, and operations require specialist attention
PostgreSQL: Powerful SQL, extensions, integrity, and open governance support demanding workloads. This removes a major source-side concern: Advanced SQL, extensibility, and consistency features can lag PostgreSQL.
What you lose: Ubiquity, operational familiarity, and broad hosting support reduce adoption risk. What you inherit: Tuning, upgrades, and sophisticated features require experienced operators.
Jump to a section
Know the shape of the move.
This timeline assumes
- Up to 500 tables, 1 TB of data, 200 critical queries, and a 30-minute write-freeze objective
- Application and data owners approve a versioned target schema and consistency strategy.
- A production-shaped snapshot and write workload are available for rehearsal.
- The source remains authoritative until lag reaches zero and all invariants pass.
- The migration team records evidence for every blocking verification check.
What survives the move.
“Partial” and “manual” are not footnotes. They are work that must be scheduled and verified.
| Item | Outcome | Impact | What happens | Mitigation |
|---|---|---|---|---|
| MySQL types and automatic values | partial | critical | Unsigned integers, tinyint booleans, zero dates, enum, auto-increment, collations, and timestamp behavior require explicit mappings. | Build boundary fixtures and reject truncation or implicit fallback. |
| SQL syntax, functions, procedures, and triggers | manual | critical | MySQL dialect and stored logic do not execute unchanged in PostgreSQL. | Inventory executed SQL and port it with behavioral tests. |
| Indexes, query plans, and locking | manual | critical | Equivalent data and SQL can have different performance and concurrency behavior. | Benchmark production-shaped reads, writes, transactions, and contention. |
| Online delta and application cutover | manual | critical | Bulk loaders move snapshots; low-downtime writes require a separate change-capture plan. | Rehearse snapshot, CDC, catch-up, freeze, connection switch, and rollback. |
| Schemas, collections, and tables | partial | critical | Structures require explicit relational, document, type, and naming decisions. | Version a signed target schema before loading production data. |
| Rows, documents, and identifiers | partial | critical | Data can move while IDs, generated values, precision, encoding, and null semantics differ. | Preserve stable source IDs and compare deterministic checksums and aggregates. |
| Types and nested values | partial | critical | Source types may coerce, flatten, normalize, or move into JSON columns. | Create type-level fixtures and reject silent truncation. |
| Relationships and references | partial | critical | Foreign keys, embedded documents, polymorphism, and cascades require redesign. | Load parents first and verify orphan and cascade behavior. |
| Indexes and query behavior | manual | critical | Index syntax and query planners differ. | Design from production query evidence and benchmark representative workloads. |
| Constraints, defaults, and generated values | manual | critical | Validation and automatic behavior do not transfer with raw data. | Recreate and test every business invariant. |
| Views, functions, triggers, and procedures | manual | critical | SQL dialects and database runtimes differ. | Port reviewed logic and test side effects and failure paths. |
| Users, roles, and permissions | manual | critical | Authentication and grants are platform-specific. | Apply least privilege before application access. |
Where each thing goes.
| Source | Destination | Method | Notes |
|---|---|---|---|
| MySQL database or base | PostgreSQL database or document | manual | Record owner, region, collation, and source ID. |
| Schema, collection, or table | Destination schema and table | transform | Approve relational and naming semantics. |
| Row or document ID | Primary key and source ID | transform | Preserve stable identity. |
| Source field or column | Destination column | transform | Map type, null, default, precision, and encoding. |
| Nested object or array | Relation, array, or JSONB | transform | Choose query and integrity behavior explicitly. |
| Reference or foreign key | Destination foreign key or relation | transform | Create after parent records exist. |
| Index | Destination index | manual | Design from query evidence. |
| View, formula, or generated value | Destination view, expression, or generated column | manual | Rewrite and test output. |
| Trigger, automation, or integration | Destination trigger or application workflow | manual | Port idempotently and test failure behavior. |
Make the move recoverable.
Create the source-of-truth backup
Preserve MySQL data, configuration, and operating evidence before any destination write.
- Create a native, restorable MySQL backup plus logical schema and data exports.
- Inventory schemas, types, relationships, indexes, logic, users, permissions, replication, queries, jobs, and integrations.
- Record counts, checksums, aggregates, sizes, sequences, lag, performance, and recovery baselines.
- Hash all immutable backup and migration artifacts.
Proof to capture: A signed manifest reconciles every scoped record class, runtime dependency, export file, count, and hash.
Target schema and transformation registry
Define every structural, type, relationship, and logic decision.
- Inventory source values and exceptions.
- Define explicit destination mappings.
- Reject unmapped critical records.
Proof to capture: Save the input, output, command or tool settings, warnings, and final item counts.
Repeatable copy and change-capture pipeline
Load, catch up, verify, and cut over deterministically.
- Normalize encoding, dates, identifiers, and blanks.
- Run a representative pilot.
- Reconcile accepted, rejected, and transformed rows.
Proof to capture: Save the input, output, command or tool settings, warnings, and final item counts.
The things most likely to hurt.
These are operating limits. Treat every “Stop if” condition as a blocked migration, not a suggestion.
Silent type coercion corrupts data
Precision, timezones, encodings, booleans, nested values, or nulls change without a load error.
- Consequence
- Applications make incorrect decisions.
- Mitigation
- Use type fixtures, checksums, aggregates, and invariant queries.
Stop if: Any critical invariant or precision check fails.
Final delta misses writes
Change capture lags, drops unsupported operations, or stops before writers freeze.
- Consequence
- Source and destination diverge permanently.
- Mitigation
- Monitor lag and reconcile a final consistency point.
Stop if: Lag is nonzero or an unexplained delta exists.
Queries pass functionally but fail under load
Indexes and planners differ from the source.
- Consequence
- Cutover causes timeouts or lock contention.
- Mitigation
- Benchmark production-shaped queries and concurrency.
Stop if: Critical latency, error, or lock thresholds are exceeded.
Do the work in this order.
- Days 1–3 · inventory
Inventory and decisions
6–12 hours active2–3 days elapsedOwner review waiting- Inventory MySQL data, features, users, domains, and integrations.
- Approve scope, owners, mappings, and exclusions.
Depends on: MySQL and PostgreSQL administrator access
Stop / go checkpointExport?
Go when: Every critical item has an owner and disposition.
Stop when: Consent, billing, access, or system ownership is unclear.
- Days 3–5 · backup
Backup and reconcile
5–10 hours active1–3 days elapsedExport processing waiting- Create immutable exports and configuration evidence.
- Reconcile counts, totals, and hashes.
Depends on: Approved inventory
Stop / go checkpointTransform?
Go when: Source totals and export manifests agree.
Stop when: Any critical dataset or configuration is absent.
- Days 5–12 · pilot
Map and pilot
10–35 hours active3–8 days elapsedDestination processing and review waiting- Configure PostgreSQL and transform representative data.
- Run a pilot containing normal records and every critical edge case.
Depends on: Verified backup
Stop / go checkpointScale?
Go when: Pilot mappings, behavior, access, and evidence pass.
Stop when: Any critical check fails or produces unexplained variance.
- Days 10–20 · cutover
Final delta and switch
5–25 hours active1–5 days elapsedDNS, import, or sync propagation waiting- Freeze production writes in MySQL.
- Apply the final delta, switch ownership, and run blocking checks.
Depends on: Passed pilot and approved rollback
Stop / go checkpointOpen production?
Go when: Counts reconcile and one destination system owns production.
Stop when: A source writer remains active or a blocking check fails.
- Days 12–40 · observe
Observe and close
3–18 hours active3–10 days elapsedOperating-cycle evidence waiting- Monitor one complete operating cycle.
- Sign the verification report and close rollback only after stability.
Depends on: Verified cutover
Stop / go checkpointClose rollback?
Go when: No trigger occurs during the agreed observation period.
Stop when: Data, access, delivery, routing, or business results regress.
Cut over with a way back.
Cutover
Make PostgreSQL the only production system without losing the final MySQL delta.
- Freeze production writes and scheduled actions in MySQL.
- Export, transform, and reconcile the final delta.
- Apply the approved delta to PostgreSQL.
- Switch domains, forms, integrations, sending, or sync ownership as applicable.
- Run every blocking verification check and keep the source intact.
Proof to capture: PostgreSQL owns production, totals reconcile, and every blocking check has durable evidence.
Rollback
Return production ownership to MySQL without losing destination-era changes.
- Stop new writes and scheduled actions in PostgreSQL.
- Restore the prior MySQL routing, forms, integrations, sending, or sync ownership.
- Export the PostgreSQL post-cutover delta.
- Review and apply safe destination-era changes to MySQL.
- Run the same blocking checks against the restored source.
Proof to capture: MySQL again owns production with current data and no duplicate destination action.
- Unexplained critical count or value variance
- Missing or exposed critical data
- Duplicate production action
- Failed access, routing, delivery, or integration check
- A critical feature has no safe destination replacement
Prove the migration worked.
Every blocking check must pass. Capture the evidence before cleanup begins.
| Pass | ID | Check | Method | Expected result | Evidence |
|---|---|---|---|---|---|
V-01Blocking | Record reconciliation | Compare counts, checksums, aggregates, and stratified records. | Every scoped record is accounted for. | Data ledger. | |
V-02Blocking | Value fidelity | Run type fixtures covering bounds, nulls, precision, Unicode, and timezones. | No silent coercion or truncation. | Type report. | |
V-03Blocking | Reference and constraint integrity | Query parents, children, orphans, duplicates, and cascades. | Every critical invariant holds. | Integrity report. | |
V-04Blocking | Views, formulas, triggers, and procedures | Run approved input and failure cases. | Outputs and side effects match acceptance. | Logic suite. | |
V-05Blocking | Functional and performance parity | Run production-shaped reads and writes under concurrency. | Results and service objectives pass. | Benchmark report. | |
V-06Blocking | Roles and data access | Run allowed and denied operations for every role. | Least privilege matches policy. | Access evidence. | |
V-07Blocking | Backup, restore, and rollback | Restore a destination backup and execute the rollback rehearsal. | Recovery objectives and integrity pass. | Recovery report. | |
V-08Blocking | Replication and write authority | Inspect lag, writers, jobs, and application connections. | PostgreSQL alone accepts approved writes with zero unexplained delta. | Cutover checklist. |
Remove the scaffolding safely.
Safe after: One complete operating cycle, at least seven stable days, and owner sign-off on every blocking check.
- Create final MySQL exports and archive verification evidence.
- Revoke temporary credentials, API keys, webhooks, and elevated roles.
- Remove obsolete embeds, forms, jobs, integrations, and DNS records.
- Keep the source intact through the approved retention window.
- Cancel paid plans only after billing, legal, and recovery review.
- Schedule the next PostgreSQL backup, access, and migration-playbook review.
When the plan met reality.
First-hand accounts are preferred. Vendor case studies are labeled, and every note below is an editorial paraphrase—follow the link for the full context.
A Django practitioner recounts a MySQL-to-PostgreSQL production migration that appeared complete until an old environment was discovered still running Celery workers four or five days later. Those workers continued consuming live customer tasks and writing results into the retired MySQL database. The incident demonstrates that database cutover is incomplete while any scheduler, worker, maintenance job, or legacy application can still write to the old system.
- Inventory and disable every worker, scheduler, webhook consumer, and maintenance process before declaring the old database read-only.
- Monitor both databases for unexpected writes during the rollback window and alert immediately if the source changes after cutover.
- The overlooked Celery environment caused a split-brain data problem several days after the apparently successful migration.
- Keeping the old stack available for rollback is useful only when it is isolated from real production inputs and cannot mutate data.
Verify against the primary material.
Platform behavior changes. Check these sources and the review dates above before executing a production migration.
- PostgreSQL wiki: proper MySQL migrationAccessed 2026-07-19
- pgloader: MySQL to PostgreSQLAccessed 2026-07-19
- MySQL: mysqldump referenceAccessed 2026-07-19