Heterogeneous Database Migration
What you'll learn: How to migrate data between different database engines (e.g., Oracle to PostgreSQL) by converting schemas and transforming data formats.
What Makes It "Heterogeneous"
Unlike homogeneous migration where you move from Oracle 11g to Oracle 19c (same engine family), heterogeneous migration means crossing engine boundaries—like moving from Microsoft SQL Server to Amazon Aurora MySQL, or from Oracle to PostgreSQL. This is fundamentally more complex because different engines speak different "dialects" of SQL, store data differently, and have unique features that don't directly map to other systems.
The Schema Conversion Challenge
Think of it like translating a technical manual from German to Japanese—not only must you change the language, but you must also adapt idioms, cultural references, and measurement systems. Database engines have their own:
- Data types: Oracle's
NUMBERvs PostgreSQL'sNUMERIC - Stored procedures: PL/SQL vs T-SQL vs PL/pgSQL
- Functions and operators: String concatenation, date math
- Indexing strategies: Bitmap indexes, function-based indexes
- Constraints and triggers: Different syntax and capabilities
Schema Conversion Tools
Cloud providers offer schema conversion tools (like AWS Schema Conversion Tool or Azure Database Migration Service) that automatically analyze your source database and generate equivalent schema definitions for the target engine. These tools identify incompatibilities, suggest alternatives, and flag code that requires manual rewriting.
After conversion, the actual data migration uses continuous data replication (CDC from lesson 2809) to keep source and target synchronized during the transition period.
Migration Workflow
Assessment → Schema conversion → Manual code remediation → Data migration → Testing → Cutover. The schema conversion phase often reveals that 80-90% converts automatically, but the remaining 10-20% requires developer expertise to rewrite business logic.
Key Takeaway: Heterogeneous database migration requires specialized tools to convert schemas and transform data between different database engines, with significant effort on code remediation and compatibility testing.