Database Migration

Database Migration

Provides comprehensive guidance for migrating databases between engines, versions, platforms, and architectures. Covers both schema and data migration with strategies for minimizing downtime and ensuring data integrity.

Migration Decision Tree

1. Identify Migration Type:

• Same engine (PostgreSQL → PostgreSQL)? → Homogeneous migration
• Different engine (Oracle → PostgreSQL)? → Heterogeneous migration
• Version upgrade only? → In-place or dump/restore
• Cloud migration? → Consider cloud-native tools

2. Assess Downtime Requirements:

• Can tolerate hours of downtime? → Dump and restore
• Need minimal downtime (minutes)? → Replication with cutover
• Require zero downtime? → See zero-downtime-migration-strategies.md

3. Choose Migration Path:

• Load migration-types.md for detailed migration approaches
• For cloud migrations, load cloud-specific-migrations.md

Core Migration Workflow

Step 1: Assessment and Planning

Analyze Source Database:

1. Document current database version, size, and complexity 2. Identify dependencies (applications, services, integrations) 3. Review schema: tables, indexes, constraints, triggers, procedures 4. Assess data volume and growth rate 5. Document current performance baselines

Define Requirements:

• Migration type (homogeneous vs heterogeneous)
• Acceptable downtime window
• Data integrity requirements
• Compliance and security requirements
• Rollback criteria

Output: Migration plan with approach, timeline, and resources

Step 2: Environment Setup

Prepare Target Environment:

1. Provision target database with appropriate sizing 2. Configure network connectivity and security 3. Set up monitoring and logging 4. Create test and staging environments matching production

Prepare Migration Tools:

• Native tools (pg_dump, mysqldump, SQL Server bcp)
• Cloud provider tools (AWS DMS, GCP Database Migration Service)
• Third-party tools (see tools-reference.md)

Step 3: Schema Migration

For Homogeneous Migration:

1. Export schema using native tools 2. Review and optimize schema for target version 3. Apply schema to target database 4. Verify all objects created successfully

For Heterogeneous Migration:

1. Analyze schema compatibility issues 2. Convert data types, stored procedures, triggers 3. Adapt SQL dialects and syntax 4. Test converted schema thoroughly

Load migration-types.md for engine-specific schema conversion guidance.

Step 4: Data Migration

Choose Data Migration Strategy:

Option A: Dump and Restore (Full Downtime)

1. Stop application writes
2. Create full backup of source database
3. Transfer backup to target environment
4. Restore to target database
5. Verify data integrity (row counts, checksums)
6. Update application connection strings
7. Resume operations

Best for: Smaller databases, acceptable downtime windows

Option B: Replication (Minimal Downtime)

1. Set up replication from source to target
2. Monitor replication lag until synchronized
3. Schedule cutover window
4. Stop writes briefly (minutes)
5. Verify replication is caught up
6. Promote target to primary
7. Update application connections
8. Resume operations

Best for: Large databases, minimal downtime requirements

Load zero-downtime-migration-strategies.md for advanced zero-downtime patterns.

Step 5: Validation and Testing

Validate Data Migration:

1. Compare row counts between source and target 2. Verify data integrity (checksums, sample queries) 3. Test application functionality against target database 4. Validate performance meets requirements 5. Check all constraints, indexes, and relationships

Testing Checklist:

• [ ] All tables migrated with correct row counts
• [ ] Schema objects (indexes, constraints, triggers) present
• [ ] Data types converted correctly
• [ ] Application queries execute successfully
• [ ] Performance meets or exceeds baseline
• [ ] Backup and restore procedures work

Load common-issues-and-solutions.md if encountering problems.

Step 6: Cutover Planning

1. Create detailed cutover runbook with specific timings 2. Define rollback criteria and procedures (load rollback-procedures.md) 3. Coordinate with stakeholders (apps, operations, business) 4. Schedule maintenance window 5. Prepare communication plan

Load migration-phases.md for detailed phase-by-phase execution guidance.

Step 7: Post-Migration

Immediate (Day 1):

1. Monitor performance metrics and error rates 2. Validate application functionality 3. Keep source database available (read-only) as safety net 4. Document any issues and resolutions

Short-term (Week 1-2):

1. Continue monitoring for issues 2. Optimize indexes and queries if needed 3. Tune database configuration for workload 4. Conduct parallel run if applicable

Long-term:

1. Validate backup and restore procedures 2. Update disaster recovery plans 3. Document final configuration and lessons learned 4. Decommission source database after retention period

