Connecting MongoDB to PostgreSQL: 2 Easy Methods

MongoDB is a NoSQL database that provides a document-oriented storage mechanism. It is the preferred choice for most use cases involving structured and semi-structured data.

MongoDB’s comprehensive querying layer, combined with the ability to add keys dynamically, makes it a suitable choice for evolving requirements.

In some cases, as time passes, the use cases mature, leading to table structures getting frozen, and the flexibility of a NoSQL database starts losing importance.

At this stage, most companies will try to move data from the NoSQL database to a conventional relational database, providing even better-querying abilities.

This post will cover the methods to move data from MongoDB to PostgreSQL using the manual ETL process and via Hevo Data.

Prerequisites

You will have a much easier time understanding the ways to set up the MongoDB to PostgreSQL connection if you have gone through the following aspects:

• An active MongoDB account.
• Ac active PostgreSQL account.
• Working knowledge of Databases.
• Clear idea regarding the type of data to be transferred.

Methods to Set Up MongoDB to PostgreSQL Integration

This post talks about the first method in depth. Additionally, the blog also talks about the limitations of MongoDB ETL. Towards the end, the blog discusses a simpler alternative to move data from MongoDB to PostgreSQL. Below are the two methods:

Method 1: Using Hevo Data to Set Up MongoDB to PostgreSQL Integration

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from MongoDB and 100+ other data sources to PostgreSQL and other Databases, Data Warehouses, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

The steps to load data from MongoDB to PostgreSQL using Hevo Data are as follow:

• Connect your MongoDB account to Hevo’s platform. Hevo has an in-built MongoDB Integration that connects to your account within minutes.

• Select PostgreSQL as your destination and start moving your data.

With this, you have successfully set up MongoDB to PostgreSQL Integration using Hevo Data.

Method 2: Manual ETL Process to Set Up MongoDB to PostgreSQL Integration

Copying data from MongoDB to PostgreSQL is usually accomplished using the mongoexport command and COPY command in PostgreSQL. Here are the broad steps:

• Step 1: Extract Data from MongoDB using mongoexport Command
• Step 2: Create a Product Table in PostgreSQL to Add the Incoming Data
• Step 3: Load the Exported CSV from MongoDB to PostgreSQL

Let us try to understand this process with the help of an example. 

Step 1: Extract Data from MongoDB using mongoexport Command

We will first use the mongoexport command to create a CSV file from an already existing Mongo collection.

mongoexport --host localhost --db productdb --collection products --type=csv 
--out products.csv --fields name,category,color, brand,description

Here the database name is productdb, the collection name is products and products.csv is the output file we expect. The last attribute fields will contain the names of the keys that will be exported to CSV. This is very important because MongoDB does not maintain a hard structure of keys and it is very much possible that not all keys are present in all the documents. The developer will have to ensure the keys that need to be present in CSV are specified. In case there is no such key present in a document, mongoexport will not throw any error. It will just silently populate that column as a blank value. If not careful enough, this can lead to unexpected results.

Step 2: Create a Product Table in PostgreSQL to Add the Incoming Data

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
category VARCHAR NOT NULL,
color VARCHAR  NOT NULL,
brand VARCHAR  NOT NULL,
description VARCHAR NOT NULL
)

Step 3: Load the Exported CSV to PostgreSQL

COPY products(name,category,color, brand,description)
FROM 'C:tmppersons.csv' DELIMITER ',' CSV HEADER;

And that completes the process. Even though these steps may seem simple, it is to be noted that this is an oversimplified version of the real data migration problem. MongoDB and PostgreSQL are very different databases and there are a number of factors that can lead to unforeseen errors in this migration. Let’s look at some of the cases which can get us into trouble while using the above approach.

Limitations of Manual ETL Process to Set Up MongoDB to PostgreSQL Integration

Though the manual ETL process of setting up MongoDB to PostgreSQL Integration is effective, however, it has certain limitations. Below are a few of the limitations associated with MongoDB to PostgreSQL Integration:

1. In typical use cases, there will be a large number of keys in a document and thereby in a MongoDB collection. Since there is nothing that restricts developers from adding more keys on the fly, most MongoDB collections will have hundreds of keys. Finalizing and specifying the keys to export in itself will be a big challenge.  
2. Since an intermediate file is involved and that file contains the whole of a MongoDB collection, this approach requires the developer to have access to an instance with enough storage space to temporarily store the file.
3. Normally, not all keys in the MongoDB collection will be present in all the documents in that collection. This method does not explicitly do anything to handle the case of non-existent keys. This will have to be specifically handled in usual scenarios.
4. Many a time, the copying data is not a one-time process, but a periodic process that keeps syncing the two databases. In that case, this will need to be modified using custom logic to accommodate the scheduling and handling of duplicate data.
5. For large MongoDB collections that serve a production application, there is a risk of this process affecting the response time of the incoming traffic. In such cases, it is imperative that the developer implements some throttling logic.
6. Another source of trouble could be the presence of arrays as field values in your MongoDB collection. A simple CSV export could lead to unexpected results here.

Conclusion

This article provided an introduction to MongoDB and PostgreSQL and explained their features. Moreover, it elaborated on the 2 methods which you can use to set up MongoDB to PostgreSQL integration. The manual method, although effective, will require a lot of time and resources. Data Migration from MongoDB to PostgreSQL is a painful and time taking process but using a data integration tool like Hevo can perform this process with no effort and no time.

Visit our Website to Explore Hevo

Businesses can use automated platforms like Hevo Data to set this integration and handle the ETL process. It helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you a hassle-free experience.

Want to try Hevo? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Have a look at our unbeatable pricing, which will help you choose the right plan for you.

Share your experience of loading data from MongoDB to PostgreSQL in the comment section below.