Migrating from MongoDB to PostgreSQL

Unlike MySQL-to-PostgreSQL, this is not a dialect translation — it's a data model change. There is no pgloader one-liner, because a tool can't decide for you which parts of your documents become columns, which become rows in a child table, and which stay as JSON. That decision is the migration.

The honest first question: why are you moving? Good reasons are real ones — you need transactions across entities without ceremony, your "documents" turned out to be rows with joins hand-rolled in application code, licensing (SSPL) blocks you, or you're consolidating engines. If your workload is genuinely document-shaped and it's working, MongoDB 8.0 is supported into 2029 and staying is a legitimate answer. And if your only problem is the license or the hosting bill, FerretDB (v2.7.0 as of November 2025) puts a MongoDB 5.0+ wire-protocol proxy in front of PostgreSQL with the DocumentDB extension — your app keeps its MongoDB driver, no rewrite. That's a different project than the one this guide covers, but for license-driven migrations it's usually the cheaper one.

The Strategy: Land in JSONB, Normalize Incrementally

Trying to design the perfect relational schema and move the data and rewrite the queries in one step is how these projects fail. The pattern that works:

1. Land every collection as a two-column table: id + doc jsonb.
2. Verify counts and checksums against MongoDB.
3. Normalize hot paths into real columns/tables, collection by collection, while the app reads jsonb.
4. Rewrite queries as each collection normalizes.

PostgreSQL's jsonb with GIN indexes is a good enough document store that step 1 alone gets you a working system — WHERE doc @> '{"status": "active"}' uses the index. You migrate under load, not during a big-bang rewrite.

Step 1: Export — the Extended JSON Trap

mongoexport writes Extended JSON, and its two modes will shape everything downstream. Relaxed mode writes {"qty": 5}; canonical mode writes {"qty": {"$numberInt": "5"}} — every value wrapped in a type annotation. Canonical preserves type fidelity (you can tell an int from a long from a double); relaxed produces JSON that's directly usable in jsonb queries. For most migrations, export relaxed and handle the few types that need care (dates, Decimal128, ObjectId) explicitly:

mongoexport --uri="mongodb://localhost/shop" --collection=orders \
  --jsonFormat=relaxed --out=orders.jsonl

Even in relaxed mode, non-representable types keep their wrappers: dates arrive as {"$date": "2026-07-18T08:00:00Z"}, ObjectIds as {"$oid": "65f..."}, Decimal128 as {"$numberDecimal": "19.99"}. Your load step must unwrap them — see below.

Load the JSONL with COPY:

CREATE TABLE orders_raw (doc jsonb);
\copy orders_raw (doc) FROM 'orders.jsonl' WITH (FORMAT text);

One gotcha: \copy ... FORMAT text treats backslashes as escapes. If your documents contain literal backslashes, load via COPY ... FROM STDIN WITH (FORMAT csv, QUOTE E'\x01', DELIMITER E'\x02') or a small script that inserts line-by-line.

Alternative: mongo_fdw

mongo_fdw (5.5.3 as of September 2025) maps collections to foreign tables so you can pull data with INSERT ... SELECT from the PostgreSQL side, including WHERE-clause pushdown. It's the right tool for selective or repeated pulls; for a full one-shot migration, mongoexport + COPY is simpler and faster.

Step 2: Unwrap the Types

A normalization pass looks like:

CREATE TABLE orders (
  mongo_id     text PRIMARY KEY,
  customer_id  text NOT NULL,
  status       text NOT NULL,
  total        numeric(12,2) NOT NULL,
  created_at   timestamptz NOT NULL,
  items        jsonb NOT NULL  -- normalize later if needed
);
 
INSERT INTO orders
SELECT doc->'_id'->>'$oid',
       doc->'customer'->>'$oid',
       doc->>'status',
       (doc->'total'->>'$numberDecimal')::numeric,
       (doc->'createdAt'->>'$date')::timestamptz,
       doc->'items'
FROM orders_raw;

The NOT NULL constraints will fail on documents missing fields — that's the point. Schemaless collections accumulate variants; find them first with SELECT DISTINCT jsonb_object_keys(doc) FROM orders_raw and decide per field whether missing means NULL, default, or data bug.

Step 3: Query Translation

Indexing translates directly: MongoDB compound index (ESR rule) → PostgreSQL composite B-tree; text index → tsvector + GIN; TTL index → a scheduled DELETE (pg_cron) — PostgreSQL has no per-row expiry.

Cutover and Validation

Same discipline as any migration: freeze writes (or run a dual-write window), final sync, point the app, keep MongoDB read-only as rollback. Validate with aggregates, not just counts:

-- PostgreSQL
SELECT COUNT(*), SUM(total), MIN(created_at), MAX(created_at) FROM orders;

// MongoDB
db.orders.aggregate([{ $group: { _id: null, n: { $sum: 1 },
  total: { $sum: "$total" }, min: { $min: "$createdAt" }, max: { $max: "$createdAt" } } }])

Running both and diffing is where a client that speaks both protocols earns its keep — Mako connects to MongoDB and PostgreSQL side-by-side, so the comparison is two tabs, not two terminals.

Common Mistakes

For the underlying comparison, see PostgreSQL vs MongoDB. Our MongoDB how-to series documents the query language you're leaving, and the PostgreSQL connection guides cover re-pointing your application.

Mako connects to MongoDB and PostgreSQL side-by-side with AI-powered autocomplete — useful for validating a migration query-by-query. Try it free at mako.ai.