Database migrations are among the highest-risk projects in enterprise IT. Get it wrong, and you're looking at data loss, application outages, and career-limiting moments. Get it right, and you unlock cost savings, better performance, and architectural flexibility. This playbook shares what we've learned from migrating databases across DACH enterprises.
Why Organizations Migrate Databases
Understanding your motivation shapes the approach:
- Cost reduction: Moving from Oracle to PostgreSQL or cloud-native databases to reduce licensing costs
- Cloud adoption: Migrating to cloud-managed services like RDS, Azure SQL, or Cloud SQL
- Modernization: Moving from legacy databases to modern architectures as part of application modernization
- Performance: Adopting specialized databases for specific workloads (time-series, graph, document)
- Strategic exit: Reducing dependency on a vendor for negotiating leverage
The cheapest database migration is the one you don't have to do. Before migrating, ask: does this database actually need to move?
Migration Strategies
Big Bang Migration
Migrate everything at once during a planned outage window.
- When it works: Smaller databases, acceptable downtime, simpler applications
- Risks: All-or-nothing pressure, limited rollback options, timing constraints
- Key success factor: Extensive rehearsal with production-like data
Parallel Running
Run both databases simultaneously, sync data, gradually shift traffic.
- When it works: Mission-critical systems, zero-downtime requirements
- Risks: Complexity of keeping systems in sync, cost of dual infrastructure
- Key success factor: Robust data synchronization and conflict resolution
Strangler Pattern
Migrate functionality piece by piece, routing specific operations to the new database.
- When it works: Complex applications, extended timeline acceptable, microservices architecture
- Risks: Long coexistence period, integration complexity between databases
- Key success factor: Clear boundaries between migrated and non-migrated functionality
Oracle to PostgreSQL: The Common Journey
This is the most frequent migration path we see in DACH enterprises. Key considerations:
Schema Conversion
- Data types: Oracle NUMBER to PostgreSQL numeric, CLOB to TEXT, DATE vs TIMESTAMP
- Sequences: Different syntax for sequence generation
- Indexes: Bitmap indexes don't exist in PostgreSQL; reconsider indexing strategy
- Partitioning: Different partitioning models and syntax
PL/SQL to PL/pgSQL
This is often the biggest effort:
- Packages: PostgreSQL doesn't have packages; refactor to schemas and functions
- Cursors: Different cursor behavior and syntax
- Exception handling: Different exception model
- Bulk operations: No direct FORALL equivalent; use different patterns
Oracle-Specific Features
Features without direct PostgreSQL equivalents:
- Materialized views: Available but with different refresh mechanisms
- Virtual columns: Use generated columns
- Synonyms: Use schemas and search_path
- Database links: Use postgres_fdw
Cloud Database Migration Considerations
Moving to managed cloud databases (RDS, Azure SQL, Cloud SQL) adds considerations:
What You Gain
- Managed patching, backups, and high availability
- Easy scaling (especially read replicas)
- Integration with cloud services and identity
- Reduced DBA operational burden
What You Lose
- Direct OS access and deep tuning control
- Some advanced features (varies by platform)
- Flexibility in maintenance windows
- Control over minor version upgrades
The Migration Playbook
Phase 1: Assessment (4-8 weeks)
- Inventory all database objects, dependencies, and connections
- Analyze PL/SQL code complexity and conversion effort
- Identify Oracle-specific features requiring alternatives
- Profile query patterns and performance baselines
- Estimate effort and timeline
Phase 2: Schema and Code Conversion (variable)
- Convert schema using automated tools + manual refinement
- Convert PL/SQL to PL/pgSQL (usually manual, labor-intensive)
- Update application database access layers
- Implement equivalent functionality for Oracle-specific features
Phase 3: Testing (4-8 weeks)
- Functional testing with production-like data
- Performance testing and query optimization
- Integration testing with dependent systems
- Failover and recovery testing
- Load testing under production conditions
Phase 4: Migration Execution
- Final data sync and cutover
- Application connection switching
- Validation and smoke testing
- Performance monitoring
- Rollback readiness (for defined period)
Common Pitfalls and How to Avoid Them
- Underestimating PL/SQL complexity: Always allocate more time for stored procedure conversion than you think you need
- Ignoring implicit behavior differences: NULL handling, string comparison, date arithmetic can subtly differ
- Testing with sanitized data: Production edge cases won't appear in test data; use realistic volumes
- Forgetting about the application: Database migration without application testing is incomplete
- Insufficient rollback planning: Know exactly how you'll revert if something goes wrong
When Not to Migrate
Sometimes the best migration strategy is not to migrate:
- Application is stable and maintenance-mode only
- Migration cost exceeds 3-5 years of licensing savings
- Critical application with extremely low risk tolerance
- Deep Oracle-specific feature dependencies
Getting Started
Database migration success requires careful planning, realistic timelines, and experienced guidance. Don't let cost pressure push you into an underprepared migration.
Planning a database migration? Our team has migrated enterprise databases across DACH—from Oracle to PostgreSQL, on-premises to cloud, and legacy to modern. We can help assess your migration, plan the approach, and execute with confidence.