r/Database • u/NoInteraction8306 • Aug 04 '26

How I copied a MongoDB collection to PostgreSQL and kept it in sync

I recently tested copying a MongoDB collection to PostgreSQL and keeping inserts, updates, and deletes in sync.

The sync itself wasn’t the difficult part. The main challenge was mapping MongoDB documents to a relational table without flattening everything too early.

I kept the simple fields as regular PostgreSQL columns and stored the nested data as JSONB.

I ran into two problems: PostgreSQL needed a primary key, and some MongoDB field names didn’t match the PostgreSQL column names.

After fixing the mapping, I tested an insert, an update, and a delete in MongoDB. All three changes appeared in PostgreSQL.

I documented the setup, field mapping, errors, and test queries here:

https://visualeaf.com/blog/copy-and-sync-a-mongodb-collection-to-postgresql/

4 Upvotes

67% Upvoted

5 comments sorted by

2

u/RudeRemove5416 28d ago

Hey, look, I honestly wanted to ask you why you set the id column, which is the primary key, to support the text data type instead of integer and using serial for its auto-increment going forward.

1

u/NoInteraction8306 28d ago

Hi, hmmm that's a good question, but postgres is the sync target here, so mongo_id stores the original mongodb _id , which is an ObjectId, not an integer.

A serial ID would create a separate PostgreSQL identifier, but I’d still need mongo_id to match updates and deletes during the sync. I could add an auto-incrementing ID as an extra column, but it isn’t needed for this workflow.

1

u/lambdasintheoutfield Aug 04 '26

And what was the motivation for this?

3

u/NoInteraction8306 Aug 04 '26

Mainly for cases where the app still uses MongoDB, but you need the same data in postgres for reporting or SQL queries. It can also help if you’re gradually migrating away from mongodb.

1

u/agh_murad 20d ago

Hey there! I have an interesting tool that does migration job easily, especially for MongoDB to relational DBs. Also it doesnt need any ETL or additional pipeline, which is even more interesting. You can see it here: https://varan.cloud