Key Considerations

Planning Guidelines:

• Allow 2-3x estimated time for heterogeneous migrations
• Plan for extended parallel run period (1-4 weeks minimum)
• Database migration often triggers application code changes
• Coordinate with application migration when possible
• Consider phased approach: read replica → read/write split → full cutover

Critical Success Factors:

• ✅ Multiple backups before migration
• ✅ Test migration in staging environment first
• ✅ Monitor metrics during migration (lag, throughput, errors)
• ✅ Always have rollback plan ready
• ✅ Document all steps, issues, and decisions
• ✅ Encrypt data in transit and at rest
• ✅ Rotate credentials after migration

Load best-practices.md for comprehensive best practices.

Reference Files

Load these references based on specific needs:

• [migration-types.md](references/migration-types.md) - Detailed guidance on homogeneous vs heterogeneous migrations, engine-specific conversion patterns
• [migration-phases.md](references/migration-phases.md) - Phase-by-phase execution details with timelines and dependencies
• [zero-downtime-migration-strategies.md](references/zero-downtime-migration-strategies.md) - Advanced patterns for zero-downtime migrations (dual writes, event streaming, phased cutover)
• [cloud-specific-migrations.md](references/cloud-specific-migrations.md) - AWS DMS, GCP Database Migration Service, Azure Database Migration Service
• [tools-reference.md](references/tools-reference.md) - Native tools, cloud provider services, third-party migration tools
• [rollback-procedures.md](references/rollback-procedures.md) - Step-by-step rollback procedures for different migration strategies
• [common-issues-and-solutions.md](references/common-issues-and-solutions.md) - Troubleshooting guide for common migration problems
• [best-practices.md](references/best-practices.md) - Comprehensive best practices checklist

Best Practices

✅ Multiple Backups: Take backups before migration, keep source DB until validated ✅ Test in Staging: Practice migration in non-production first ✅ Monitor Metrics: Watch lag, throughput, errors during migration ✅ Incremental Approach: Migrate in phases if possible ✅ Validate Thoroughly: Check schema, data, and functionality ✅ Plan for Rollback: Always have a way back ✅ Document Everything: Record steps, issues, decisions ✅ Parallel Run: Run both databases temporarily when possible ✅ Performance Tuning: Optimize indexes and queries in target ✅ Security: Encrypt data in transit, rotate credentials

Cloud-Specific Migrations

AWS RDS Migration

# On-Premise PostgreSQL → AWS RDS PostgreSQL

# Option 1: pg_dump/pg_restore
pg_dump -h source-host -U user -d dbname -F c -f dump.backup
pg_restore -h rds-endpoint -U user -d dbname dump.backup

# Option 2: AWS DMS
# Use AWS Console or CLI to create migration task

# Option 3: RDS Snapshot (for RDS to RDS)
aws rds create-db-snapshot \
  --db-instance-identifier source-db \
  --db-snapshot-identifier migration-snapshot

aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier target-db \
  --db-snapshot-identifier migration-snapshot

GCP Cloud SQL Migration

# MySQL → Cloud SQL MySQL

# Using Database Migration Service (DMS)
gcloud sql connect-db-instances create CONNECTION_PROFILE \
  --source=mysql \
  --host=SOURCE_IP \
  --port=3306 \
  --username=USER

# Or using mysqldump
mysqldump -h source-host -u user -p --databases dbname \
  --single-transaction --set-gtid-purged=OFF > dump.sql

# Import to Cloud SQL
gcloud sql import sql INSTANCE_NAME gs://BUCKET/dump.sql \
  --database=DATABASE_NAME

Azure Database Migration

# SQL Server → Azure SQL Database

# Using Azure Database Migration Service
# Or using BACPAC export/import

# Export
SqlPackage.exe /Action:Export \
  /SourceServerName:source-server \
  /SourceDatabaseName:mydb \
  /TargetFile:mydb.bacpac

# Upload to Azure Blob Storage
az storage blob upload \
  --account-name mystorageaccount \
  --container-name backups \
  --file mydb.bacpac

# Import to Azure SQL
az sql db import \
  --resource-group mygroup \
  --server target-server \
  --name mydb \
  --storage-key-type StorageAccessKey \
  --storage-key STORAGE_KEY \
  --storage-uri https://mystorageaccount.blob.core.windows.net/backups/mydb.bacpac

Common Issues and Solutions

Issue: Replication Lag Too High
Solution:
- Increase network bandwidth
- Optimize source DB performance
- Reduce concurrent writes during migration
- Use parallel replication streams

