---
# === IDENTITY ===
id: software/migrations/mongodb-to-postgresql/2026
canonical_question: "How do I migrate from MongoDB to PostgreSQL?"
aliases:
  - "MongoDB to PostgreSQL migration"
  - "convert MongoDB to Postgres"
  - "document DB to relational migration"
  - "MongoDB to Postgres guide"
  - "move data from MongoDB to PostgreSQL"
  - "migrate NoSQL to SQL"
  - "MongoDB BSON to PostgreSQL JSONB"
entity_type: software_reference
domain: software > migrations > mongodb_to_postgresql
region: global
jurisdiction: global
temporal_scope: 2020-2026

# === VERIFICATION ===
last_verified: 2026-05-17
confidence: 0.90
version: 2.1
first_published: 2026-02-17

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "AWS DMS 3.5.4+ — MongoDB 7.0/8.0 + PostgreSQL 17/18 target support (2026-Q1)"
  next_review: 2026-11-13
  change_sensitivity: medium

# === CONSTRAINTS ===
constraints:
  - "Never run a production MongoDB-to-PostgreSQL migration without first auditing field type variance across at least 10% of documents — MongoDB collections routinely store the same field as different types (string vs number)"
  - "Always preserve the original MongoDB ObjectId in a reference column (mongo_id) during migration — you will need it for cross-referencing, debugging, and rollback"
  - "Use PostgreSQL COPY protocol (not individual INSERTs) for bulk loading — COPY is 5-10x faster and avoids transaction overhead on large datasets"
  - "Disable foreign key constraints during bulk load (ALTER TABLE ... DISABLE TRIGGER ALL) and re-enable + validate after — FK violations during load are the #1 cause of failed migrations"
  - "MongoDB Decimal128 must map to PostgreSQL NUMERIC, not FLOAT — using FLOAT silently loses precision on financial data"
  - "AWS DMS document mode stores entire documents in a single _doc JSONB column — this requires a second normalization pass and is not a complete migration on its own"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "User wants to migrate MySQL to PostgreSQL (relational-to-relational, not MongoDB)"
    use_instead: "software/migrations/mysql-to-postgresql/2026"
  - condition: "User wants to migrate Oracle to PostgreSQL"
    use_instead: "software/migrations/oracle-to-postgresql/2026"
  - condition: "User wants to swap the ORM (Mongoose to Prisma) while keeping the same database"
    use_instead: "software/migrations/mongoose-to-prisma/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: downtime_tolerance
    question: "Is downtime acceptable during the migration?"
    type: choice
    options: ["Yes — maintenance window is fine", "No — must be zero-downtime"]
  - key: data_volume
    question: "How large is the MongoDB dataset?"
    type: choice
    options: ["Small (<10 GB)", "Medium (10-100 GB)", "Large (>100 GB)"]
  - key: schema_variability
    question: "Do documents within the same collection have highly variable schemas?"
    type: choice
    options: ["Yes — many optional/dynamic fields", "No — mostly consistent structure", "Not sure"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/migrations/mongodb-to-postgresql/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-05-17)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/migrations/mysql-to-postgresql/2026"
      label: "MySQL to PostgreSQL Migration"
    - id: "software/migrations/oracle-to-postgresql/2026"
      label: "Oracle to PostgreSQL Migration"
    - id: "software/migrations/sqlserver-to-postgresql/2026"
      label: "SQL Server to PostgreSQL Migration"
    - id: "software/migrations/mongoose-to-prisma/2026"
      label: "Mongoose to Prisma Migration"
  alternative_to:
    - id: "software/migrations/mysql-to-postgresql/2026"
      label: "MySQL to PostgreSQL (homogeneous relational migration)"
  often_confused_with:
    - id: "software/migrations/mongoose-to-prisma/2026"
      label: "Mongoose to Prisma (ORM swap, not database migration)"

