MongoDB to PostgreSQL Sync: Copy & CDC Guide

Building a modern SaaS application often starts with a flexible document database like MongoDB. Its schema-less design allows rapid iteration, letting developers store messy, nested data without upfront table definitions. However, as the application grows, the need for structured analytics and reporting emerges. Business intelligence tools, dashboards, and legacy reporting infrastructure typically rely on relational databases like PostgreSQL. Running complex JOINs, aggregations, or using SQL-based BI tools directly on MongoDB can be painful – queries become slow, indexing is limited, and nested documents require awkward flattening. This is where the challenge lies: you have valuable operational data living in MongoDB, but your analytics team needs it in PostgreSQL. This guide provides a practical two-step solution. First, we cover the initial bulk copy of a MongoDB collection into a PostgreSQL table, handling document flattening and type mapping. Second, we introduce Change Data Capture (CDC) using MongoDB change streams to keep PostgreSQL synchronized in near real-time. By the end, you’ll understand how to build a robust pipeline that combines the best of both worlds: the flexibility of MongoDB for operations and the analytical power of PostgreSQL. This guide is written for developers and product teams building SaaS and web applications who need to move data from MongoDB to PostgreSQL for analytics, without resorting to complex ETL tools or manual exports.

Why Migrate from MongoDB to PostgreSQL for Analytics?

Many teams adopt MongoDB for its flexible schema and fast write performance, but find that analytical workloads expose its limitations. PostgreSQL offers a mature SQL engine with rich support for aggregations, window functions, and joins – queries that power reporting dashboards and business intelligence tools. Running these queries directly on MongoDB often requires complex aggregation pipelines with $unwind, $group, and $lookup stages that are harder to write, debug, and optimize compared to a straightforward SQL query. For example, an e-commerce platform storing orders as nested documents (with items array, shipping info, customer data) may need a weekly sales report by product category and region. In MongoDB, this means unwinding the items array, grouping by category, joining with a separate customer collection to get region, then filtering by date – a multi-stage pipeline that grows brittle as the report evolves. In PostgreSQL, the same report is a simple SELECT with JOIN and GROUP BY, easily extendable with additional dimensions. Moreover, BI tools like Tableau or Metabase connect natively to PostgreSQL but require custom connectors for MongoDB, adding maintenance overhead. Moving analytics to PostgreSQL also enables legacy system integration, as many existing tools expect relational data. These factors make synchronizing MongoDB to PostgreSQL a practical step for teams serious about analytics.

The Initial Data Transfer: Export and Import

After deciding to move analytics workloads from MongoDB to PostgreSQL, the first step is a one-time bulk copy of existing data. This section walks through exporting a MongoDB collection and importing it into a PostgreSQL table, covering common pitfalls like nested documents and large volumes.

Using mongoexport to Extract Data

The simplest approach uses mongoexport to dump a collection to JSON or CSV. For example:

mongoexport --db mydb --collection orders --out orders.json

JSON preserves the full document structure, which is useful when you need to map fields manually later. CSV is flatter and easier to load directly but struggles with arrays and nested objects – those fields are either omitted or stringified. For most analytical migrations, JSON is safer because it retains all data.

Handling Nested Objects and Arrays

MongoDB documents often contain embedded arrays or sub-documents. PostgreSQL can store these as JSONB columns, which allows querying via JSON operators. A common pattern is to create a target table with a data column of type JSONB plus extracted top-level columns for indexing. For example:

CREATE TABLE orders (
    id TEXT PRIMARY KEY,
    customer TEXT,
    items JSONB,
    created_at TIMESTAMP
);

Then use a script (Python, Node.js) to parse the JSON dump, extract the key fields, and insert. Arrays like items can remain as JSONB, enabling later extraction into a normalized order_items table.

Loading Data: COPY vs INSERT

PostgreSQL’s COPY command is the fastest way to load data from a file. After exporting to CSV, you can run:

COPY orders (id, customer, items, created_at) FROM '/path/to/orders.csv' DELIMITER ',' CSV HEADER;

For JSON, a common workflow is to stream documents from a script and batch insert using INSERT INTO ... VALUES (...), (...), .... While slower than COPY, this gives you full control over transformation. For typical mid-size datasets (millions of rows), COPY with a preprocessed flat file is recommended.

