A MongoDB collection loads into PostgreSQL, the row count matches, and the application still breaks. A date became a wrapper object. An array lost its order when it became child rows. An absent field was converted to the same value as an explicit null. A database migration has to preserve the meaning the application uses, not just the number of records it can load.
Inventory document shapes before designing tables
Start with the collections and the code that reads and writes them. List important queries, update paths, identifiers and relationships. Profile the actual data rather than using one convenient document as the schema. Record optional fields, nested objects, arrays and fields that appear with different types.
Build a fixture set that includes ordinary documents and awkward cases. Include missing fields, explicit nulls, empty arrays, repeated array elements, deeply nested sections and values that need exact numeric handling. These are proposed migration tests. Their importance depends on your data and application behavior.
For each field, write a target rule and a rejection rule. A conversion should either produce the intended representation or put the source document in a visible exception queue. Silent coercion makes a clean import report look more trustworthy than it is.
Choose relational columns and JSONB deliberately
Stable business fields can become ordinary columns. Repeated children can become related rows. Variable document sections can remain JSONB. Choose the boundary around query needs and update ownership. Do not normalize every nested field simply because the target is relational, and do not put everything in one JSONB column to avoid making decisions.
| Source pattern | Possible target design | Required decision |
|---|---|---|
| Business identifier | Explicit source ID column | Preserve a reversible mapping to the source value |
| Stable scalar field | Typed column | Define conversion and invalid-value handling |
| Ordered nested array | JSONB array or child rows | Retain position and repeated elements |
| Variable metadata | JSONB column | Define which keys the application depends on |
| Absent field versus explicit null | Presence flag or retained document representation | Preserve the distinction when business logic needs it |
Give each transformed document a source reference. If PostgreSQL uses a new internal identifier, keep a mapping from the original source identity. Check references against that mapping before approving the load. An email address or display name is a poor substitute for a durable source identifier.
Extract with BSON type handling in mind
MongoDB's mongoexport produces JSON or CSV. It uses Relaxed Extended JSON by default and supports Canonical Extended JSON through the jsonFormat option. MongoDB documents Canonical mode for preserving rich BSON type information in exports. Record the tool version, collection, options and export scope in the private migration manifest.
Canonical output is an interchange representation, not a PostgreSQL conversion plan. A BSON-aware transformation must interpret the exported types and deliberately map them into the target. A successful JSON parse does not prove a date, identifier or numeric value now has the intended PostgreSQL meaning.
By default, mongoexport writes one JSON document for each MongoDB document. Its jsonArray option writes the output as one JSON array instead. Match the loader to the chosen framing and test interrupted files. A loader expecting one document per line should not accidentally treat an entire collection array as one business record.
Check export coverage and field-name restrictions
Check whether the extraction selects every needed document and field. In JSON exports, mongoexport's fields option includes selected fields and the source identifier. Selecting a nested field includes its containing subdocument with all its fields. Do not assume a narrow field selection also removes every neighboring private value.
MongoDB also documents import and export limitations for field names containing periods or beginning with a dollar character. Extended JSON can confuse type wrappers with fields that use the same wrapper names. Inventory these cases before choosing the extraction path. Stop and resolve unsupported records instead of treating their absence as a successful filter.
Keep a manifest of extracted records, rejected records and intentional exclusions. Separate source access permissions from destination access. An engineer debugging a conversion should not need to distribute unrestricted production exports across personal machines.
Understand what PostgreSQL JSONB changes
PostgreSQL 17 offers json and jsonb. The json type keeps the original input text. JSONB uses a decomposed representation and supports indexing, but it does not preserve whitespace or object key order. It also does not keep duplicate object keys. When input contains duplicate keys, only the last value is retained.
Do not use a byte-for-byte comparison between exported JSON text and JSONB output as the business validation test. Compare the values and structure that should survive. If exact source reconstruction is required, retain the original extraction in a separately controlled archive rather than expecting JSONB to reproduce it.
JSONB also has input constraints. PostgreSQL documents restrictions around Unicode escapes, the zero character and numeric range. Test representative source values against the actual target version. Keep conversion failures visible, including the source reference and the specific field, without copying sensitive document contents into general logs.
Keep nulls, absence and arrays meaningful
PostgreSQL documents JSON null and SQL NULL as different concepts. Define how both relate to an absent source field. If an application distinguishes never provided from deliberately cleared, collapsing both into an empty target column changes behavior. Preserve a presence marker or another explicit representation where needed.
When splitting arrays into child rows, store a position if ordering matters. Preserve repeated elements unless the business has explicitly approved deduplication. An empty array, no array field and an array containing a null value should each have a defined result. Count child elements as well as parent records.
For numeric and date values, test the whole round trip through the export reader, transformation code, database driver and destination. An intermediate parser can lose meaning before PostgreSQL sees the input. Use expected-value fixtures, not just a check that the destination column accepts the load.
Reconcile application behavior before cutover
- Load into a test destination and account for every source document.
- Reconcile source IDs, child counts and unresolved references.
- Compare important filters, sort results and aggregate outputs.
- Run application create, update and delete paths against the target.
- Test repeated loads and confirm they do not duplicate transformed children.
- Agree a write freeze or a tested change-capture plan.
- Run final reconciliation before switching the production writer.
Define what rollback means after PostgreSQL accepts new writes. Sending traffic back to an older MongoDB copy can lose those changes. Name the authority for each phase and keep a journal of post-cutover writes that would need reconciliation. A finished bulk load is only one checkpoint.
How Remqa approaches MongoDB to PostgreSQL migrations
Remqa can scope the document inventory, conversion, validation and application handoff as a data migration. If queries and update paths need changing, connect that work to the code migration plan. Bring a representative collection and the application operations that must remain correct. The first deliverable should be an agreed mapping and a testable cutover plan.