# === SOURCES (10 authoritative sources) ===
# Types: official_docs, technical_blog, rfc_spec, academic_paper, community_resource, industry_report
# Reliability: high, moderate_high, moderate, moderate_low, low, authoritative
sources:
  - id: src1
    title: "JSON Types -- PostgreSQL 18 Documentation"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/datatype-json.html
    type: official_docs
    published: 2025-09-25
    reliability: authoritative
  - id: src2
    title: "JSON Functions and Operators -- PostgreSQL 18 Documentation"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/functions-json.html
    type: official_docs
    published: 2025-09-25
    reliability: authoritative
  - id: src3
    title: "Using MongoDB as a source for AWS DMS"
    author: Amazon Web Services
    url: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MongoDB.html
    type: official_docs
    published: 2025-04-01
    reliability: high
  - id: src4
    title: "Moving Data from MongoDB to PostgreSQL: 3 Methods & Steps"
    author: Estuary
    url: https://estuary.dev/blog/mongodb-to-postgresql/
    type: technical_blog
    published: 2025-03-10
    reliability: high
  - id: src5
    title: "Migrating Data from MongoDB to PostgreSQL: Best Practices and Guide"
    author: Coefficient
    url: https://coefficient.io/postgresql/migrate-mongodb-to-postgresql
    type: technical_blog
    published: 2025-06-20
    reliability: moderate_high
  - id: src6
    title: "ADR013 -- MongoDB to PostgreSQL: Edition ID to be UUID"
    author: GOV.UK
    url: https://docs.publishing.service.gov.uk/repos/publisher/arch/adr-013-mongo-to-postgres-edition-uuid-foriegn-key.html
    type: community_resource
    published: 2024-08-15
    reliability: moderate_high
  - id: src7
    title: "Converting from other Databases to PostgreSQL"
    author: PostgreSQL Wiki
    url: https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
    type: community_resource
    published: 2024-11-01
    reliability: moderate_high
  - id: src8
    title: "MongoDB to Postgres Migration: Proven Methods, Key Steps, and a Zero-Downtime Strategy"
    author: Entrans.ai
    url: https://www.entrans.ai/blog/mongodb-to-postgres-migration
    type: technical_blog
    published: 2025-08-15
    reliability: moderate_high
  - id: src9
    title: "AWS DMS release notes -- MongoDB 7.0/8.0 + PostgreSQL 17/18 target support (3.5.4+)"
    author: Amazon Web Services
    url: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReleaseNotes.html
    type: official_docs
    published: 2026-03-12
    reliability: authoritative
  - id: src10
    title: "Database Migration Done Fast: MongoDB to PostgreSQL + pgvector (2026 Buyer's Guide)"
    author: GroovyWeb
    url: https://www.groovyweb.co/blog/database-migration-mongodb-postgresql-pgvector-2026
    type: technical_blog
    published: 2026-02-08
    reliability: moderate
---

# How to Migrate from MongoDB to PostgreSQL

## TL;DR

- **Bottom line**: Export MongoDB collections with `mongoexport`, design a relational or hybrid (JSONB) schema in PostgreSQL, transform nested documents into rows or JSONB columns, and load with `\copy` or a migration script -- use AWS DMS or CDC pipelines for zero-downtime migrations.
- **Key tool/command**: `mongoexport --db mydb --collection users --type=json --out users.json` then transform and `\copy` or use a Python/Node.js ETL script.
- **Watch out for**: Dumping nested MongoDB documents directly into flat relational tables without deciding what to normalize vs. store as JSONB -- this leads to either data loss or unmaintainable schemas.
- **Works with**: MongoDB 4.x-8.x, PostgreSQL 14-18, AWS DMS 3.4.5+, any language with MongoDB and PostgreSQL drivers.

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- Never run a production migration without first auditing field type variance across at least 10% of documents -- MongoDB collections routinely store the same field as different types (string vs number). [src5]
- Always preserve the original MongoDB ObjectId in a reference column (`mongo_id`) during migration -- you will need it for cross-referencing, debugging, and rollback. [src6]
- Use PostgreSQL `COPY` protocol (not individual `INSERT`s) for bulk loading -- `COPY` is 5-10x faster and avoids transaction overhead on large datasets. [src7]
- Disable foreign key constraints during bulk load (`ALTER TABLE ... DISABLE TRIGGER ALL;`) and re-enable + validate after -- FK violations during load are the #1 cause of failed migrations. [src3]
- MongoDB `Decimal128` must map to PostgreSQL `NUMERIC`, not `FLOAT` -- using `FLOAT` silently loses precision on financial data. [src1]
- AWS DMS document mode stores entire documents in a single `_doc` JSONB column -- this requires a second normalization pass and is not a complete migration on its own. [src3]

## Quick Reference

