This project is a production-grade Python script designed to migrate large-scale relational data from Microsoft SQL Server to PostgreSQL reliably, efficiently, and with full logging and error handling.
- Migrated millions of rows across multiple tables (e.g.,
Users,Moves,GameWords,FloodControl, etc.). - Ensured data type compatibility, converting types like:
INTβINTEGERNVARCHAR(MAX)βTEXTDATETIMEβTIMESTAMPBITβBOOLEAN
- Performed batch-wise data transfer using optimized pagination via
OFFSET(with potential for key-based pagination later). - Included data transformation logic, such as converting
0/1to native PostgreSQL booleans. - Implemented detailed logging for traceability, error reporting, and performance tracking.
- Handled edge cases like naming mismatches, reserved keywords, and missing/null values.
- Python 3
pyodbcβ for SQL Server connectionpsycopg2β for PostgreSQL connectionloggingβ for real-time feedback and file-based logs
| Feature | Description |
|---|---|
| π Batch Processing | Customizable batch sizes for standard and large tables |
| β Type-safe Transformation | Automatically converts INT to BOOLEAN and other type adjustments |
| π§ Error Handling & Recovery | Rolls back transactions on failure to maintain data integrity |
| π Auto Logging | Logs every batch, table, and error with timestamps |
| π§ Schema-Agnostic | Dynamically detects columns and adjusts inserts to fit destination schema |
This script was deployed in a real-world scenario where an organization migrated its backend from SQL Server to PostgreSQL. The migration preserved:
- Full data integrity
- Field types and structure
- Operational continuity with minimal downtime
-
Fill in the configuration section:
- SQL Server and PostgreSQL connection details
- Tables to migrate
- Batch size preferences
-
Run the script:
python3 migrate.py- Check logs via
migration.logfor progress and results.