MongoDB to PostgreSQL: Batch Migration and Real-Time CDC

You can move data from MongoDB to PostgreSQL in two main ways: export a point-in-time snapshot and load it into PostgreSQL, or use MongoDB Change Streams to backfill existing documents and continuously replicate inserts, updates, and deletes.

Use a batch export for small, one-time migrations when MongoDB will not continue changing during the move. Use change data capture (CDC) when MongoDB remains active and PostgreSQL must stay current for analytics, application migration, or a gradual cutover.

Because MongoDB stores flexible BSON documents while PostgreSQL uses typed tables, both methods require decisions about _id, nested objects, arrays, polymorphic fields, indexes, and delete behavior. Moving the data does not automatically convert MongoDB queries, aggregation pipelines, or application logic.

MongoDB to PostgreSQL methods compared

Estuary also supports Batch Snapshot and Batch Incremental capture modes for collections or MongoDB deployments that cannot use change streams. Only Change Stream Incremental mode captures delete events.

For a deeper comparison of Change Streams, oplog-based capture, polling, and snapshots, see our MongoDB change data capture guide.

What changes when moving from MongoDB to PostgreSQL?

A MongoDB collection does not always translate directly into a normalized PostgreSQL table. Before moving the data, decide how each BSON structure should be represented in the relational destination.

A practical design often uses a hybrid model: frequently queried fields become typed PostgreSQL columns, while less predictable nested content remains in PostgreSQL’s JSONB type.

This process moves and reshapes data. MongoDB Query Language expressions, aggregation pipelines, transactions, indexes, and application code must be reviewed separately.

When to Migrate vs. Replicate MongoDB to PostgreSQL

Data moves from MongoDB to PostgreSQL in two scenarios. A one-time migration copies the current state into PostgreSQL, for example when an application moves its system of record to a relational database. Ongoing replication keeps MongoDB as the source and PostgreSQL as a continuously updated destination, which is where CDC applies.

Common production uses for the replication case:

• Offloading analytical and reporting queries from MongoDB to PostgreSQL, so heavy aggregations do not compete with the application's operational writes.
• Consolidating fragmented document collections (orders, users, products) into a relational model that supports joins and BI tools.
• Feeding a SQL-based dashboard or downstream service that expects relational tables.

The data typically moved is operational application data: orders, events, user records, and similar collections whose changes need to reach analytics quickly.

Method 1: Export MongoDB data and load it into PostgreSQL

This method is suitable for a small, one-time migration involving relatively flat documents. It consists of exporting selected fields, creating a compatible PostgreSQL table, and loading the exported file.

Step 1: Export the MongoDB collection

The following example preserves MongoDB’s _id field:

bash
mongoexport \
  --uri="<MONGODB_CONNECTION_STRING>" \
  --db="employeedb" \
  --collection="employees" \
  --type="csv" \
  --fields="_id,name,position,country,specialization" \
  --out="employees.csv"

Avoid placing production credentials directly in shell history. Use an appropriately protected connection string or the authentication options supported by mongoexport.

Step 2: Create the PostgreSQL table

sql
CREATE TABLE employees (
  mongo_id TEXT PRIMARY KEY,
  name TEXT,
  position TEXT,
  country TEXT,
  specialization TEXT
);

The example keeps the source _id as the destination key. Do not generate a new unrelated identifier unless the migration design deliberately uses a surrogate key and also retains the original MongoDB identifier.

Step 3: Load the CSV with \copy

Run this command from psql:

sql
\copy employees (
  mongo_id,
  name,
  position,
  country,
  specialization
)
FROM 'employees.csv'
WITH (
  FORMAT csv,
  HEADER true,
  ENCODING 'UTF8'
);

\copy reads the file from the client running psql. PostgreSQL's SQL COPY command reads from the database server's filesystem and may require additional server-side file permissions.

Limitations of the export-and-load method

mongoexport can produce JSON or CSV, but CSV output includes only explicitly selected fields. It is therefore most useful for predictable, flat documents. Nested documents, arrays, BSON-specific values, and fields with inconsistent types usually require transformation before loading.

For complex documents, export MongoDB Extended JSON and transform it into typed columns, JSONB, normalized tables, or a combination of these structures.

This process captures only a point-in-time view and does not apply subsequent changes. MongoDB also states that mongoexport is an export utility rather than a deployment backup tool. For continuously changing production data, use change-stream CDC or schedule a controlled cutover.

Method 2: Continuously replicate MongoDB to PostgreSQL with Estuary

Estuary is a managed real-time data movement platform that supports CDC, streaming, and batch pipelines. For MongoDB collections using Change Stream Incremental mode, the connector backfills existing documents while also reading change events. After the initial backfill, it continues capturing inserts, updates, replacements, and deletes.

Captured documents are represented in reusable Estuary collections. A PostgreSQL materialization then writes those collections into connector-managed destination tables. Estuary can reshape documents with SQL or TypeScript transformations before materialization, but you must still decide whether nested data should remain as JSON, become typed columns, or be normalized into related tables.

Disclosure: Estuary is our platform. The connector behavior described below should be verified against the linked product documentation when planning a production migration.

MongoDB prerequisites

Before configuring Change Stream Incremental capture, confirm the following:

• You have MongoDB credentials with read access to the required databases and collections.
• The MongoDB deployment is a replica set or sharded cluster that supports change streams. Standalone deployments cannot provide change-stream CDC.
• Estuary can reach the MongoDB deployment through an IP allowlist, private connectivity, or an SSH tunnel.
• If the user authenticates through the admin database, the connection address includes authSource=admin.
• The replica-set oplog retains changes long enough for the connector to resume after interruptions. Estuary recommends at least 24 hours of retention and a longer period where possible.
• Collections that cannot use change streams, including views and time-series collections, are configured with an appropriate batch capture mode.
• Cursor fields used for Batch Snapshot or Batch Incremental capture are indexed.
• changeStreamPreAndPostImages is enabled if the pipeline requires document values from before updates, replacements, or deletes.

Only Change Stream Incremental capture includes delete events. Batch Snapshot can observe updates by rescanning the collection, while Batch Incremental is appropriate only when its cursor reliably increases for new or updated documents.

PostgreSQL prerequisites

Before creating the destination materialization, confirm the following:

• Estuary can reach MongoDB directly through allowlisted IP addresses or through an SSH tunnel. If you use a Private or BYOC deployment, configure network access according to that deployment.
• You have supported database credentials or cloud IAM authentication.
• The destination user can create, read, and write connector-managed tables. Estuary’s PostgreSQL connector creates new destination tables; manually pre-created tables are not supported.
• You have selected the destination database and schema.
• You have decided whether to use standard updates or delta updates. Standard updates are the default and are normally appropriate for a current-state replica.
• You have decided how source deletes should be represented. PostgreSQL materializations use soft deletes by default; enabling Hard Delete physically removes destination rows.
• Required indexes are planned through the Additional Table Create SQL setting or are created after the initial materialization.

See the MongoDB capture connector documentation and PostgreSQL materialization documentation for current configuration properties, capture modes, authentication methods, and connector behavior.

The steps to achieve this are given below.

• Sign up for Estuary or log in if you already have an account.
  
  

• Make sure your existing MongoDB and PostgreSQL databases are ready to use with Estuary. See the prerequisites for MongoDB and PostgreSQL. 
  
• Next, you will need to create a data pipeline that connects a source to a destination. Data pipelines are called “Data Flow” in Estuary speak. Sources are called “Captures” while destinations are called “Materializations.”
  
• Click on Captures on the left pane menu, then click on the New capture button.
  
  

• Next, select MongoDB as the Connector for your source.
  
• You will have to provide a Name for the capture and provide the necessary details, then click on Next.
  

• You can select collections or modify properties as you see fit. Select the MongoDB collections to capture and review the generated bindings. The captured documents are represented as Estuary collections, which are reusable logical datasets that can be transformed or materialized into one or more destinations. Where pipeline data is processed depends on the selected Estuary deployment model.
• Click on Next again if you made changes to your collections, and then click Save and publish.
  
• The next step is to click on Materialize collections.
  
• You will now select the Connector tile for the destination, which in this case will be PostgreSQL.
  
• Choose a unique Name for the materialization, then configure the connection details, then click Next. You can still choose to add or remove collections at this point.
  

• Finally, click on Save and publish.

Ready to keep PostgreSQL current as MongoDB changes? Explore the MongoDB source connector and PostgreSQL destination connector, then start building a Data Flow for free.

How to validate a MongoDB-to-PostgreSQL pipeline

Do not consider the migration complete after the first rows appear in PostgreSQL. Validate both the backfill and ongoing replication:

1. Compare MongoDB document counts with PostgreSQL row counts, accounting for filters and delete settings.
2. Test documents containing nested objects, arrays, missing fields, explicit nulls, large values, and fields with inconsistent types.
3. Confirm that each MongoDB _id maps consistently to the expected PostgreSQL key.
4. Insert, update, replace, and delete test documents and verify the corresponding destination behavior.
5. Pause and resume the pipeline within the configured oplog-retention window.
6. Measure source-to-destination freshness under a production-like write workload.
7. Verify PostgreSQL indexes and query plans for the destination access patterns.
8. Reconcile important numeric totals, timestamps, and business aggregates between the two systems.
9. Test application cutover and rollback separately from the data replication pipeline.

For an application migration, run MongoDB and PostgreSQL in parallel until correctness, performance, and recovery behavior have been validated.

Can MongoDB and PostgreSQL both accept writes during migration?

The pipeline described here is one-way from MongoDB to PostgreSQL. Writes made directly to PostgreSQL do not flow back to MongoDB. During validation, keep MongoDB as the system of record unless you implement and test a separate dual-write or reverse-replication strategy. Before PostgreSQL accepts production writes, define a cutover, rollback, and reconciliation plan.

Conclusion

For a small, one-time migration of flat MongoDB documents, mongoexport, explicit schema transformation, and PostgreSQL \copy can be sufficient. Preserve MongoDB’s _id, validate BSON-to-PostgreSQL type mappings, and remember that the export will not capture changes made after it runs.

For continuously changing production data, Change Streams CDC is more appropriate when PostgreSQL must remain current. Estuary can perform the initial backfill, capture ongoing MongoDB changes, transform documents, and materialize the results into PostgreSQL without requiring teams to operate a separate streaming stack.

Ready to build the pipeline? Start a MongoDB-to-PostgreSQL Data Flow or contact Estuary to discuss networking, schema design, and production cutover requirements.