Chunking Large Datasets

When collections contain billions of documents, a single export may overwhelm memory or network. Use the --query option with a date range or --skip/--limit to chunk exports. For example:

mongoexport --db mydb --collection orders --query '{ "created_at": { "$gte": ISODate("2023-01-01"), "$lt": ISODate("2023-02-01") } }' --out january_orders.json

Then import each chunk sequentially. This also allows you to parallelize loading and monitor progress.

Handling Data Type Mismatches

MongoDB’s BSON types (ObjectId, Date, NumberLong) need explicit mapping. Convert ObjectId to a string or UUID in PostgreSQL. Use TIMESTAMP WITH TIME ZONE for dates. Ensure numeric precision: MongoDB uses 64-bit floating point by default, but PostgreSQL NUMERIC is safer for monetary values. Always test with a subset first.

Once the bulk copy is complete, you have a snapshot of your data in PostgreSQL. The next section will show how to keep that snapshot current using change data capture.

Capturing Changes with MongoDB Change Streams

Once you’ve completed the initial bulk copy, the next challenge is keeping your PostgreSQL data in sync with MongoDB as documents are inserted, updated, or deleted. MongoDB’s change streams provide a real-time, event-driven mechanism to capture these changes. This section explains how to set up change streams and use them as the foundation of your CDC pipeline.

Prerequisites

Change streams require a replica set or a sharded cluster with replica sets. A standalone MongoDB instance does not support change streams. If your current deployment is a standalone, you can convert it to a single-node replica set by restarting with the --replSet flag and initializing it via rs.initiate(). This is safe for development, but production should use at least a three-member set for resilience.

Opening a Change Stream in Node.js

The official mongodb Node.js driver provides a watch() method on collections, databases, or the entire cluster. The example below opens a change stream on a specific collection and logs inserts, updates, and replaces:

const { MongoClient } = require('mongodb');

async function startChangeStream() {
  const uri = 'mongodb://localhost:27017/mydb?replicaSet=rs0';
  const client = new MongoClient(uri);
  await client.connect();

  const collection = client.db('mydb').collection('orders');
  const changeStream = collection.watch();

  for await (const change of changeStream) {
    console.log('Change event:', change);
    // Here you would apply the change to PostgreSQL
  }
}

startChangeStream().catch(console.error);

Filtering by Collection and Operation Type

By default, watch() returns all changes on the target. You can narrow the stream using a pipeline of $match stages. For instance, to listen only to inserts and updates on orders:

const pipeline = [
  { $match: { operationType: { $in: ['insert', 'update'] } } }
];
const changeStream = collection.watch(pipeline);

This reduces noise and simplifies downstream processing.

Handling Resume Tokens for Fault Tolerance

Change streams are resumable if your application restarts or experiences a network interruption. Each change event includes a _id field that acts as a resume token. Store this token after processing each event, and pass it to watch() on restart:

let resumeToken;

const changeStream = collection.watch();
changeStream.on('change', (change) => {
  // Process change
  resumeToken = change._id;
});

// On restart
collection.watch([], { resumeAfter: resumeToken });

This ensures no changes are missed, even after a crash.

Oplog vs. Change Streams

Before change streams (introduced in MongoDB 3.6), developers tailed the oplog directly. While still possible, change streams offer higher‑level abstractions, built‑in filtering, and no need to parse the raw oplog. Change streams also work seamlessly with sharded clusters. For new CDC pipelines, prefer change streams.

With change streams in place, you can now consume these events and apply them to PostgreSQL. The next section covers exactly that.

Processing CDC Events and Applying to PostgreSQL

After capturing change stream events from MongoDB, the next challenge is reliably applying them to PostgreSQL. Each event carries a document ID (_id), the operation type (insert, update, replace, delete), and the new document (for inserts/updates).

Mapping MongoDB _id to PostgreSQL Primary Key

MongoDB’s _id can be an ObjectId, UUID, or custom value. For PostgreSQL, convert ObjectId to a text field or use a UUID type if you stored UUIDs originally. A common pattern is to store _id as TEXT in PostgreSQL and make it the primary key. Example column definition: id TEXT PRIMARY KEY. When processing a change event, extract fullDocument._id and convert it to a string (e.g., _id.toString() or _id.toHexString() if ObjectId).

