Migrating Schema from MongoDB to PostgreSQL: A Practical Example

Database migrations aren't just for upgrading databases inside the same system. They are also essential when switching between different database systems. We'll walk through the process of converting a schema from MongoDB to PostgreSQL using Python and related modules in this.
The following is just a quick example to know the idea.

Understanding the Scenario

Assume you're working on a social networking platform that allows users to send messages. The data was originally stored in a MongoDB database, but you've decided to migrate it to PostgreSQL for better querying and scalability. The user messages and associated data will be moved from MongoDB to PostgreSQL.

Step 1: Install Required Libraries

Make sure you have the necessary libraries installed:

pip install pymongo psycopg2

Step 2: Fetch Data from MongoDB

Use the pymongo library to connect to the MongoDB database and retrieve the data you want to migrate:

from pymongo import MongoClient

# Connect to MongoDB
mongo_client = MongoClient("mongodb://localhost:27017/")
mongo_db = mongo_client["social_media_db"]
mongo_messages = mongo_db["messages"]

# Fetch messages
messages_to_migrate = list(mongo_messages.find())

Step 3: Set Up PostgreSQL Connection

Create a PostgreSQL database and set up the schema to match the MongoDB data. Use the psycopg2 library to connect to the PostgreSQL database and insert data:

import psycopg2

# Connect to PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="social_media_db",
    user="your_username",
    password="your_password"
)
cur = conn.cursor()

# Create schema in PostgreSQL if not already done
create_schema_query = """
CREATE TABLE IF NOT EXISTS messages (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    content TEXT
);
"""
cur.execute(create_schema_query)
conn.commit()

# Insert migrated messages
for message in messages_to_migrate:
    insert_query = "INSERT INTO messages (user_id, content) VALUES (%s, %s);"
    cur.execute(insert_query, (message["user_id"], message["content"]))
    conn.commit()

# Close connections
cur.close()
conn.close()

Step 4: Data Transformation (Optional)

If data types or structures between MongoDB and PostgreSQL are different, you might need to transform the data before insertion.

Step 5: Testing and Verification

Before using the new PostgreSQL-based system, thoroughly test the migrated data to ensure accuracy and data integrity.

Conclusion

Migrating schema and data between database systems is a difficult operation that needs meticulous planning, data transformation, and validation. In this example, we used Python and the pymongo and psycopg2 modules to convert user messages from MongoDB to PostgreSQL. Data is retrieved from the source database, the destination schema is configured, and data is transformed and inserted into the target database. Database migration between different systems can be accomplished successfully with careful planning and execution, allowing enterprises to capitalize on the capabilities of each database platform.

Comments

[Sourabh Gupta]

Thanks for sharing this! For anyone looking for a no-code solution, Estuary Flow is worth checking out. It streamlines data transfer between MongoDB and PostgreSQL, handling both real-time and batch migrations.