| MongoDB Concept | PostgreSQL Equivalent | Example |
|---|---|---|
| Collection | Table | `CREATE TABLE users (...)` |
| Document (BSON) | Row (or JSONB column) | `INSERT INTO users (data) VALUES ('{"name":"Alice"}'::jsonb)` |
| `_id` (ObjectId) | `UUID` or `BIGSERIAL` primary key | `id UUID DEFAULT gen_random_uuid()` |
| Embedded subdocument | JSONB column or separate table (FK) | `metadata JSONB` or `CREATE TABLE addresses (user_id UUID REFERENCES users)` |
| Array field | JSONB array or junction table | `tags JSONB` or `CREATE TABLE user_tags (user_id, tag_id)` |
| `$lookup` (aggregation) | `JOIN` | `SELECT * FROM orders JOIN users ON orders.user_id = users.id` |
| `find({status: "active"})` | `WHERE` clause | `SELECT * FROM users WHERE status = 'active'` |
| `find({"addr.city": "NYC"})` | JSONB operator `->>`  | `SELECT * FROM users WHERE data->>'city' = 'NYC'` |
| Compound index | B-tree composite index | `CREATE INDEX idx ON users (status, created_at)` |
| Text index | `tsvector` + GIN index | `CREATE INDEX idx ON docs USING GIN (to_tsvector('english', body))` |
| `$regex` query | `~` or `LIKE`/`ILIKE` | `SELECT * FROM users WHERE name ~ '^Al'` |
| Schema-less fields | JSONB with GIN index | `CREATE INDEX idx ON users USING GIN (metadata)` |
| `db.collection.aggregate()` | SQL `GROUP BY` / window functions | `SELECT status, COUNT(*) FROM orders GROUP BY status` |
| Replica set (read scaling) | Read replicas / streaming replication | `pg_basebackup` + streaming replication |
| `Decimal128` | `NUMERIC` | `price NUMERIC(12,2)` |
| `ISODate()` | `TIMESTAMPTZ` | `created_at TIMESTAMPTZ DEFAULT NOW()` |
| `JSON_TABLE()` (PG 17+) | Explode JSON arrays to rows | `SELECT * FROM JSON_TABLE(doc, '$.items[*]' COLUMNS ...)` |
| UUID v7 (PG 18+) | Time-ordered UUIDs | `id UUID DEFAULT uuidv7()` |

## Decision Tree

```
START
+-- Is downtime acceptable during migration?
|   +-- YES -> Use mongoexport + ETL script + COPY (simplest approach)
|   +-- NO v
+-- Need real-time sync during cutover?
|   +-- YES -> Use AWS DMS with CDC or Debezium + Kafka pipeline
|   +-- NO v
+-- Data volume > 100 GB?
|   +-- YES -> Use parallel export (mongodump --numParallelCollections) + batch COPY
|   +-- NO v
+-- Documents have highly variable schemas?
|   +-- YES -> Use hybrid approach: relational columns for common fields + JSONB for variable fields
|   +-- NO v
+-- Deep nesting (>3 levels)?
|   +-- YES -> Store as JSONB, add GIN indexes for query paths
|   +-- NO v
+-- DEFAULT -> Fully normalize into relational tables with foreign keys
```

## Step-by-Step Guide

### 1. Audit MongoDB collections and plan the schema

Analyze every collection's document structure, field types, nesting depth, and access patterns. This determines which fields to normalize into columns vs. store as JSONB. [src4, src5]

```bash
# List all collections and their document counts
mongosh --eval 'db.getCollectionNames().forEach(c => print(c, db[c].countDocuments()))'

# Sample a collection's schema (using variety.js or manual inspection)
mongosh --eval 'db.users.aggregate([{$sample: {size: 100}}, {$project: {_id: 0}}]).forEach(d => printjson(Object.keys(d)))'

# Detect field type variance (critical for schema design)
mongosh --eval '
  db.users.aggregate([
    { $sample: { size: 5000 } },
    { $project: { _id: 0 } },
    { $addFields: { _keys: { $objectToArray: "$$ROOT" } } },
    { $unwind: "$_keys" },
    { $group: { _id: { field: "$_keys.k", type: { $type: "$_keys.v" } }, count: { $sum: 1 } } },
    { $sort: { "_id.field": 1 } }
  ])'
```

**Verify**: You have a list of all collections with field names, types, and nesting depth. Create an ER diagram for the target PostgreSQL schema before writing any migration code.

### 2. Create the PostgreSQL schema

Design tables based on your audit. Use a hybrid approach: relational columns for frequently queried fields, JSONB for variable or deeply nested data. [src1, src2]

```sql
-- Enable UUID generation (PostgreSQL 13+: gen_random_uuid() is built-in)
-- PostgreSQL 18+: uuidv7() available for time-ordered UUIDs
-- For older versions: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Example: users collection with embedded address subdocument
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    mongo_id VARCHAR(24),          -- preserve original ObjectId for reference
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    status TEXT DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}'    -- variable fields stored as JSONB
);

-- Normalized: embedded addresses become a separate table
CREATE TABLE addresses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    street TEXT,
    city TEXT,
    state TEXT,
    zip TEXT,
    country TEXT DEFAULT 'US',
    is_primary BOOLEAN DEFAULT false
);

-- Array fields: tags become a junction table
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE user_tags (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, tag_id)
);

-- Indexes for common queries
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_status ON users (status);
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);
CREATE INDEX idx_users_created ON users (created_at DESC);
CREATE INDEX idx_addresses_user ON addresses (user_id);
```

**Verify**: `\dt` shows all tables. `\d users` shows column types and constraints match your schema design.

### 3. Export data from MongoDB

Use `mongoexport` for small-to-medium datasets or `mongodump` for large binary exports. JSON format preserves nested structure better than CSV. [src4]