Handling Updates with Upsert Patterns

Applying a change means performing an upsert: INSERT ... ON CONFLICT (id) DO UPDATE. This handles both inserts and updates in one statement. For deletes, simply run a DELETE where id matches. To ensure idempotency, process events exactly once by tracking resume tokens or by using a unique constraint with ON CONFLICT DO NOTHING for inserts if you may receive duplicates.

Example: Batch Upsert from Change Stream Cursor

Below is a simplified Node.js example that buffers events and performs a batch upsert every 100 events or after 1 second:

const { MongoClient } = require('mongodb');
const { Pool } = require('pg');

const mongoClient = new MongoClient(uri);
const pgPool = new Pool({ connectionString: postgresUri });

async function processChanges() {
  const db = mongoClient.db('mydb');
  const collection = db.collection('orders');
  const changeStream = collection.watch();

  let buffer = [];

  for await (const change of changeStream) {
    if (['insert', 'update', 'replace'].includes(change.operationType)) {
      const doc = change.fullDocument;
      buffer.push({
        id: doc._id.toString(),
        data: doc
      });
    } else if (change.operationType === 'delete') {
      const id = change.documentKey._id.toString();
      await pgPool.query('DELETE FROM orders WHERE id = $1', [id]);
    }

    if (buffer.length >= 100) {
      await batchUpsert(buffer);
      buffer = [];
    }
  }
}

async function batchUpsert(rows) {
  const client = await pgPool.connect();
  try {
    for (const row of rows) {
      await client.query(`
        INSERT INTO orders (id, data) VALUES ($1, $2)
        ON CONFLICT (id) DO UPDATE SET data = $2
      `, [row.id, JSON.stringify(row.data)]);
    }
  } finally {
    client.release();
  }
}

This pattern uses ON CONFLICT to handle both new and updated documents. For production, you would batch with INSERT ... ON CONFLICT in a single statement using unnest.

Managing Schema Drift and Adding Columns

MongoDB collections often have evolving schemas. When a new field appears in a document, you have two options:

• Store the entire document as a JSONB column in PostgreSQL, preserving flexibility.
• Or, predefine a set of columns and use JSONB for the rest (hybrid approach). If you choose fixed columns, monitor schema changes and run ALTER TABLE to add new columns when necessary. A simpler approach is to rely on JSONB and extract fields in queries with ->> syntax, avoiding migration headaches.

By combining upsert logic with a flexible storage strategy, you can keep PostgreSQL in sync with MongoDB efficiently, even as schemas evolve.

Avoiding Common Mistakes When Syncing MongoDB to PostgreSQL

Even with a well-designed change data capture (CDC) pipeline, several pitfalls can disrupt your MongoDB-to-PostgreSQL sync. Here are the most common issues and how to address them.

1. Large Documents Exceeding Row Limits

PostgreSQL has a practical row size limit of about 1.6 TB, but individual columns have limits (e.g., TOAST can handle large values, but performance degrades). If your MongoDB documents contain huge embedded arrays or binary data, they may exceed reasonable row sizes or cause slow writes. Solution: Split oversized documents into related tables. For example, if a product document has an array of hundreds of reviews, store the product metadata in one table and the reviews in a child table with a foreign key referencing the product’s _id. This keeps each row lean and queries efficient.

2. Transforming Nested Structures with JSONB

MongoDB’s flexible schema often includes deeply nested subdocuments. Attempting to flatten everything into individual columns is brittle and violates normalization principles. Solution: Use PostgreSQL’s JSONB column type to store entire nested objects or arrays when the substructure is not queried independently. For fields that need filtering or joining, extract only the necessary keys into separate columns during the ETL step. This strikes a balance between queryability and maintainability.

3. Missing Change Events

If your change stream resumes token expires or your consumer crashes without checkpointing, you may lose events. Solution: Always persist the resume token to a durable store after each batch is applied to PostgreSQL. In case of failure, restart from the last checkpoint. Also, configure MongoDB’s change stream with startAfter to avoid re-processing events. For high-traffic collections, consider using a separate oplog tailer to replay missed events.

