Streaming Replication From MongoDB Into PostgreSQL
At some point in your engineering career, you will face the question:
- “How to replication data from MongoDB into PostgreSQL?”.
- Or even “NoSQL vs. SQL?”
This question comes from the need for Online Analytical Processing (OLAP) or Cube, usually from business/product teams. You can imagine OLAP Cube as a multi-dimensional spreadsheet dependent on tabular and row-and-column data. This is where relational databases with SQL (Structured Query Language) shine.
Therefore, in most cases, you will end up supporting SQL (e.g., PostgreSQL) and NoSQL(e.g., MongoDB) and find ways to replicate data between them to keep your business/product teams happy :)
How to Use Oplog to Replicate Data From MongoDB Into PostgreSQL
As shown in the architecture diagram below, we are leveraging MongoDB’s Replica Set Oplog.
The Oplog keeps all write operations in a unique collection so all replica sets can copy and apply these operations asynchronously; it is the exact mechanism MongoDB uses to maintain the current state of the database across all replica sets.
We will use the mosql library to connect MongoDB and PostgreSQL and map collections into SQL schema.
Alright, let’s start. We are going to run Dockerized mosql and stream this replication.
Step 1: Prepare your environment variables in .envrc file in mosql folder:
mkdir mosql
cd mosqlcat .envrc
MOSQL_POSTGRESQL=<POSTGRESQL CONNECTION STRING>
MOSQL_MONGODB=<MONGODB CONNECTION STRING>
MOSQL_DB=<MONGODB NAME>
Step 2: Create a folder and place your collection map files into the collection folder.
mkdir -p mosql/collection
cd mosqlStep 2: Create mosql.sh bash file with the following content:
Step 3: Create Dockerfile with the following content:
Step 4: Build and run your docker container:
docker build -t mosql .
docker run --env-file=.envrc --name=mosql mosqlThat’s it! If your connections with MongoDB and PostgreSQL are correct, your mosql container will start replication streaming.
Feel free to reach us if you have any questions or provide any feedback. We will be happy to help you!
Disclaimer: We appreciate and would like to credit Stripe and Nelson for their work. However, their repository is no longer maintained; please follow our forked version for future updates.
