This article lays out the cutover method we are using for a live learning platform moving from MongoDB to PostgreSQL: audit the current data, make the target schema explicit, rehearse a repeatable ETL, verify the result, and keep a rollback path. The move is not complete, so this is a working plan, not a report of a successful production cutover.
Why we are moving, and why not in a weekend
A document model can hold relationships in fields and embedded data, but a live application often needs clear integrity rules across users, courses, enrollments, messages, and progress. A relational schema can enforce some of those relationships directly. Choosing PostgreSQL does not automatically fix the data; the team must decide which relationships become constraints and how each legacy value maps.
But a live system cannot stop for a migration. The move has to be staged so that at every moment there is a working system and a way back.
Audit the schema you have
Before designing the target, inventory collections and fields, then trace each relationship through its readers and writers. Record the identifier type, optionality, delete semantics, and how authentication treats it. String identifiers, object references, arrays, embedded subdocuments, and polymorphic links require different migration decisions. A document export cannot decide those rules for you.
Design the target schema and ID map
A relational target should make its guarantees explicit: normalized tables and foreign keys for relationships that need them, and JSONB only for deliberately variable data. Preserve legacy identifiers in a unique migration column or an id map until reconciliation is complete. Document how each source identifier maps to its target; do not infer the mapping from matching values.
Two supporting records make repeatable ETL easier to reconcile. A source-to-target id map records the source collection and identifier alongside the target table and key. A snapshot or checksum of the source record gives the team an independent basis for comparison. These aids support investigation; they do not replace reference and orphan checks.
The ETL: repeatable, honest, and loud
A migration runner should be repeatable, take a lock so concurrent runs cannot collide, validate required references before writing, process bounded batches, and count what it reads and writes. Finish with orphan checks and fail when an invariant is broken. A dry run should validate without writing. Diagnostic bypasses, if they exist, do not belong in a cutover run.
SELECT status, count(*) FROM migration_runs GROUP BY status;
SELECT source_collection, count(*) FROM migration_id_maps GROUP BY 1;
-- Both of these must return zero:
SELECT count(*) FROM course_enrollments ce
LEFT JOIN users u ON u.id = ce.user_id
LEFT JOIN courses c ON c.id = ce.course_id
WHERE u.id IS NULL OR c.id IS NULL;Rehearse twice, cut over once
Keep the current database authoritative while the target schema and application path are prepared. Rehearse the ETL against a production-like copy and reconcile counts and references on each run. At the maintenance window, stop writes, run the final ETL with every required check enabled, archive its output, switch the application to the target, and walk through the critical workflows. Shift traffic only after the checks pass and the rollback owner is ready.
The runbook should name the critical paths to verify, such as sign-in, roles, course access, enrollment, progress, grading, messaging, payment callbacks, uploads, and administration. Follow the manual checks with the contract tests that apply to the system. During any traffic shift, monitor application errors, database capacity, and row-count drift against the recorded baseline.
The rollback you write before you need it
Write rollback before cutover. If the ETL fails, do not switch over. If application behavior fails after the switch, return to the prior provider only under a documented reconciliation policy. Any writes accepted by the target after cutover must be preserved or reconciled before the source becomes authoritative again. Without a write-consistency plan, there is no safe automatic rollback.
- Non-zero orphan counts: stop and roll back.
- Unexplained source and target count differences: stop and roll back.
- Widespread login or refresh failures, payment callback failures, or role escalation: stop and roll back.
- MongoDB stays read-only and retained until reconciliation and sign-off, however well the cutover went.
Cut over on evidence
Do not call a migration complete because its schema and scripts exist. Completion requires reconciled source and target counts, zero unexplained orphans, exercised authentication and role checks, verified critical workflows, repeatable migration output, and an owner for rollback. The cutover remains in progress until those checks pass and production data is confirmed against the target.