```bash
# Export a single collection as JSON (one document per line = JSONL)
mongoexport --db=myapp --collection=users --type=json --out=users.json

# Export with specific fields (reduces file size)
mongoexport --db=myapp --collection=users --type=json \
  --fields=_id,email,name,status,address,tags,createdAt,updatedAt \
  --out=users.json

# Export all collections (use mongodump for binary, faster for large DBs)
mongodump --db=myapp --out=./dump/

# For large collections, export in parallel
mongodump --db=myapp --numParallelCollections=4 --out=./dump/

# Verify export: count lines in JSON export
wc -l users.json
# Compare with: mongosh --eval 'db.users.countDocuments()'
```

**Verify**: Line count in exported JSON matches document count in MongoDB. Spot-check a few records: `head -5 users.json | python3 -m json.tool`.

### 4. Transform and load data with a migration script

Write a script that reads MongoDB JSON, transforms documents to match the PostgreSQL schema, and inserts using batch operations. [src4, src5]

```python
# migrate_users.py
# Input:  users.json (mongoexport JSONL output)
# Output: Rows inserted into PostgreSQL users, addresses, user_tags tables

import json
import psycopg2       # pip install psycopg2-binary==2.9.9
from datetime import datetime

conn = psycopg2.connect(
    host="localhost", dbname="myapp", user="postgres", password="secret"
)
cur = conn.cursor()

BATCH_SIZE = 1000
user_batch = []
addr_batch = []

def parse_mongo_date(val):
    """Convert MongoDB extended JSON date to Python datetime."""
    if isinstance(val, dict) and "$date" in val:
        return datetime.fromisoformat(val["$date"].replace("Z", "+00:00"))
    if isinstance(val, str):
        return datetime.fromisoformat(val.replace("Z", "+00:00"))
    return None

def parse_object_id(val):
    """Extract string from MongoDB extended JSON ObjectId."""
    if isinstance(val, dict) and "$oid" in val:
        return val["$oid"]
    return str(val)

with open("users.json", "r", encoding="utf-8") as f:
    for i, line in enumerate(f, 1):
        doc = json.loads(line)

        mongo_id = parse_object_id(doc["_id"])
        email = doc.get("email", "")
        name = doc.get("name", "")
        status = doc.get("status", "active")
        created = parse_mongo_date(doc.get("createdAt"))
        updated = parse_mongo_date(doc.get("updatedAt"))
        # Variable fields go into JSONB metadata
        metadata = {k: v for k, v in doc.items()
                    if k not in ("_id", "email", "name", "status",
                                 "createdAt", "updatedAt", "address", "tags")}

        user_batch.append((
            mongo_id, email, name, status, created, updated,
            json.dumps(metadata)
        ))

        # Handle embedded address subdocument
        addr = doc.get("address")
        if addr and isinstance(addr, dict):
            addr_batch.append((
                mongo_id, addr.get("street"), addr.get("city"),
                addr.get("state"), addr.get("zip"), addr.get("country", "US")
            ))

        if i % BATCH_SIZE == 0:
            cur.executemany("""
                INSERT INTO users (mongo_id, email, name, status, created_at, updated_at, metadata)
                VALUES (%s, %s, %s, %s, %s, %s, %s::jsonb)
                ON CONFLICT (email) DO UPDATE SET
                    name = EXCLUDED.name, status = EXCLUDED.status,
                    updated_at = EXCLUDED.updated_at, metadata = EXCLUDED.metadata
            """, user_batch)
            # Addresses reference users by mongo_id -- resolve FK after
            user_batch.clear()
            conn.commit()
            print(f"  Processed {i} documents...")

    # Flush remaining
    if user_batch:
        cur.executemany("""
            INSERT INTO users (mongo_id, email, name, status, created_at, updated_at, metadata)
            VALUES (%s, %s, %s, %s, %s, %s, %s::jsonb)
            ON CONFLICT (email) DO UPDATE SET
                name = EXCLUDED.name, status = EXCLUDED.status,
                updated_at = EXCLUDED.updated_at, metadata = EXCLUDED.metadata
        """, user_batch)
        conn.commit()

# Insert addresses with FK resolution
if addr_batch:
    cur.executemany("""
        INSERT INTO addresses (user_id, street, city, state, zip, country)
        SELECT u.id, %s, %s, %s, %s, %s
        FROM users u WHERE u.mongo_id = %s
    """, [(s, c, st, z, co, mid) for mid, s, c, st, z, co in addr_batch])
    conn.commit()

print(f"Migration complete. {i} users processed.")
cur.close()
conn.close()
```

**Verify**: `SELECT COUNT(*) FROM users;` matches the MongoDB document count. `SELECT * FROM users LIMIT 5;` shows correctly transformed data.

### 5. Migrate indexes and validate queries

Recreate MongoDB indexes as PostgreSQL equivalents. Test every critical query path against the new schema. [src1, src2]