Issue: Data Type Conversion Errors
Solution:
- Review and fix schema mapping
- Handle NULL values appropriately
- Cast incompatible types explicitly
- Test with sample data first

Issue: Foreign Key Constraint Violations
Solution:
- Disable constraints during load
- Migrate in dependency order
- Load parent tables before child tables
- Re-enable and validate constraints after

Issue: Performance Worse on Target
Solution:
- Analyze query plans
- Create missing indexes
- Update database statistics
- Tune database parameters
- Consider partitioning

Migration Phases

Phase 1: Assessment and Planning

1. Database Inventory
   - Database size (tables, indexes, total GB)
   - Number of objects (tables, views, procedures, functions)
   - Dependencies (foreign keys, triggers)
   - Integration points
   - Peak usage patterns

2. Compatibility Analysis
   - Identify incompatible features
   - Map data types
   - Review stored procedures
   - Check SQL queries
   - Validate application framework

3. Performance Baseline
   - Query response times
   - Transaction throughput
   - Concurrent connections
   - Resource utilization
   - Batch job timings

4. Migration Strategy Selection
   - Downtime tolerance
   - Data volume
   - Budget constraints
   - Technical complexity
   - Risk tolerance

5. Timeline and Resources
   - Duration estimate
   - Team assignment
   - Tool selection
   - Budget allocation

Phase 2: Schema Migration

Automated Schema Conversion:

# Using pgLoader (MySQL to PostgreSQL)
pgloader mysql://user:pass@source-host/dbname \
          postgresql://user:pass@target-host/dbname

# Using AWS Schema Conversion Tool (SCT)
# GUI-based tool for Oracle/SQL Server to PostgreSQL/MySQL

# Using ora2pg (Oracle to PostgreSQL)
ora2pg -c ora2pg.conf -t TABLE -o schema.sql
ora2pg -c ora2pg.conf -t VIEW -o views.sql
ora2pg -c ora2pg.conf -t PROCEDURE -o procedures.sql

Manual Schema Conversion:

-- Oracle to PostgreSQL Example

-- Oracle
CREATE TABLE employees (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(100),
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2)
);

-- PostgreSQL
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    emp_name VARCHAR(100),
    hire_date DATE DEFAULT CURRENT_DATE,
    salary NUMERIC(10,2)
);

-- Oracle Sequence
CREATE SEQUENCE emp_seq START WITH 1;

-- PostgreSQL (using SERIAL or IDENTITY)
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    -- or
    emp_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

-- Oracle PL/SQL Function
CREATE OR REPLACE FUNCTION get_employee_count
RETURN NUMBER IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
END;

-- PostgreSQL PL/pgSQL Function
CREATE OR REPLACE FUNCTION get_employee_count()
RETURNS INTEGER AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

Data Type Mapping:

Oracle → PostgreSQL:
- NUMBER → NUMERIC or INTEGER
- VARCHAR2 → VARCHAR
- DATE → DATE or TIMESTAMP
- CLOB → TEXT
- BLOB → BYTEA
- RAW → BYTEA

MySQL → PostgreSQL:
- INT → INTEGER
- TINYINT → SMALLINT
- BIGINT → BIGINT
- VARCHAR → VARCHAR
- TEXT → TEXT
- DATETIME → TIMESTAMP
- ENUM → Custom TYPE or VARCHAR with CHECK

SQL Server → PostgreSQL:
- INT → INTEGER
- NVARCHAR → VARCHAR
- DATETIME → TIMESTAMP
- BIT → BOOLEAN
- UNIQUEIDENTIFIER → UUID
- IMAGE → BYTEA

Phase 3: Data Migration

Small Database (< 100 GB):

# PostgreSQL dump and restore
pg_dump -h source-host -U user -d dbname -F c -f dump.backup
pg_restore -h target-host -U user -d dbname dump.backup

# MySQL dump and restore
mysqldump -h source-host -u user -p dbname > dump.sql
mysql -h target-host -u user -p dbname < dump.sql

# With compression
mysqldump -h source-host -u user -p dbname | gzip > dump.sql.gz
gunzip < dump.sql.gz | mysql -h target-host -u user -p dbname

Large Database (> 100 GB):

# Parallel export/import (PostgreSQL)
pg_dump -h source-host -U user -d dbname -F d -j 8 -f dumpdir/
pg_restore -h target-host -U user -d dbname -j 8 dumpdir/

