About Database Migration

Database migration involves moving your database schema and data from one database system to another. This is common when modernizing applications, changing cloud providers, or optimizing for specific workloads.

Common Migration Scenarios

MySQL to PostgreSQL

One of the most common migrations, often driven by PostgreSQL's advanced features and standards compliance.

MySQL	PostgreSQL Equivalent
AUTO_INCREMENT	SERIAL or IDENTITY
TINYINT(1)	BOOLEAN
DATETIME	TIMESTAMP
BLOB	BYTEA
`backticks`	"double quotes"
ENGINE=InnoDB	(removed - not needed)

SQL Server to PostgreSQL

Common when moving from Windows-based infrastructure to Linux or cloud-native environments.

SQL Server	PostgreSQL Equivalent
IDENTITY(1,1)	SERIAL or IDENTITY
BIT	BOOLEAN
NVARCHAR	VARCHAR
DATETIME2	TIMESTAMP
UNIQUEIDENTIFIER	UUID
[square brackets]	"double quotes"

Oracle to PostgreSQL

Often motivated by cost reduction and avoiding vendor lock-in.

Oracle	PostgreSQL Equivalent
NUMBER	NUMERIC or INTEGER
VARCHAR2	VARCHAR
DATE	TIMESTAMP (Oracle DATE includes time)
CLOB	TEXT
RAW	BYTEA
SEQUENCE	SERIAL or SEQUENCE

Migration Steps

1. Schema Migration

Convert table definitions, data types, and constraints.

-- MySQL source
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- PostgreSQL target
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

2. Data Migration

Export data from source and import to target database.

# Export from MySQL
mysqldump -u user -p database > dump.sql

# Import to PostgreSQL (after schema conversion)
psql -U user -d database -f converted_schema.sql
psql -U user -d database -f converted_data.sql

3. Index Migration

Recreate indexes appropriate for the target database.

-- Create indexes after data import for better performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

4. Constraint Migration

Add foreign keys and constraints after data is loaded.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Migration Challenges

Auto-Increment Sequences

Different databases handle auto-incrementing IDs differently.

• MySQL: AUTO_INCREMENT
• PostgreSQL: SERIAL or IDENTITY
• SQL Server: IDENTITY(1,1)
• Oracle: SEQUENCE + TRIGGER

Date/Time Handling

Date and time types vary significantly between databases.

• Oracle's DATE includes time component
• MySQL's TIMESTAMP has limited range (1970-2038)
• PostgreSQL distinguishes between TIMESTAMP and TIMESTAMPTZ

String Case Sensitivity

• PostgreSQL folds unquoted identifiers to lowercase
• MySQL can be case-sensitive depending on OS and configuration
• SQL Server is case-insensitive by default (depends on collation)

Best Practices

• Test Thoroughly: Migrate to development environment first
• Validate Data: Compare row counts, checksums after migration
• Plan Downtime: Schedule migration during maintenance window
• Backup Everything: Keep full backups of source database
• Update Application Code: Adjust queries for target database
• Performance Test: Verify performance matches or exceeds source
• Monitor Closely: Watch for errors after going live

Migration Tools

Commercial Tools

• AWS Database Migration Service (DMS): Managed migration service
• pgLoader: Fast PostgreSQL data loader
• Ora2Pg: Oracle to PostgreSQL migration
• SQL Server Migration Assistant (SSMA): Microsoft's migration tool

Open Source Tools

• pgLoader: Migrate from MySQL, SQLite to PostgreSQL
• SQLines: SQL conversion tool
• Flyway/Liquibase: Schema versioning and migration

Post-Migration Checklist

• Verify all tables and data migrated successfully
• Test all application functionality
• Update connection strings and credentials
• Recreate views, stored procedures, functions
• Update monitoring and alerting
• Train team on new database system
• Document differences and workarounds
• Plan for eventual source database decommissioning