How to load data from MongoDB into a Postgres destination

MongoDB is a distributed database that is built for modern transactional and analytical applications and can be used for fast-changing, multi-structured data. On the other hand, PostgreSQL is a SQL database that has all the features you need from a relational database. If you are unsure of the differences between these systems, you can find an article comparing PostgreSQL and MongoDB on the MongoDB website.

Choosing one or the other between MongoDB and PostgreSQL may not be your only option—in fact, since each database has different strengths, you may want to use them side by side. If this is your case, you may need to sync data between them.

Custom building a data pipeline to replicate data from MongoDB to Postgres is time consuming and tedious. Airbyte, on the other hand, is designed for exactly this task. This article will show you how to use Airbyte to replicate and synchronize data from MongoDB to PostgreSQL!

Prerequisites

This guide uses the following tools:

1. Clever Cloud: MongoDB and PostgreSQL database hosting.
2. mongosh: CLI client to interface with MongoDB database.
3. Postgres CLI: terminal interface for PostgreSQL.
4. Airbyte cloud: A data integration tool that will be used to replicate and synchronize data between MongoDB and PostgreSQL. Alternatively, you can choose to install Airbyte OSS locally.

Step 1-A: Start MongoDB

In this section, you will use Clever Cloud to create a MongoDB instance. After signing in, select the option to create an add-on from your personal space.

Select from the available list of plugins MongoDB addition.

Select for sample size DEV plan, which is free to use.

Enter the name of the plugin, select the region as shown below, then click Following.

You should now have a new MongoDB database created with all the details to connect to it. Copy Mongo CLI connect command from the Clever Cloud plugin dashboard shown below.

Copy and replace “mongo” with “Mongolian” before executing the command in your terminal, as shown below:

You should now be connected to the PRIMARY replica of the MongoDB replica set (denoted by [primary] in the shell).

In connecting Airbyte to MongoDB, you will use the URL for the primary replica. This can be recovered by running rs.isMaster().primary in the MongoDB shell, which will respond with a string in the format [hostname]:[port]. In our case, the URL returned by this command is n2-c2-mongodb-clevercloud-customers.services.clever-cloud.com:27017

Step 1-B: Add Data to MongoDB (Optional)

For this demonstration, we download and then import the example restaurant collection using the mongoimport db tool command.

mongoimport --host=n2-c2-mongodb-clevercloud-customers.services.clever-cloud.com --port=27017 --username= --password= --db= –collection=restaurant restaurant.json

Step 2: Configure the MongoDB native connector

Create an Airbyte MongoDB source by selecting sources from the Airbyte dashboard and clicking on A new source button. Then select from the source list MongoDBand you should see a user interface similar to the following:

To keep this tutorial simple and for demonstration purposes only, we’ve selected a standalone MongoDB instance in the image above. However, you can also consider selecting one of the alternative MongoDB configuration parameters if you want to have a more resilient connection to your MongoDB cluster.

Enter the Host, Port, Username, DB Name and Password shown earlier in the Clever Cloud MongoDB configuration UI. Then choose Set the source.

Step 3: Start PostgreSQL

To set up a PostgreSQL database, create a new plugin in the Clever Cloud dashboard and select PostgreSQL of available plugins.

Select for the plan DEV option, which offers 256 MB of storage.

Give your plugin a name and choose a location.

Click on Following once you’re happy with your configurations, then Clever Cloud should show you the PostgreSQL database credentials with the information Airbyte will require, including host, user, password, and database name.

To connect to our newly created database, copy the connection URI and pass it as an argument to the PSQL CLI tool, as shown below.

Step 4: Configure the PostgreSQL destination connector

To go Destinations on your Airbyte dashboard select Create a destination from the list and select PostgreSQL. You will then see a user interface similar to the following:

Enter the PostgreSQL parameters returned by Clever Cloud and click Set destination.

Step 5: Set up an Airbyte connection from MongoDB to Postgres

To go Connections on the Airbyte dashboard and select New relationship. Select the source and destination you just created, after which you should see a user interface similar to the following:

Airbyte got it right restaurant collection as a stream, and you can choose how it is replicated to PostgreSQL. For the synchronization mode, select one of the available modes.

For Frequency of replication, set the interval between starting synchronization. After you are done with the configurations, select Set up a link, and Airbyte will start its first sync. When you’re done, you’ll be able to see how many records have been replicated.

Log in to the Postgres host to see the replicated data. Note that you have to change track_path according to DB Name that you specified when you set the PostgreSQL destination in Airbyte.

You should now be able to see the replicated data using standard SQL commands.

Conclusion

In short, in this guide you learned how to:

1. Start a MongoDB database on Clever Cloud and then add some test data to it.
2. Configure the native Airbyte connector to read data from MongoDB.
3. Start a PostgreSQL database on Clever Cloud.
4. Configure the Airbyte destination connector to send data to PostgreSQL.
5. Create an Airbyte connection that replicates data from MongoDB to Postgres.