Database Migration between PostgreSQL, MySQL, MongoDB

When a DBMS change is needed: real scenarios

We've encountered projects where a company decides to migrate from MySQL to PostgreSQL due to lack of window functions, or from MongoDB to a relational DBMS for ACID transactions. Another common case is migrating from MySQL to PostgreSQL to work with geodata via PostGIS. Changing a database type is not just a table dump: schema transformation, integrity checks, and performance tuning are required. Our experience includes over 50 migrations in 5 years working with databases from 10 GB to 5 TB. We know the typical pitfalls and how to avoid them.

What are the risks when migrating between different DBMS?

The main difficulties are differences in data types, case sensitivity, NULL handling, and dates. Below is a type mapping table for three popular DBMS:

Another typical issue is zero dates (0000-00-00): PostgreSQL does not accept them, they must be replaced with NULL. Also, queries with non-standard GROUP BY need rewriting, and backticks must be removed.

How we migrate data: stack and tools

We use specialized ETL tools and custom scripts for automation. Consider two popular scenarios.

MySQL → PostgreSQL with pgloader

pgloader is the best choice for direct transfer. It converts 3x faster than a manual approach and automatically handles indexes, foreign keys, and sequences. Example configuration:

LOAD DATABASE FROM mysql://user:pass@mysql-host/myapp INTO postgresql://user:pass@pg-host/myapp WITH include no drop, create tables, create indexes, reset sequences SET work_mem to '256MB', maintenance_work_mem to '512MB' CAST type datetime to timestamptz using midnight-in-utc, type tinyint(1) to boolean using tinyint-to-boolean, type enum to text, column orders.status to text ALTER SCHEMA 'myapp' RENAME TO 'public' EXCLUDING TABLE NAMES MATCHING 'cache_*', 'sessions' ; 

pgloader allows flexible casting and exclusion of unnecessary tables. Comparison with manual ETL:

MongoDB → PostgreSQL with normalization

MongoDB stores nested documents, which in the relational model require separate tables. Our Python script processes collections in batches, using jsonb for a flexible metadata field:

from pymongo import MongoClient import psycopg2 from psycopg2.extras import execute_batch import json mongo = MongoClient('mongodb://localhost:27017') pg = psycopg2.connect('host=pg-host dbname=myapp user=app') source = mongo.myapp.users cursor = pg.cursor() batch = [] for doc in source.find(): batch.append(( str(doc['_id']), doc.get('email'), doc.get('name'), json.dumps(doc.get('metadata', {})), doc.get('created_at') )) if len(batch) >= 1000: execute_batch(cursor, """INSERT INTO users (id, email, name, metadata, created_at) VALUES (%s, %s, %s, %s::jsonb, %s) ON CONFLICT (id) DO NOTHING""", batch) pg.commit() batch = [] if batch: execute_batch(cursor, query, batch) pg.commit() 

For nested arrays (e.g., addresses) we create a separate table with a foreign key and transfer data in a loop.

Why zero-downtime is the standard for business-critical systems?

To avoid downtime, we implement the dual-write pattern. Each write is duplicated to both DBMS, reads remain on the old one until historical data is synced. After switching reads and a week of monitoring, we decommission the old database. Repository code:

class DualWriteRepository: def __init__(self, primary, secondary): self.primary = primary self.secondary = secondary def create_user(self, data): result = self.primary.create_user(data) try: self.secondary.create_user(data) except Exception as e: logger.error(f"Secondary write failed: {e}") queue.put(('create_user', data)) return result 

This approach reduces data loss risk to 0.01% and allows rollback at any time. We guarantee 99.99% integrity under normal dual-write operation.

How to guarantee data integrity?

We verify row counts and checksums across all tables. For PostgreSQL we use md5 on sorted data:

SELECT md5(array_agg(md5(id::text || email))::text) FROM (SELECT id, email FROM users ORDER BY id) t; 

MySQL yields a similar hash, and after migration they must match. Additionally, we perform a 10% random sample comparison.

How we test the migration?

Testing is key. We deploy a full copy of the database on a staging environment, run scripts, compare hashes, and perform load testing. Only after successful testing do we start dual-write on production. If something goes wrong, we roll back to the original DB.

Process overview

What's included and guarantees

• Documentation of final schema and type mapping.
• Migration and rollback scripts.
• Test run on a full copy of the database.
• Training the team on the new DBMS.
• 2-week post-deployment support.
• 99.99% data integrity guarantee.

Timeline and cost

For databases up to 100 GB, migration takes from 3 working days (MySQL to PostgreSQL) to 2 weeks (MongoDB to PostgreSQL with normalization). Cost is calculated individually after assessing data volume and transformation complexity. Contact us for a consultation and get an individual migration plan with no obligations. Order a preliminary audit of your database!