```sql
-- MongoDB: db.users.createIndex({email: 1}, {unique: true})
-- PostgreSQL: already created as UNIQUE constraint on email column

-- MongoDB: db.orders.createIndex({userId: 1, createdAt: -1})
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- MongoDB: db.products.createIndex({name: "text", description: "text"})
-- PostgreSQL: full-text search
ALTER TABLE products ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', coalesce(name,'') || ' ' || coalesce(description,''))) STORED;
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

-- MongoDB: db.events.createIndex({metadata: 1})  (on BSON document)
-- PostgreSQL: GIN index on JSONB
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);

-- MongoDB: db.logs.createIndex({createdAt: 1}, {expireAfterSeconds: 86400})
-- PostgreSQL: no TTL index, use pg_cron or partitioning
-- Option A: partitioning by date range
CREATE TABLE logs (
    id UUID DEFAULT gen_random_uuid(),
    message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Option B: scheduled cleanup with pg_cron
-- SELECT cron.schedule('0 3 * * *', $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '1 day'$$);

-- PostgreSQL 17+: Use JSON_TABLE for analytics on JSONB arrays
-- MongoDB: db.orders.aggregate([{$unwind: "$items"}, {$group: {_id: "$items.sku", total: {$sum: "$items.qty"}}}])
SELECT jt.sku, SUM(jt.qty) AS total
FROM orders, JSON_TABLE(items, '$[*]' COLUMNS (sku TEXT PATH '$.sku', qty INT PATH '$.qty')) AS jt
GROUP BY jt.sku;
```

**Verify**: `EXPLAIN ANALYZE SELECT ...` on your most common queries confirms index usage. Compare query times with MongoDB equivalents.

### 6. Set up application-level dual-write (optional, for zero-downtime)

For production systems, implement a dual-write pattern during the cutover period: writes go to both MongoDB and PostgreSQL, reads shift gradually. [src3, src4, src8]

```javascript
// Node.js dual-write middleware (Express example)
// Input:  Application write request
// Output: Data written to both MongoDB and PostgreSQL

const { MongoClient } = require('mongodb');       // mongodb@6.x
const { Pool } = require('pg');                    // pg@8.x

const mongo = new MongoClient(process.env.MONGO_URI);
const pg = new Pool({ connectionString: process.env.PG_URI });

async function dualWrite(collection, pgTable, document, pgTransform) {
    const mongoDb = mongo.db('myapp');
    const pgRow = pgTransform(document);

    // Write to both -- PostgreSQL is the source of truth
    const pgResult = await pg.query(
        `INSERT INTO ${pgTable} (${Object.keys(pgRow).join(', ')})
         VALUES (${Object.keys(pgRow).map((_, i) => `$${i+1}`).join(', ')})
         RETURNING id`,
        Object.values(pgRow)
    );

    try {
        await mongoDb.collection(collection).insertOne({
            ...document,
            _pg_id: pgResult.rows[0].id  // cross-reference
        });
    } catch (err) {
        // MongoDB write failure is non-fatal during migration
        console.error(`MongoDB shadow write failed: ${err.message}`);
    }

    return pgResult.rows[0];
}
```

**Verify**: Spot-check 100 records -- data in PostgreSQL and MongoDB should match. Monitor error logs for dual-write failures.

### 7. Validate data integrity and cut over

Run full data validation before decommissioning MongoDB. Compare record counts, checksums on critical fields, and test every query path. [src5, src8]

```sql
-- Count comparison
SELECT 'users' AS table_name, COUNT(*) AS pg_count FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'products', COUNT(*) FROM products;
-- Compare with: db.users.countDocuments(), db.orders.countDocuments(), etc.

-- Checksum critical fields (detect data corruption)
SELECT MD5(STRING_AGG(email || name || status, '' ORDER BY mongo_id)) AS checksum
FROM users;

-- Verify foreign key integrity
SELECT COUNT(*) FROM addresses a
LEFT JOIN users u ON a.user_id = u.id
WHERE u.id IS NULL;  -- Should return 0

-- Verify JSONB data is queryable
SELECT COUNT(*) FROM users
WHERE metadata @> '{"role": "admin"}'::jsonb;

-- PostgreSQL 18: use enhanced RETURNING to verify transformations
UPDATE users SET status = 'migrated'
WHERE mongo_id IS NOT NULL AND status = 'active'
RETURNING id, OLD.status AS old_status, NEW.status AS new_status;
```

**Verify**: All counts match. Checksum matches an equivalent computed from MongoDB. FK orphan check returns 0. Application test suite passes against PostgreSQL.

## Code Examples

### Python: Full collection migration with progress tracking

> Full script: [python-full-collection-migration-with-progress-tra.py](scripts/python-full-collection-migration-with-progress-tra.py) (67 lines)

```python
# Input:  MongoDB connection string + collection name
# Output: Data migrated to PostgreSQL table with JSONB hybrid schema
import json
import time
import psycopg2                    # pip install psycopg2-binary==2.9.9
# ... (see full script)
```

