How to Migrate from MySQL to PostgreSQL

Migrating from MySQL to PostgreSQL is a significant undertaking that involves schema conversion, data migration, application code changes, and thorough testing. This guide provides a structured approach using both automated tools and manual techniques to ensure a successful migration with minimal downtime.

Why Migrate to PostgreSQL?

Common reasons for migrating include PostgreSQL's superior JSON support, advanced indexing (GiST, GIN, BRIN), better standards compliance, window functions, CTEs with write operations, and native partitioning. PostgreSQL also excels at complex analytical queries and has a stronger extension ecosystem with PostGIS, pg_trgm, and TimescaleDB.

Migration Planning

Assessment Phase

Start by cataloging your MySQL database:

-- Get database size
SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;

-- List all tables with row counts
SELECT table_name, table_rows, engine, table_collation
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY table_rows DESC;

-- Find MySQL-specific features in use
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'your_database';

-- Check triggers
SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers
WHERE trigger_schema = 'your_database';

Schema Conversion

Data Type Mapping

Key data type differences between MySQL and PostgreSQL:

MySQL                   → PostgreSQL
─────────────────────────────────────────
TINYINT(1) / BOOLEAN    → BOOLEAN
TINYINT                 → SMALLINT
INT AUTO_INCREMENT      → SERIAL or GENERATED ALWAYS AS IDENTITY
BIGINT AUTO_INCREMENT   → BIGSERIAL
DOUBLE                  → DOUBLE PRECISION
DATETIME                → TIMESTAMP
TIMESTAMP               → TIMESTAMPTZ
TEXT / MEDIUMTEXT        → TEXT
BLOB / MEDIUMBLOB       → BYTEA
ENUM('a','b','c')       → CREATE TYPE ... AS ENUM
SET('a','b','c')        → TEXT[] (array)
JSON                    → JSONB (preferred)
UNSIGNED INT            → INT (add CHECK constraint)

Using pgloader for Automated Migration

pgloader is the recommended tool for MySQL-to-PostgreSQL migration. It handles schema conversion, data type mapping, and data loading in a single step:

# Install pgloader
sudo apt install pgloader  # Ubuntu/Debian
sudo dnf install pgloader  # Fedora/RHEL

# Create migration configuration
cat > migration.load <<EOF
LOAD DATABASE
    FROM mysql://myuser:mypass@localhost/mydb
    INTO postgresql://pguser:pgpass@localhost/pgdb

WITH include drop,
     create tables,
     create indexes,
     reset sequences,
     workers = 4,
     concurrency = 2,
     batch rows = 10000

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

CAST type datetime to timestamptz using zero-dates-to-null,
     type tinyint to boolean using tinyint-to-boolean

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

# Run the migration
pgloader migration.load

Manual Schema Conversion

For more control, dump the MySQL schema and convert it manually:

# Dump MySQL schema only
mysqldump -u root -p --no-data --routines --triggers your_database > mysql_schema.sql

# Key manual conversions needed:
# 1. Remove backtick quoting (`) → use double quotes or nothing
# 2. Change ENGINE=InnoDB → remove entirely
# 3. Convert AUTO_INCREMENT → SERIAL or IDENTITY
# 4. Change UNSIGNED → add CHECK (column >= 0)
# 5. Convert ENUM types → CREATE TYPE ... AS ENUM
# 6. Change IF NOT EXISTS syntax
# 7. Remove MySQL-specific index hints

Data Migration

Using COPY for Large Tables

# Export from MySQL as CSV
mysql -u root -p your_database -e "SELECT * FROM large_table" \
    --batch --raw > /tmp/large_table.tsv

# Import into PostgreSQL
psql -U pguser -d pgdb -c "\COPY large_table FROM '/tmp/large_table.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true, NULL 'NULL')"

SQL Syntax Differences

Common SQL patterns that need updating in your application code:

-- String concatenation
MySQL:    CONCAT(first, ' ', last)
PG:       first || ' ' || last

-- LIMIT with offset
MySQL:    LIMIT 10, 20  (offset, count)
PG:       LIMIT 20 OFFSET 10

-- Date functions
MySQL:    NOW(), CURDATE(), DATE_FORMAT(d, '%Y-%m-%d')
PG:       NOW(), CURRENT_DATE, TO_CHAR(d, 'YYYY-MM-DD')

-- UPSERT
MySQL:    INSERT ... ON DUPLICATE KEY UPDATE
PG:       INSERT ... ON CONFLICT (col) DO UPDATE SET

-- Group by
MySQL:    Allows non-aggregated columns (with sql_mode lax)
PG:       Strictly requires all non-aggregated columns in GROUP BY

-- Boolean
MySQL:    WHERE active = 1
PG:       WHERE active = true (or active IS TRUE)

-- Case sensitivity
MySQL:    Case-insensitive by default (utf8_general_ci)
PG:       Case-sensitive; use ILIKE or citext extension

Application Code Changes

PHP PDO Migration

// MySQL
$pdo = new PDO('mysql:host=localhost;dbname=mydb', $user, $pass);

// PostgreSQL
$pdo = new PDO('pgsql:host=localhost;dbname=pgdb', $user, $pass);

// Update prepared statements that use backticks
// MySQL: SELECT `order`.`id` FROM `order`
// PG:    SELECT "order"."id" FROM "order"

Post-Migration Validation

# Compare row counts
mysql -e "SELECT COUNT(*) FROM users" mydb
psql -d pgdb -c "SELECT COUNT(*) FROM users"

# Compare checksums on critical columns
mysql -e "SELECT SUM(CRC32(CONCAT(id, email, balance))) FROM users" mydb

# PostgreSQL equivalent using md5
psql -d pgdb -c "SELECT md5(string_agg(id::text || email || balance::text, '')) FROM (SELECT * FROM users ORDER BY id) t"

Performance Tuning Post-Migration

-- Run ANALYZE on all tables after migration
ANALYZE;

-- Check for missing indexes based on sequential scans
SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC;

-- Tune postgresql.conf for your workload
-- shared_buffers = 25% of RAM
-- effective_cache_size = 75% of RAM
-- work_mem = RAM / max_connections / 4

Migration Checklist

• Map all MySQL data types to PostgreSQL equivalents
• Convert stored procedures and functions to PL/pgSQL
• Update all application SQL queries for PostgreSQL syntax
• Migrate triggers and adjust syntax
• Verify foreign key constraints are intact
• Run ANALYZE after data import
• Test application thoroughly with PostgreSQL backend
• Set up monitoring for the new PostgreSQL instance
• Plan rollback procedure in case of issues