4. Latency Spikes and Backpressure

When the initial bulk copy runs simultaneously with CDC, the consumer can fall behind, causing PostgreSQL to buffer high volume writes and increasing sync lag. Solution: Monitor the CDC lag (difference between the latest change event timestamp and the time it was applied) and set a threshold alert. Implement backpressure by pausing the batch copy if lag exceeds, say, 10 seconds. Also, use batch upserts (e.g., 1000 events at a time) rather than single-row inserts to improve throughput.

5. Conflicts During Initial Sync Overlap

If you start CDC before the initial bulk copy completes, you may apply the same change twice, or a change may be overwritten by the bulk import. Solution: Choose a consistent snapshot timestamp from MongoDB (e.g., using countDocuments timestamp). Perform the bulk copy from that snapshot, and start the change stream from the same timestamp. After the bulk copy finishes, run a verification step to reconcile any changes that occurred during the copy. Use idempotent upsert logic so that applying the same event multiple times does not corrupt data.

By anticipating these common mistakes and building safeguards into your pipeline, you can ensure a reliable and accurate sync between MongoDB and PostgreSQL.

Batch Sync vs. CDC: Choosing the Right Strategy

Deciding between periodic batch syncs and real-time change data capture (CDC) depends on your latency requirements, data change frequency, and operational constraints. Each strategy carries distinct trade-offs in complexity, cost, and consistency.

When batch sync is sufficient

A simple nightly or hourly batch sync using mongoexport and PostgreSQL COPY works well when data changes infrequently (e.g., a product catalog updated daily) and your analytics only need end-of-day accuracy. This approach is straightforward to implement, uses minimal infrastructure — just a cron job and a script — and avoids the overhead of maintaining a persistent CDC pipeline. For static reporting like monthly sales summaries or compliance snapshots, batch syncs are cost-effective and easy to debug.

When CDC is necessary

Real-time CDC via MongoDB change streams becomes essential when your analytics must reflect current state. For instance, a fraud detection dashboard or a live inventory feed for an e-commerce platform needs sub-minute latency. As covered in Section 5, processing change events with upsert statements keeps PostgreSQL in sync without batch windows. The trade-off is higher complexity: you must handle resume tokens, manage worker restart scenarios, and ensure idempotent writes.

The hybrid approach

Most production pipelines start with a one-time batch load of historical data (as detailed in Section 3) and then switch to CDC for ongoing changes. This combines the efficiency of bulk loading with the freshness of streaming updates. For example, you could export all orders from MongoDB into a PostgreSQL table, then activate a change stream listener to capture new orders and updates. The initial batch seed gives you a full dataset quickly; CDC keeps it current.

Tooling availability

For batch transfers, built-in MongoDB tools (mongoexport, mongodump) combined with PostgreSQL's COPY command require no extra licenses. For CDC, you can write a Node.js or Python script using the MongoDB driver's change stream API and the psycopg2 or node-postgres library for apply logic. Third-party ETL platforms offer managed connectors but add cost and vendor lock-in. A practical starting point is to prototype a hybrid pipeline using open-source components; platforms like Paradane (https://paradane.com) can help orchestrate these workflows at scale without reinventing infrastructure.

Your Next Sync Project: A Practical Starting Point

Now that you’ve explored both the initial bulk copy and real-time CDC approaches, it’s time to put them into practice. Start by choosing a single MongoDB collection you frequently query for reporting—maybe an orders or users collection. Export it using mongoexport as you learned in Section 3, then load it into a PostgreSQL table with JSONB columns for any nested fields. Once the initial data is in place, write a small Node.js script that opens a change stream on that collection, filtering for insert, update, and delete operations. For each change event, apply an upsert to your PostgreSQL table using the _id as the primary key. This two-step approach gives you immediate analytics capability from the bulk copy while keeping the data fresh with CDC. If you run into schema drift or nested array challenges, revisit the advice in Section 6. To simplify building and maintaining such pipelines, you can leverage Paradane at https://paradane.com for orchestrating the batch and CDC steps without reinventing the wheel. Start with this minimal proof of concept, then gradually expand to more collections and complex transformations. The key is to take action: pick a collection, run the initial copy, and wire up the change stream this week.