Convert MongoDB to PostgreSQL: documents to tables and JSONB, with the checks that prove each mapping is safe

Should I convert MongoDB documents to tables or JSONB?

Use both in the same table. The columns your application and reports filter, join, sort and sum on become typed PostgreSQL columns, with constraints where you can. The full document goes into a jsonb column next to them, so nothing is lost and a field you did not model on day one is still queryable on day ninety. Arrays of subdocuments, such as line items on an order, get their own child table keyed on the parent id plus the position in the array.

The two pure strategies each fail in a predictable way. All JSONB is quick to build and it is what several tools produce by default: AWS DMS puts each document into a single _doc column in its default document mode, and Fivetran's default packed mode writes the whole document as JSON. You have moved the data and none of the queries. All tables, on the other hand, breaks the first time a document changes shape, which in a MongoDB application happens with ordinary releases. Keep in mind that jsonb is not a byte-for-byte copy either. PostgreSQL documents that it "does not preserve the order of object keys, and does not keep duplicate object keys", keeping only the last value.

How do MongoDB data types map to PostgreSQL?

Most BSON types have one correct PostgreSQL target, and the trouble comes from defaults that pick a different one. Here is the mapping we use, with the note that matters for each.

Names need the same care as values. PostgreSQL keeps no more than 63 bytes of an identifier and truncates anything longer, and flattened paths such as billing_address_customer_primary_contact_phone_extension get there faster than you would think. Unquoted names are folded to lower case, so a MongoDB field called createdAt becomes createdat unless every query quotes it forever. Rename to snake_case in the mapping and the problem never reaches your SQL. The full tool comparison, with each vendor's default quoted from its own documentation, is on MongoDB to PostgreSQL migration tools.

What should I check in MongoDB before converting to PostgreSQL?

Nine things, and each one is a single mongosh command against the source. This is the step that replaces the tools' sampling with facts. The first query reads every document in the collection, so run it against a secondary or off hours on a large collection. It still finishes in minutes on most production datasets, which is a small price for knowing your real schema.

The first query is the important one. It unrolls each document into key and value pairs and counts every combination of field name and BSON type across the whole collection, not across a sample. Its output is the column list for your PostgreSQL table, along with the evidence you need to choose each column's type. Run it once per collection and once per embedded document you plan to flatten, swapping "$$ROOT" for the sub-document path such as "$customer". While you are there, add a $strLenBytes filter on the field name to catch anything that will pass 63 bytes once it is prefixed.

One more check belongs before all nine: db.hello().setName. If it returns nothing, you are on a standalone server. Change streams are available only on replica sets and sharded clusters, so no CDC tool can follow changes until you convert the server into a single-node replica set. That is a short job, and a much better one to find in week one than during the cutover.

How do I export MongoDB data for PostgreSQL?

For a one-time copy, run mongoexport --jsonFormat=canonical and load the output into a staging table with a single jsonb column, then build the typed tables with INSERT ... SELECT in SQL. The canonical flag is not optional. mongoexport defaults to relaxed Extended JSON, and MongoDB describes relaxed mode as emphasizing "readability and interoperability at the expense of type preservation". In relaxed output a 64-bit integer is a plain number and a date is an ISO string, so your SQL sees a number and a string where it needed a bigint and a timestamptz.

This route is right for small collections and proofs of concept. It is a point-in-time copy with no change tracking, so the data is stale when the load finishes, and a cutover planned on it needs a write freeze for as long as the whole export and load take.

What is the best tool to convert MongoDB to PostgreSQL?

The one whose default you have read. AWS DMS suits a one-time move inside AWS, as long as you pick table mode deliberately and raise the sample size, because by default it scans 1,000 documents and its documentation states that fields that do not exist in the target "aren't replicated". Airbyte suits teams who want open source, with the caveat that its schema-enforced default writes structural mismatches as NULL. Its schemaless mode avoids that by landing only _id and data, which returns the modeling to you. Fivetran suits teams who want no pipeline to run, though its unpacked mode goes one layer deep, so nested objects stay JSON.

If the actual goal is to leave MongoDB without rewriting the application, none of these is the answer. FerretDB speaks the MongoDB wire protocol and stores the data in PostgreSQL through the DocumentDB extension, so your drivers keep working. You get PostgreSQL operations, and you do not get relational tables, which for some teams is exactly the right trade.

We built Adapters for the case in between: typed PostgreSQL tables from declared document paths, arrays sent to child tables, and an incremental sync afterwards, on a flat monthly price rather than a row meter. Nothing lands that you did not declare, which is the opposite of sampling, and the field inventory above is exactly the list you declare from. The same flat-price pattern for keeping PostgreSQL fed from other sources is on Postgres ETL tools.

What happens to MongoDB queries after the conversion?

They have to be rewritten, and no data tool does it for you. Aggregation pipelines become SQL with joins, GROUP BY and window functions, and $lookup stages usually turn into plain joins against the child tables you created for arrays. Budget this as its own workstream. On many migrations it is larger than the data movement, and it is the part that decides whether the application team signs off on the cutover date.

It also changes who can answer questions. Reports that needed an engineer who could write an aggregation pipeline can now be written in SQL by anyone on the analytics side, and teams that want to go further can let business users turn plain-English questions into SQL against the new tables. That payoff is only real if the schema is clean, which is one more reason to get the mapping right before the load.

How do I keep MongoDB and PostgreSQL in sync until cutover?

Run a full load, then follow the MongoDB change stream and apply every insert, update and delete to PostgreSQL until the switch. That keeps the cutover to minutes. Two checks make it trustworthy. Reconcile document counts against row counts per collection on a schedule, and compare, for each field, the number of documents that hold it with the number of non-NULL values in its column. The second check is the only one that catches a sampled schema or a NULL-on-mismatch default, because neither produces an error. Change streams resume from a position in the oplog, so an outage longer than your oplog window leaves a gap only reconciliation will reveal.

For the wider set of capture options and what each source needs switched on, see change data capture tools. If MongoDB is one of several systems moving at once, the cost side of the program is in what a data migration really costs, and the relational version of this type-by-type work is in converting MySQL data types to PostgreSQL.