Database Migration Guide: Oracle, SQL Server, PostgreSQL, and MySQL

Database migration projects can be complex endeavors that require careful planning and execution. This guide provides a detailed roadmap for migrating between the four most popular relational database systems: Oracle, Microsoft SQL Server, PostgreSQL, and MySQL. We’ll explore the unique challenges and considerations for each migration path, along with practical solutions and code examples.

Contents

Quick Reference: Database System Comparison

Let’s begin with a comprehensive comparison of key features and considerations across these database systems:

Feature	Oracle	SQL Server	PostgreSQL	MySQL
Data Types	NUMBER, VARCHAR2, CLOB, BLOB	INT, VARCHAR, TEXT, VARBINARY	INTEGER, VARCHAR, TEXT, BYTEA	INT, VARCHAR, TEXT, BLOB
Auto-increment	SEQUENCE	IDENTITY	SERIAL/SEQUENCE	AUTO_INCREMENT
Default Schema	No default	dbo	public	No schema support
Case Sensitivity	Case-sensitive	Case-insensitive	Case-sensitive	Platform-dependent
Stored Procedures	PL/SQL	T-SQL	PL/pgSQL	SQL/PSM
NULL Sorting	NULLS LAST default	NULLS FIRST default	Configurable	NULLS FIRST default
Outer Join Syntax	(+) or ANSI	ANSI only	ANSI only	ANSI only
Cost	Commercial	Commercial	Free	Free
JSON Support	Native	Native	Native (JSONB)	Native
Partitioning	Native	Native	Native	Available in Enterprise
Full-Text Search	Oracle Text	Full-Text Search	Built-in	Built-in

General Migration Process

Before diving into specific migration paths, let’s understand the general migration workflow that applies to all scenarios:

def general_migration_workflow(source_db, target_db):
    """
    A general framework for database migration
    """
    # Phase 1: Assessment and Planning
    schema_analysis = analyze_source_schema(source_db)
    data_volume = calculate_data_volume(source_db)
    dependencies = identify_dependencies(source_db)

    # Phase 2: Schema Conversion
    target_schema = convert_schema(schema_analysis, source_db, target_db)

    # Phase 3: Data Migration
    migration_batches = plan_data_migration(data_volume)

    # Phase 4: Object Migration
    stored_procedures = convert_stored_procedures(source_db, target_db)
    views = convert_views(source_db, target_db)
    triggers = convert_triggers(source_db, target_db)

    # Phase 5: Testing and Validation
    validate_schema(target_schema)
    validate_data_integrity(source_db, target_db)

    return MigrationPlan(
        schema=target_schema,
        procedures=stored_procedures,
        batches=migration_batches
    )

Oracle to PostgreSQL Migration

Oracle to PostgreSQL is one of the most common migration paths due to cost considerations. Here’s how to handle key differences:

Data Type Mapping

-- Oracle to PostgreSQL type mapping function
CREATE OR REPLACE FUNCTION map_oracle_to_pg_type(
    oracle_type VARCHAR2,
    data_length NUMBER,
    data_precision NUMBER,
    data_scale NUMBER
) RETURN VARCHAR2 IS
BEGIN
    CASE oracle_type
        WHEN 'NUMBER' THEN
            IF data_precision IS NULL THEN
                RETURN 'double precision';
            ELSIF data_scale = 0 THEN
                IF data_precision <= 4 THEN
                    RETURN 'smallint';
                ELSIF data_precision <= 9 THEN
                    RETURN 'integer';
                ELSIF data_precision <= 18 THEN
                    RETURN 'bigint';
                ELSE
                    RETURN 'numeric';
                END IF;
            ELSE
                RETURN 'numeric(' || data_precision || ',' || data_scale || ')';
            END IF;
        WHEN 'VARCHAR2' THEN
            RETURN 'varchar(' || data_length || ')';
        WHEN 'DATE' THEN
            RETURN 'timestamp';
        WHEN 'CLOB' THEN
            RETURN 'text';
        WHEN 'BLOB' THEN
            RETURN 'bytea';
        ELSE
            RETURN 'text';
    END CASE;
END;
/

PL/SQL to PL/pgSQL Conversion

-- Oracle PL/SQL
CREATE OR REPLACE PROCEDURE update_customer_status(
    p_customer_id IN NUMBER,
    p_status IN VARCHAR2
) IS
BEGIN
    UPDATE customers
    SET status = p_status
    WHERE customer_id = p_customer_id;

    IF SQL%ROWCOUNT = 0 THEN
        raise_application_error(-20001, 'Customer not found');
    END IF;
    COMMIT;
END;
/

