How to Migrate from MySQL to PostgreSQL: A Complete Step-by-Step Guide

Migrating from MySQL to PostgreSQL is a significant infrastructure decision that can unlock better performance, advanced features, and stronger data integrity. This guide provides a systematic approach to migration that minimizes downtime and risk.

Why Migrate to PostgreSQL?

• Better Standards Compliance — PostgreSQL follows SQL standards more closely
• Advanced Data Types — Native JSON, arrays, ranges, and custom types
• Superior Concurrency — MVCC without table-level locking issues
• Extensibility — Extensions like PostGIS, pg_trgm, and full-text search
• Better Query Planner — More sophisticated optimization strategies

Phase 1: Assessment and Planning

Inventory Your MySQL Database

-- List all databases
SHOW DATABASES;

-- List tables and sizes
SELECT
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY data_length DESC;

Identify Incompatibilities

Common MySQL features that need conversion:

• AUTO_INCREMENT → SERIAL or GENERATED ALWAYS AS IDENTITY
• ENUM columns → PostgreSQL ENUM types or check constraints
• TINYINT(1) for booleans → BOOLEAN
• DATETIME → TIMESTAMP
• UNSIGNED integers → Not supported, use check constraints
• Backtick quoting → Double-quote quoting

Phase 2: Schema Migration

Using pgloader (Recommended)

# Install pgloader
sudo apt install pgloader

# Create migration configuration
cat > migrate.load << 'EOF'
LOAD DATABASE
     FROM mysql://user:pass@localhost/mydb
     INTO postgresql://user:pass@localhost/mydb

WITH include drop, create tables, create indexes,
     reset sequences, downcase identifiers

SET work_mem to '128MB',
    maintenance_work_mem to '512MB'

ALTER SCHEMA 'mydb' RENAME TO 'public';
EOF

# Run migration
pgloader migrate.load

Manual Schema Conversion

# Export MySQL schema
mysqldump --no-data --compatible=postgresql mydb > schema.sql

# Common sed replacements
sed -i 's/AUTO_INCREMENT/GENERATED ALWAYS AS IDENTITY/g' schema.sql
sed -i 's/`//g' schema.sql
sed -i "s/ENGINE=InnoDB.*//g" schema.sql
sed -i 's/TINYINT(1)/BOOLEAN/g' schema.sql
sed -i 's/DATETIME/TIMESTAMP/g' schema.sql

Phase 3: Data Migration

# Export data from MySQL
mysqldump --compatible=postgresql --no-create-info mydb > data.sql

# Or use pgloader for automated data transfer
pgloader mysql://user:pass@localhost/mydb \
         postgresql://user:pass@localhost/mydb

Phase 4: Application Code Changes

Common code changes needed:

# MySQL syntax → PostgreSQL syntax

# LIMIT with OFFSET
# MySQL: LIMIT 10, 20
# PostgreSQL: LIMIT 20 OFFSET 10

# String concatenation
# MySQL: CONCAT(a, b)
# PostgreSQL: a || b (also supports CONCAT)

# Date functions
# MySQL: NOW()
# PostgreSQL: NOW() or CURRENT_TIMESTAMP

# IF() function
# MySQL: IF(condition, true_val, false_val)
# PostgreSQL: CASE WHEN condition THEN true_val ELSE false_val END

# GROUP_CONCAT
# MySQL: GROUP_CONCAT(name SEPARATOR ', ')
# PostgreSQL: STRING_AGG(name, ', ')

Phase 5: Testing

• Compare row counts across all tables
• Run application test suites against PostgreSQL
• Test all stored procedures and triggers
• Performance benchmark critical queries
• Verify character encoding (UTF-8)
• Test backup and restore procedures

Phase 6: Cutover

1. Set MySQL to read-only mode
2. Run final data sync
3. Verify data consistency
4. Update application connection strings
5. Monitor closely for 24-48 hours
6. Keep MySQL available for rollback

Further Reading

• PostgreSQL Database Setup
• MySQL Fundamentals
• SQL for Absolute Beginners