### Node.js: Streaming migration for large collections

> Full script: [node-js-streaming-migration-for-large-collections.js](scripts/node-js-streaming-migration-for-large-collections.js) (60 lines)

```javascript
// Input:  MongoDB collection with millions of documents
// Output: Data streamed into PostgreSQL using COPY protocol for maximum throughput
const { MongoClient } = require('mongodb');     // mongodb@6.12.0
const { Pool } = require('pg');                  // pg@8.13.0
const { pipeline } = require('stream/promises');
# ... (see full script)
```

### SQL: Query translation examples (MongoDB shell to PostgreSQL)

> Full script: [sql-query-translation-examples-mongodb-shell-to-po.sql](scripts/sql-query-translation-examples-mongodb-shell-to-po.sql) (42 lines)

```sql
-- Input:  Common MongoDB aggregation queries
-- Output: Equivalent PostgreSQL SQL queries
-- MongoDB: db.orders.find({status: "shipped", total: {$gte: 100}}).sort({createdAt: -1}).limit(20)
SELECT * FROM orders
WHERE status = 'shipped' AND total >= 100
# ... (see full script)
```

## Anti-Patterns

### Wrong: Dumping entire documents into a single JSONB column

```sql
-- BAD -- stores the entire MongoDB document as one JSONB blob
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    doc JSONB  -- everything goes here
);

INSERT INTO users (doc) VALUES ('{"email":"alice@ex.com","name":"Alice","status":"active","createdAt":"2026-01-01"}'::jsonb);

-- Every query now requires JSONB operators, no constraints, no type safety
SELECT doc->>'email' FROM users WHERE doc->>'status' = 'active';
```

### Correct: Hybrid schema with relational columns for common fields

```sql
-- GOOD -- frequently queried fields are real columns with types and constraints
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    status TEXT DEFAULT 'active' CHECK (status IN ('active','inactive','suspended')),
    created_at TIMESTAMPTZ NOT NULL,
    metadata JSONB DEFAULT '{}'  -- only variable/rare fields go here
);

-- Type safety, constraints, and efficient B-tree indexes on real columns
SELECT email, name FROM users WHERE status = 'active' AND created_at > '2026-01-01';
```

### Wrong: Using VARCHAR(24) to store ObjectIds as primary keys

```sql
-- BAD -- ObjectIds make poor PostgreSQL primary keys
CREATE TABLE users (
    id VARCHAR(24) PRIMARY KEY,  -- MongoDB ObjectId as string
    email TEXT
);
-- 24-char string comparisons are slower than UUID, no built-in generation, index bloat
```

### Correct: Use UUID with a mongo_id reference column

```sql
-- GOOD -- native UUID primary key + reference column for traceability
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    mongo_id VARCHAR(24),  -- for cross-referencing during migration, drop later
    email TEXT NOT NULL UNIQUE
);
CREATE INDEX idx_users_mongo_id ON users (mongo_id);  -- temporary, remove post-migration
```

### Wrong: Normalizing every nested field into separate tables

```sql
-- BAD -- over-normalization of rarely-queried nested data
CREATE TABLE user_preferences (id UUID, user_id UUID, key TEXT, value TEXT);
CREATE TABLE user_notification_settings (id UUID, user_id UUID, channel TEXT, enabled BOOLEAN);
CREATE TABLE user_ui_settings (id UUID, user_id UUID, theme TEXT, language TEXT);
-- Result: 4+ JOINs to reconstruct a single user profile, slow reads
```

### Correct: Use JSONB for infrequently queried nested data

```sql
-- GOOD -- one JSONB column for settings, no extra tables
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    preferences JSONB DEFAULT '{"notifications": {"email": true}, "ui": {"theme": "light"}}'
);

-- Still queryable when needed
SELECT * FROM users WHERE preferences->'ui'->>'theme' = 'dark';
-- Add GIN index only if this query becomes frequent
```

### Wrong: Ignoring MongoDB's array-in-document pattern during migration

```python
# BAD -- inserting MongoDB arrays as comma-separated strings
tags_str = ",".join(doc.get("tags", []))
cur.execute("INSERT INTO users (tags) VALUES (%s)", (tags_str,))
# Result: no indexing, no querying individual tags, CSV parsing nightmares
```

### Correct: Use JSONB arrays or a proper junction table

```python
# GOOD -- preserve array structure in JSONB
import json
tags = doc.get("tags", [])
cur.execute(
    "INSERT INTO users (tags) VALUES (%s::jsonb)",
    (json.dumps(tags),)
)
# Query: SELECT * FROM users WHERE tags ? 'premium'
# Or normalize into a junction table for heavy query workloads
```

## Common Pitfalls