-- PostgreSQL PL/pgSQL
CREATE OR REPLACE FUNCTION update_customer_status(
    p_customer_id INTEGER,
    p_status VARCHAR
) RETURNS VOID AS $$
BEGIN
    UPDATE customers
    SET status = p_status
    WHERE customer_id = p_customer_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Customer not found'
            USING ERRCODE = '23505';
    END IF;
END;
$$ LANGUAGE plpgsql;

SQL Server to PostgreSQL Migration

SQL Server to PostgreSQL migrations require careful handling of T-SQL specific features:

Handling Identity Columns

-- SQL Server
CREATE TABLE orders (
    order_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT GETDATE()
);

-- PostgreSQL equivalent
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Converting Table-Valued Functions

-- SQL Server
CREATE FUNCTION get_customer_orders(@customer_id INT)
RETURNS TABLE
AS
RETURN
(
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = @customer_id
);

-- PostgreSQL equivalent
CREATE OR REPLACE FUNCTION get_customer_orders(p_customer_id INTEGER)
RETURNS TABLE (
    order_id INTEGER,
    order_date TIMESTAMP,
    total_amount NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    WHERE o.customer_id = p_customer_id;
END;
$$ LANGUAGE plpgsql;

MySQL to PostgreSQL Migration

MySQL to PostgreSQL migration requires attention to data type differences and syntax variations:

Handling AUTO_INCREMENT

def convert_mysql_auto_increment(mysql_schema):
    """
    Converts MySQL AUTO_INCREMENT to PostgreSQL SERIAL
    """
    converted_schema = re.sub(
        r'int.*AUTO_INCREMENT',
        'SERIAL',
        mysql_schema,
        flags=re.IGNORECASE
    )
    return converted_schema

Converting Group Concatenation

-- MySQL
SELECT customer_id,
       GROUP_CONCAT(order_id ORDER BY order_date DESC) as order_list
FROM orders
GROUP BY customer_id;

-- PostgreSQL equivalent
SELECT customer_id,
       STRING_AGG(order_id::text, ',' ORDER BY order_date DESC) as order_list
FROM orders
GROUP BY customer_id;

Special Considerations for Each Migration Path

Oracle to PostgreSQL

When migrating from Oracle to PostgreSQL, pay special attention to:

-- Sequence handling
-- Oracle
CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1;
CREATE TRIGGER order_id_trigger
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SELECT order_seq.NEXTVAL INTO :NEW.order_id FROM dual;
END;

-- PostgreSQL
CREATE SEQUENCE order_seq START 1;
ALTER TABLE orders ALTER COLUMN order_id SET DEFAULT nextval('order_seq');

SQL Server to PostgreSQL

SQL Server migrations require careful handling of:

-- DateTime conversion
-- SQL Server
SELECT DATEADD(day, 1, order_date) FROM orders;

-- PostgreSQL
SELECT order_date + INTERVAL '1 day' FROM orders;

-- Full-text search conversion
-- SQL Server
SELECT * FROM products 
WHERE CONTAINS(description, 'searchterm');

-- PostgreSQL
SELECT * FROM products 
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'searchterm');

MySQL to PostgreSQL

MySQL migrations need special attention for:

-- ENUM handling
-- MySQL
CREATE TABLE orders (
    status ENUM('pending', 'processing', 'completed')
);

-- PostgreSQL
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'completed');
CREATE TABLE orders (
    status order_status
);

Post-Migration Tasks

After completing any migration, perform these essential tasks:

-- Update statistics
ANALYZE VERBOSE;

-- Rebuild indexes
REINDEX DATABASE your_database;

-- Validate foreign keys
SELECT conname, contype, 
       pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.contype = 'f'
ORDER BY conname;

-- Check for invalid indexes
SELECT schemaname, tablename, indexname, 
       pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE indexname IN (
    SELECT indexrelname 
    FROM pg_stat_user_indexes 
    WHERE idx_scan = 0
);

Performance Monitoring and Optimization

After migration, monitor and optimize performance:

-- Check for slow queries
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>'
AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

-- Identify missing indexes
SELECT schemaname, tablename, 
       round(heap_blks_hit * 100.0 / nullif(heap_blks_hit + heap_blks_read, 0), 1) as cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY cache_hit_ratio DESC;

Conclusion

Database migration is a complex process that requires careful planning and execution. The key to success lies in understanding the unique characteristics of both the source and target databases. This guide has covered the essential aspects of migrating between major database systems, but remember that each migration project may have its own specific requirements and challenges.

Remember these key points for a successful migration:

Always maintain comprehensive backups throughout the migration process
Test the migration thoroughly in a staging environment
Plan for adequate downtime during the final migration
Monitor performance closely after migration
Keep application code updated to leverage target database features
Document all changes and maintain detailed migration logs

By following this guide and adapting it to your specific needs, you can ensure a successful database migration project while minimizing risks and downtime.