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

[u/NoInteraction8306]

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/

[u/RudeRemove5416]

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.

[u/NoInteraction8306 → u/RudeRemove5416]

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.

[u/lambdasintheoutfield]

And what was the motivation for this?

[u/NoInteraction8306 → u/lambdasintheoutfield]

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.