- **MongoDB extended JSON date format**: `mongoexport` outputs dates as `{"$date": "2026-01-01T00:00:00Z"}` -- not a plain ISO string. Fix: Parse with `val["$date"]` before inserting into `TIMESTAMPTZ` columns. [src4]
- **ObjectId serialization**: `{"$oid": "65a..."}` in extended JSON, not a plain string. Fix: Extract with `doc["_id"]["$oid"]` or use `str(doc["_id"])` with PyMongo. [src6]
- **Schema inference from small samples**: MongoDB collections often have inconsistent field types (string vs. number for same field). Fix: Sample at least 10% of documents or use `$type` aggregation to detect type variance per field. [src5]
- **Missing NULL handling**: MongoDB documents omit fields entirely instead of storing `null`. Fix: Use `.get(field, default)` in your migration script, and set appropriate `DEFAULT` values in PostgreSQL schema. [src7]
- **Foreign key violations during load**: Inserting child tables before parent rows are committed causes FK constraint errors. Fix: Disable FK constraints during bulk load (`ALTER TABLE addresses DISABLE TRIGGER ALL;`), re-enable and validate after. [src3]
- **JSONB storage bloat**: Storing large arrays (1000+ elements) as JSONB in every row wastes storage and slows updates. Fix: Normalize arrays with >50 elements into junction tables. Keep JSONB for small, variable-length arrays. [src1]
- **Transaction size during bulk load**: Wrapping millions of inserts in a single transaction locks the table and consumes memory. Fix: Commit every 5,000-10,000 rows. Use `COPY` protocol instead of individual `INSERT` statements for 10x throughput. [src7]
- **Losing MongoDB's automatic `_id` index**: Every MongoDB collection has an automatic index on `_id`. Fix: Ensure your PostgreSQL `PRIMARY KEY` (UUID or BIGSERIAL) is in place -- it creates a B-tree index automatically. [src1]
- **AWS DMS document mode assumptions**: DMS stores documents in `_doc` JSONB by default, giving a false sense of completion. Fix: Always plan a second normalization pass after DMS full-load completes. [src3]

## Diagnostic Commands

```bash
# Check MongoDB collection stats before migration
mongosh --eval 'db.users.stats()' | grep -E 'count|size|storageSize|nindexes'

# Export MongoDB collection to JSON
mongoexport --db=myapp --collection=users --type=json --out=users.json

# Verify export line count
wc -l users.json

# Check PostgreSQL table after migration
psql -c "SELECT COUNT(*) AS row_count FROM users;"
psql -c "SELECT pg_size_pretty(pg_total_relation_size('users')) AS table_size;"

# Verify JSONB data is valid
psql -c "SELECT COUNT(*) FROM users WHERE metadata IS NOT NULL AND jsonb_typeof(metadata) = 'object';"

# Check index usage
psql -c "SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE schemaname = 'public' ORDER BY idx_scan DESC;"

# Compare record counts (run both)
mongosh --eval 'db.users.countDocuments()'
psql -c "SELECT COUNT(*) FROM users;"

# Detect orphaned foreign keys
psql -c "SELECT COUNT(*) FROM addresses a LEFT JOIN users u ON a.user_id = u.id WHERE u.id IS NULL;"

# Check for NULL in required fields post-migration
psql -c "SELECT COUNT(*) FROM users WHERE email IS NULL OR name IS NULL;"

# PostgreSQL 17+: Use JSON_TABLE to inspect JSONB array integrity
psql -c "SELECT COUNT(*) FROM orders, JSON_TABLE(items, '\$[*]' COLUMNS (sku TEXT PATH '\$.sku')) jt WHERE jt.sku IS NULL;"
```

## Version History & Compatibility

| Component | Version | Status | Migration Notes |
|---|---|---|---|
| MongoDB 8.0 | Current | Full `mongoexport` support | New queryable encryption not supported by DMS; AWS DMS 3.5.4+ required |
| MongoDB 7.0 | Supported | AWS DMS 3.5.4+ required | Standard migration path |
| MongoDB 6.0 | Supported | AWS DMS 3.5.2+ required | Timeseries collections not supported by DMS |
| MongoDB 5.0 | Supported | AWS DMS 3.5.1+ required | Live resharding not supported by DMS |
| MongoDB 4.x | Legacy | AWS DMS 3.4.5+ | Most documented migration path |
| PostgreSQL 18 | Current (Sep 2025) | All JSONB features | Async I/O (2-3x perf), UUID v7, enhanced RETURNING clause |
| PostgreSQL 17 | Supported | All features | `JSON_TABLE()` for analytics, improved VACUUM |
| PostgreSQL 16 | Supported | All features | Improved JSONB performance |
| PostgreSQL 14-15 | Supported | JSONB subscripting (PG 14+) | Use `jsonb_set()` for older versions |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Need ACID transactions across related documents | Schema is truly dynamic with no common fields | Keep MongoDB or use DynamoDB |
| Want SQL JOINs for complex reporting/analytics | Sub-millisecond reads on simple key-value lookups | Redis or MongoDB with read replicas |
| Regulatory requirements demand strong data integrity | Horizontal sharding across 10+ nodes is required | MongoDB Atlas, CockroachDB, or Citus |
| Team knows SQL better than MongoDB aggregation | Data is primarily geospatial with GeoJSON | MongoDB (better native geo support) or PostGIS |
| PostgreSQL JSONB covers your schema flexibility needs | Write volume exceeds 100K inserts/sec sustained | MongoDB or time-series DB (TimescaleDB) |
| Want a single database for relational + document data | Application already has stable MongoDB with no pain points | Don't migrate for migration's sake |

