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.
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.
Built to be reviewed.
Tested 2026-07-19. Next scheduled review: 2026-10-19.
- PostgreSQL wiki: proper MySQL migrationAccessed 2026-07-19
- pgloader: MySQL to PostgreSQLAccessed 2026-07-19
- MySQL: mysqldump referenceAccessed 2026-07-19