# Table-by-table migration
for table in $(psql -h source -U user -d db -t -c "SELECT tablename FROM pg_tables WHERE schemaname='public'"); do
    pg_dump -h source -U user -d db -t $table -F c -f ${table}.backup
    pg_restore -h target -U user -d db ${table}.backup
done

# Using COPY for fast data transfer
psql -h source -U user -d db -c "COPY table TO STDOUT" | \
psql -h target -U user -d db -c "COPY table FROM STDIN"

Cross-Engine Migration:

# MySQL to PostgreSQL using pgLoader
pgloader mysql://user:pass@mysql-host/dbname \
          postgresql://user:pass@pg-host/dbname

# With custom configuration
cat > migration.load <<EOF
LOAD DATABASE
    FROM mysql://user:pass@mysql-host/dbname
    INTO postgresql://user:pass@pg-host/dbname

WITH include drop, create tables, create indexes,
     reset sequences, workers = 8, concurrency = 1

CAST type datetime to timestamp
     drop default drop not null using zero-dates-to-null,
     
     type date drop not null drop default using zero-dates-to-null

EXCLUDING TABLE NAMES MATCHING 'temp_', 'backup_'

BEFORE LOAD DO
    \$\$ DROP SCHEMA IF EXISTS public CASCADE; \$\$,
    \$\$ CREATE SCHEMA public; \$\$;
EOF

pgloader migration.load

AWS Database Migration Service (DMS):

DMS Migration Types:

1. Full Load
   - Migrate all existing data
   - Database stays online
   - No ongoing replication

2. Full Load + CDC (Change Data Capture)
   - Initial full load
   - Continuous replication
   - Minimal downtime cutover

3. CDC Only
   - Replicate changes only
   - Assumes initial data already migrated
   - For validation or sync

DMS Setup:
1. Create replication instance
2. Create source endpoint
3. Create target endpoint
4. Create migration task
5. Start migration
6. Monitor progress
7. Perform cutover

Phase 4: Application Code Migration

Code Changes Required:

1. Connection Strings
   # Oracle
   jdbc:oracle:thin:@host:1521:SID
   
   # PostgreSQL
   jdbc:postgresql://host:5432/database

2. SQL Query Syntax
   -- Oracle
   SELECT * FROM employees WHERE ROWNUM <= 10;
   SELECT NVL(column, 'default') FROM table;
   SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD');
   
   -- PostgreSQL
   SELECT * FROM employees LIMIT 10;
   SELECT COALESCE(column, 'default') FROM table;
   SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD');

3. ORM Framework Updates
   # Hibernate
   # Update hibernate.dialect
   # Oracle
   hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
   
   # PostgreSQL
   hibernate.dialect=org.hibernate.dialect.PostgreSQL10Dialect

4. Stored Procedure Calls
   # Update procedure call syntax
   # May need to rewrite in target database language

Phase 5: Testing and Validation

Testing Checklist:

Schema Validation:
- [ ] All tables created
- [ ] Columns match (names, types, constraints)
- [ ] Indexes created
- [ ] Foreign keys established
- [ ] Views functional
- [ ] Stored procedures working
- [ ] Triggers active

Data Validation:
- [ ] Row counts match
  SELECT COUNT(*) FROM table;
  
- [ ] Data integrity checks
  SELECT MD5(string_agg(column, '')) FROM 
    (SELECT column FROM table ORDER BY id) t;
  
- [ ] Sample data comparison
- [ ] Referential integrity maintained
- [ ] No data truncation

Functional Testing:
- [ ] All CRUD operations work
- [ ] Queries return correct results
- [ ] Transactions commit/rollback properly
- [ ] Concurrency handling correct
- [ ] Batch jobs complete successfully

Performance Testing:
- [ ] Query response times acceptable
- [ ] Index usage optimal
- [ ] Connection pooling works
- [ ] Resource utilization normal
- [ ] Load testing passed

Application Testing:
- [ ] Application starts successfully
- [ ] All features functional
- [ ] Reports generate correctly
- [ ] APIs respond properly
- [ ] User acceptance testing passed

Migration Types

1. Homogeneous Database Migration

Definition: Same database engine, different version or platform

Examples:

• PostgreSQL 11 → PostgreSQL 15
• MySQL 5.7 → MySQL 8.0
• On-premise PostgreSQL → AWS RDS PostgreSQL
• Self-managed MySQL → Cloud SQL MySQL

Migration Strategy:

Approach 1: Dump and Restore
Pros: Simple, reliable, clean database
Cons: Downtime required