## Important Caveats

- MongoDB's flexible schema means production collections often have undocumented field type variations (e.g., `age` stored as string in some docs, number in others). Always audit field types across a large sample before designing PostgreSQL columns.
- AWS DMS document mode stores entire documents in a single `_doc` JSONB column -- useful for initial migration but requires a second pass to normalize into relational columns.
- PostgreSQL JSONB does not enforce schema -- you lose MongoDB's optional schema validation. Use `CHECK` constraints or application-level validation to maintain data quality.
- The `COPY` protocol is 5-10x faster than individual `INSERT` statements for bulk loading. Always use `COPY` or the streaming equivalent (`pg-copy-streams` in Node.js, `copy_expert` in psycopg2) for large migrations.
- MongoDB `Decimal128` maps to PostgreSQL `NUMERIC` -- verify precision is preserved, especially for financial data. Test with edge cases like `0.1 + 0.2`.
- PostgreSQL 17's `JSON_TABLE()` function significantly simplifies analytics queries on migrated JSONB data -- consider using it for reporting pipelines instead of manual `jsonb_array_elements()` calls.
- PostgreSQL 18's async I/O can provide 2-3x performance improvement for bulk load operations -- upgrade before running large migrations if possible.
- If your application also needs vector search (RAG, embeddings, semantic similarity), PostgreSQL with the `pgvector` extension covers both relational and vector workloads in a single store -- avoiding the operational overhead of running MongoDB Atlas Vector Search alongside a separate primary database. [src10]
- AWS DMS 3.5.4+ adds full support for MongoDB 7.0 and 8.0 as sources and PostgreSQL 17 and 18 as targets, including generative-AI-assisted schema conversion for the document-to-relational step. [src9]
- For applications with thousands of MongoDB queries, expect 1-2 weeks of rewrite effort per 100-200 queries. Documented enterprise migrations have slipped from 2-month to 7-month timelines almost entirely because of query-rewrite work. [src10]

## Decision Logic

### If downtime is acceptable and dataset < 100 GB
--> Use `mongoexport --type=json` + a Python or Node.js ETL script + PostgreSQL `COPY` protocol. Simplest path, no AWS dependency. [src4, src7]

### If zero-downtime is required
--> Use AWS DMS 3.5.4+ with CDC (or Debezium + Kafka). DMS supports MongoDB 7.0/8.0 source and PostgreSQL 17/18 target; expect a phased dual-run period of 1-3 months. [src3, src9]

### If documents have highly variable schemas with no common fields
--> Do NOT migrate. Either keep MongoDB or use PostgreSQL JSONB-only schema and lose most relational benefits -- the migration cost rarely pays off. [src5, src10]

### If you also need vector search (RAG, embeddings, semantic similarity)
--> Migrate to PostgreSQL and use `pgvector` instead of running MongoDB Atlas Vector Search + a separate primary DB. Teams report ~60% fewer integration bugs with a single store. [src10]

### If the application has >2,000 MongoDB queries to rewrite
--> Budget 1-2 weeks per 100-200 queries and run a 3-6 month migration. Don't promise a 2-month timeline -- documented enterprise migrations regularly slip from 2 to 7 months on query-rewrite alone. [src10]

### If Decimal128 fields exist (financial, scientific data)
--> Map to PostgreSQL `NUMERIC` with explicit precision, NEVER `FLOAT` or `DOUBLE PRECISION`. Test edge cases (0.1 + 0.2, large multiplications) before cutover. [src1]

### If the source MongoDB is on Atlas with queryable encryption
--> AWS DMS does NOT support queryable-encryption sources as of 3.5.4. Use mongodump/mongoexport against a decrypted replica, or stage data through a non-encrypted secondary. [src3, src9]

## Related Units
<!-- Generated from related_kos frontmatter -->

- [MySQL to PostgreSQL Migration](/software/migrations/mysql-to-postgresql/2026)
- [Oracle to PostgreSQL Migration](/software/migrations/oracle-to-postgresql/2026)
- [SQL Server to PostgreSQL Migration](/software/migrations/sqlserver-to-postgresql/2026)
- [Mongoose to Prisma Migration](/software/migrations/mongoose-to-prisma/2026)