How to Copy and Sync a MongoDB Collection to PostgreSQL

I tested a MongoDB-to-PostgreSQL sync with a visits collection.

At first, I thought the hard part would be the sync itself. It was not. The harder part was making PostgreSQL understand the MongoDB document shape.

MongoDB lets one document contain strings, dates, nested objects, arrays, and an _id. PostgreSQL wants fixed columns, clear data types, and a primary key.

So before running the sync, I had to decide what the PostgreSQL table should look like.

For this test, I used VisuaLeaf Mongo Sync.

I used it because I wanted to set up the workflow visually: source collection, target PostgreSQL table, field mapping, and sync status. I still had to create the PostgreSQL table and check the mapping carefully, but I did not have to write a custom sync script.

Source Collection

The source was:

database_compare_demo.visits

Each MongoDB document had simple fields like:

• _id
• visitId
• status
• visitDate
• visitReason

It also had nested fields:

• patient
• doctor
• symptoms
• prescriptions
• invoice
• labResults
• visitDetails
• vitals
• clinic

This is where the mapping matters. If you try to force every nested MongoDB field into separate SQL columns too early, the setup gets messy fast.

For this test, I used a PostgreSQL landing table.

PostgreSQL Table

I created one table:

CREATE TABLE public.mongo_visits_sync (
    mongo_id TEXT PRIMARY KEY,
    "visit_id" TEXT,
    status TEXT,
    "visit_date" TIMESTAMPTZ,
    "visit_reason" TEXT,

    patient JSONB,
    doctor JSONB,
    symptoms JSONB,
    prescriptions JSONB,
    invoice JSONB,
    "lab_results" JSONB,
    "visit_details" JSONB,
    vitals JSONB,
    clinic JSONB
);

I kept the simple fields as normal columns.

I stored the nested MongoDB objects and arrays as JSONB.

This was easier to test, and it kept the original document structure instead of flattening everything too early.

Why mongo_id Is Important

The PostgreSQL table needs a primary key.

In this setup, MongoDB _id maps to PostgreSQL mongo_id.

_id -> mongo_id

Without this, the sync cannot safely update existing rows.

One error I hit was:

SQL target has no key columns — cannot upsert idempotently

That means PostgreSQL did not have a usable key for the sync job.

The fix was to create mongo_id as the primary key and map MongoDB _id to it.

Field Mapping and Data Types

In VisuaLeaf, the mapping shows the MongoDB fields with simple source types like String, Object, and Array.

That matters because not every MongoDB field should become a normal text column in PostgreSQL.

For this test, I used this logic:

The simple string fields went into normal PostgreSQL text columns.

The objects and arrays went into JSONB columns.

This was the important part. If I mapped an object or array into a normal text column, the sync would either fail or store the data in a way that is harder to query later.

For example:

patient is an object, so I mapped it to a JSONB column.

symptoms is an array, so I also mapped it to JSONB.

The main key was:

_id -> mongo_id

That gave PostgreSQL a stable primary key for updates and deletes during sync.

The Error I Had With Column Names

At first, my PostgreSQL table used names like:

lab_results
visit_details
visit_reason

But the sync tried to insert into:

labResults
visitDetails
visitReason

So PostgreSQL returned this error:

ERROR: column "labResults" of relation "mongo_visits_sync" does not exist

The problem was not PostgreSQL. The problem was the mismatch between the mapping and the real column names.

You can fix this in two ways:

1. Keep snake_case in PostgreSQL and map each field manually.
2. Use quoted camelCase column names in PostgreSQL.

Sync Setup

The source MongoDB database was a replica set.

That matters because continuous sync uses MongoDB change streams. Change streams work with replica sets and sharded clusters, not standalone MongoDB.

The setup was:

I used Full sync because I wanted the first copy and the later changes.

Checking the Initial Copy

After running the sync, I checked the row count:

SELECT COUNT(*) AS synced_rows
FROM public.mongo_visits_sync;

Then I checked a few fields:

SELECT
    "visit_id",
    status,
    "visit_date",
    patient ->> 'fullName' AS patient_name,
    doctor ->> 'fullName' AS doctor_name
FROM public.mongo_visits_sync;

This confirmed that normal fields were copied into columns, and nested values were still queryable from JSONB.

Testing Insert, Update, and Delete

The first copy is not enough to prove the sync works.

So I tested changes from MongoDB too.

For the update test, I used:

db.visits.updateOne(
  { visitId: "VIS-SYNC-TEST" },
  {
    $set: {
      status: "completed",
      "visitDetails.notes": "Updated after initial sync"
    }
  }
);

Then I checked PostgreSQL:

SELECT
    "visit_id",
    status,
    "visit_details" ->> 'notes' AS notes
FROM public.mongo_visits_sync
WHERE "visit_id" = 'VIS-SYNC-TEST';

The updated status and note appeared in PostgreSQL.

I also tested insert and delete. The new document appeared in PostgreSQL, and the deleted document was removed from the table.

What I Would Watch For

The sync works, but the setup is not automatic magic.

You still need to be careful with:

• primary keys
• field mapping
• camelCase vs snake_case column names
• nested objects
• arrays
• JSONB fields
• unmapped MongoDB fields

For a first version, one PostgreSQL landing table is usually easier than trying to normalize everything.

After the data is synced, you can decide if some fields should move into separate SQL tables later.

Conclusion

This kind of sync is useful when MongoDB is where the app data lives, but PostgreSQL is better for reporting, SQL queries, dashboards, or sharing data with teams that work mostly in relational databases.

The goal is not only to copy the collection once. The useful part is keeping PostgreSQL updated when documents are inserted, updated, or deleted in MongoDB.

For this test, the cleanest setup was to store simple fields as normal PostgreSQL columns and keep nested objects and arrays as JSONB.

Once the primary key, column names, data types, and field mapping were correct, VisuaLeaf Mongo Sync could handle the copy and the later changes without writing a custom sync script.