Steps:
1. Take backup using native tools
2. Stop application writes
3. Final incremental backup
4. Restore to target database
5. Verify data integrity
6. Update application connection
7. Resume operations

Approach 2: Replication
Pros: Minimal downtime, gradual cutover
Cons: More complex setup

Steps:
1. Set up replication (primary → replica)
2. Monitor replication lag
3. When synchronized, plan cutover
4. Stop writes briefly
5. Promote replica to primary
6. Update application connections
7. Resume operations

2. Heterogeneous Database Migration

Definition: Different database engines

Common Migrations:

Popular Paths:
- Oracle → PostgreSQL
- MySQL → PostgreSQL
- SQL Server → PostgreSQL
- MongoDB → PostgreSQL
- Oracle → MySQL
- SQL Server → MySQL

Reasons:
- Cost reduction (licensing)
- Open source preference
- Cloud-native features
- Better performance
- Vendor lock-in avoidance

Heterogeneous Migration Challenges:

Schema Differences:
- Data types (Oracle NUMBER → PostgreSQL NUMERIC)
- Stored procedures (PL/SQL → PL/pgSQL)
- Triggers and functions
- Sequences and auto-increment
- Index types
- Constraints and defaults

SQL Dialect Differences:
- Syntax variations
- Function names (NVL vs COALESCE)
- Date/time handling
- String concatenation (|| vs CONCAT)
- LIMIT vs ROWNUM vs TOP

Feature Gaps:
- Packages (Oracle) → Schemas (PostgreSQL)
- Synonyms → Views
- Materialized views
- Partitioning approaches
- Full-text search

Rollback Procedures

Rollback Triggers:
- Data corruption detected
- Performance degradation > 50%
- Application failures
- Data inconsistencies
- Unable to resolve issues within SLA

Rollback Steps:

1. Decision Point
   - Assess severity
   - Estimate fix time
   - Make rollback decision
   - Communicate to stakeholders

2. Stop New Database Operations
   - Stop application writes to new DB
   - Prevent further data divergence

3. Revert Application Connections
   - Update connection strings
   - Point to old database
   - Restart application if needed

4. Validate Old Database
   - Check data integrity
   - Verify service functionality
   - Monitor performance

5. Resume Operations
   - Announce rollback complete
   - Normal operations restored

6. Post-Rollback
   - Root cause analysis
   - Fix identified issues
   - Reschedule migration

Tools Reference

Cross-Platform Migration:
- pgLoader (MySQL/SQLite → PostgreSQL)
- AWS Database Migration Service (DMS)
- Azure Database Migration Service
- GCP Database Migration Service
- ora2pg (Oracle → PostgreSQL)
- SQLines (multi-database conversion)

Native Tools:
- pg_dump/pg_restore (PostgreSQL)
- mysqldump/mysql (MySQL)
- mongodump/mongorestore (MongoDB)
- SQL Server Management Studio
- Oracle Data Pump (expdp/impdp)

Schema Conversion:
- AWS Schema Conversion Tool (SCT)
- ora2pg
- pgloader (with schema conversion)
- ESF Database Migration Toolkit

Validation:
- data-diff (compare databases)
- Custom SQL scripts
- Checksum comparisons

Zero-Downtime Migration Strategies

Logical Replication

-- PostgreSQL Logical Replication Setup

-- On source (publisher)
ALTER SYSTEM SET wal_level = logical;
-- Restart PostgreSQL

CREATE PUBLICATION my_publication FOR ALL TABLES;

-- On target (subscriber)
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=source-host port=5432 dbname=mydb user=repuser password=pass'
    PUBLICATION my_publication;

-- Monitor replication
SELECT * FROM pg_stat_subscription;

-- Cutover
-- 1. Stop application writes
-- 2. Wait for replication to catch up
SELECT pg_current_wal_lsn();  -- On source
SELECT latest_end_lsn FROM pg_stat_subscription;  -- On target
-- When they match, replication is current

-- 3. Drop subscription
DROP SUBSCRIPTION my_subscription;

-- 4. Update application connection to target
-- 5. Resume operations

Dual-Write Strategy

Approach:
1. Write to both old and new databases
2. Read from old database initially
3. Validate data consistency
4. Switch reads to new database
5. Stop writing to old database

Pros:
- Very low downtime
- Easy rollback

Cons:
- Application complexity
- Requires code changes
- Potential data inconsistency

Implementation:
- Use application middleware
- Queue-based async writes
- Monitoring and reconciliation