How to migrate MongoDB data to PostgreSQL using mongoexport

11 September,2019 by Jack Vamvas

MongoDB is a non-relational database and the target database platform is relational - PostgreSQL. Is there a simple approach to migrating data from MongoDB to PostgreSQL?

First problem - defining a target schema .

Second problem - tweaking the target schema for any changes made by the developers on the MongoDB side aka Evolving Schema. As a one-off migration - probably not such a major problem - but if your basing a DIY ETL process on mongoexport - than it's something you'll need to consider 

There is a tool provided by MongoDB called mongoexport and it's capable of exporting csv files. 

mongoexport is a command-line tool that produces a JSON or CSV export of data stored in a MongoDB instance.  Make sure you execute mongoexport as a command line utility not as a mongo shell utility 

In summary

Step 1) `mongoexport —type=csv` data to csv-file

Step 2 )Create DDL of  table in PostgreSql and “copy” data to it

 

 

An example of the mongoexport command line statement and the messages 

mongoexport --uri "mongodb://my_usr:my_pwd@mymongoserver.net:27017/testdb" --collection tes --type csv --fields _id,class,status --out /tmp/collection_export.csv

2019-09-11T16:39:56.693+0100 connected to: localhost
2019-09-11T16:39:56.693+0100 exported 77 records

 

An example of loading the data into a PostgreSQL database table 

`COPY myTable(ID,class,status)

FROM '/tmp/collection_export.csv' DELIMITER ',' CSV HEADER;`

 

How to export subfields in a sub document? A common problem is exporting sub fields in a sub document and how use the --fields parameter.     Extending the mongoexport example above, you'll notice I've added company.address and manager.surname , as examples of sub fields in a sub document 

 

mongoexport --uri "mongodb://my_usr:my_pwd@mymongoserver.net:27017/testdb" --collection tes --type csv --fields _id,class,status,company.address,manager.surname --out /tmp/collection_export.csv

 

 

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to migrate MongoDB data to PostgreSQL using mongoexport


dba-ninja.com