How I Migrated Our MySQL Database to PostgreSQL (And Didn't Quit My Job)

Why I Keep Doing This Migration

Look, MySQL pissed me off one too many times. After 4 years of babysitting production databases, PostgreSQL just works better. PostgreSQL 16 dropped in September 2023 and fixed the JSON indexing shit that kept breaking our product search. The official PostgreSQL 16 announcement covers all the performance improvements, while this migration guide from 2025 provides current best practices.

Here's why I finally said "fuck this" and migrated our e-commerce platform's 47GB inventory database last year (maybe 52GB? I stopped counting after the third disk expansion):

MySQL kept breaking on us:

• JSON queries were garbage - MySQL's JSON implementation couldn't handle our product catalog searches efficiently (PostgreSQL JSONB performance analysis)
• Complex analytics queries timed out - Our reporting dashboard took 45 seconds to load inventory reports (PostgreSQL query optimization guide)
• Foreign key constraints were optional - I spent 2 days debugging phantom data because MySQL silently ignored a broken relationship (MySQL foreign key reliability issues)
• Replication kept shitting the bed - MySQL's master-slave replication failed twice during Black Friday prep (MySQL vs PostgreSQL reliability comparison)

Hardware Reality Check - Don't Be Like Me

Anyway, this bit me in the ass hard and I don't want you to go through the same pain:

I learned this the hard way when pgloader crashed at 89% complete (pgloader heap exhaustion issue):

• 16GB RAM minimum - I tried with 8GB and pgloader ate all available memory and crashed after 6 hours (pgloader memory requirements)
• SSD storage is non-negotiable - My first migration took 18 hours on spinning rust, same database took 4 hours on SSD (PostgreSQL storage considerations)
• Fast network connection - Migration over VPN to AWS took 14 hours, direct connection took 3 hours (migration best practices)
• 3x storage space not 2x - PostgreSQL uses more space than MySQL for the same data, plus you need temp space for indexes. I think our 47GB MySQL database ended up needing like 140GB? Maybe 150GB? I honestly stopped counting when the disk usage alarm went off at 3am (PostgreSQL vs MySQL storage differences)

Tools that actually work:

• PostgreSQL 17.5 - current stable build as of September 2025, been rock solid in production for us (PostgreSQL 17.5 release notes)
• pgloader 3.6.9 - Latest stable version, avoid 3.6.7 which had bugs with large TEXT columns (pgloader GitHub releases)
• MySQL 8.0+ or 8.4 LTS (if you're still on 5.7, it's EOL as of October 2023 - upgrade first)
• DBeaver because pgAdmin's interface makes me want to throw my laptop out the window

Pre-Migration Assessment (The Stuff That Will Bite You Later)

Run these queries to find the landmines in your schema (replace inventory_db with your actual database):

-- Find the data types that will cause pgloader to have a mental breakdown
SELECT DISTINCT DATA_TYPE 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'inventory_db' 
AND DATA_TYPE IN ('ENUM', 'SET', 'YEAR', 'TINYINT');

-- Identify your biggest tables (these take forever to migrate)
SELECT TABLE_NAME, 
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as SIZE_MB
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'inventory_db' 
ORDER BY SIZE_MB DESC;

-- Find stored procedures that you'll need to rewrite by hand
SELECT ROUTINE_NAME, ROUTINE_TYPE 
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'inventory_db';

The 4 things that will definitely fuck you up:

• ENUM columns become CHECK constraints - Our product status went from ENUM('active','discontinued') to a CHECK constraint that half our app didn't expect
• AUTO_INCREMENT sequences start weird - PostgreSQL sequences don't start at your current max ID unless you tell them to
• Zero dates break everything - MySQL's 0000-00-00 dates will cause pgloader to error out. I had 1,247 records with invalid dates in our orders table
• Case sensitivity will murder your app - PostgreSQL converts ProductTable to producttable, your ORM queries will fail with "relation does not exist"

Realistic Timeline Planning (Not The Bullshit Estimates You See Elsewhere)

OK, real talk time. Every migration guide lies about timelines:

How long this actually takes (based on my last 5 migrations):

• Under 1GB: 1-2 hours (including debugging weird schema issues)
• 1-10GB: 2-5 hours (our 8GB customer database took 4.5 hours)
• 10-50GB: 4-8 hours (the 47GB inventory database took 7 hours including index rebuilds, or maybe 9 hours? I lost track after the UTF-8 issue)
• 50-100GB: 8-16 hours (and that's if nothing breaks)

Downtime reality check:

• Schedule for a weekend because shit will go wrong
• Plan for 2x your estimated time - my "4 hour migration" took 9 hours when pgloader choked on our UTF8 data
• Have pizza and coffee ready - you'll be debugging until 3am. I spent 6 hours tracking down why our user login system broke, turned out PostgreSQL was case-sensitive on the username column and half our users had mixed-case usernames in MySQL
• Keep MySQL running until you're 100% sure PostgreSQL works - I rolled back twice before getting it right

The migration itself is the easy part. It's the 47 SQL queries in your application that assume MySQL's quirks that will ruin your week.

Phase 1: Installing PostgreSQL Without Breaking Your System

Alright, let's get this shit started:

Installing PostgreSQL (Ubuntu 22.04 - Other Distros Are Similar)

I now use PostgreSQL 17.5 in production since it's rock solid (official PostgreSQL installation guide):

## Add the official PostgreSQL repo (don't use Ubuntu's ancient version)
sudo sh -c 'echo \"deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main\" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
sudo apt update
sudo apt install postgresql-17 postgresql-contrib-17

For comprehensive installation instructions, see the DigitalOcean PostgreSQL Ubuntu 22.04 guide and Ubuntu official documentation.

macOS (if you're into that sort of thing):

## Homebrew works fine but I prefer Linux for databases
brew install postgresql@17
brew services start postgresql@17

Windows:
Just use Docker. Seriously. Download Docker Desktop and run PostgreSQL in a container instead of dealing with Windows service bullshit. Check out Docker's PostgreSQL best practices and production Docker PostgreSQL setup guide.

Setting Up Your Target Database

Create the database that will hold your migrated data:

## Connect as the postgres superuser
sudo -u postgres psql

## Create your actual database (I'm calling it inventory_prod)
CREATE DATABASE inventory_prod;
CREATE USER app_user WITH PASSWORD 'Tr7$mK9*wE2!nB8q';
GRANT ALL PRIVILEGES ON DATABASE inventory_prod TO app_user;

## Grant schema permissions
\c migrated_db
GRANT ALL ON SCHEMA public TO migration_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO migration_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO migration_user;

## Exit PostgreSQL
\q

Installing pgloader

Ubuntu/Debian:

sudo apt update
sudo apt install pgloader

macOS:

brew install pgloader

Using Docker (recommended for Windows):

docker pull dimitri/pgloader:latest

Phase 2: Pre-Migration Database Backup

Create MySQL Backup

Always backup before migration - this is your safety net (mysqldump best practices):

## Create a complete backup of your MySQL database
mysqldump -u username -p --single-transaction --routines --triggers database_name > mysql_backup_$(date +%Y%m%d_%H%M%S).sql

## For large databases, use compression
mysqldump -u username -p --single-transaction --routines --triggers database_name | gzip > mysql_backup_$(date +%Y%m%d_%H%M%S).sql.gz

For large databases (>50GB), consider alternative backup strategies like Percona XtraBackup for faster performance.

Verify your backup:

## Check backup file size (should not be 0)
ls -lh mysql_backup_*.sql

## Test restore to a temporary database (optional but recommended)
mysql -u username -p -e \"CREATE DATABASE backup_test;\"
mysql -u username -p backup_test < mysql_backup_*.sql
mysql -u username -p -e \"DROP DATABASE backup_test;\"

Phase 3: Execute Migration with pgloader

Basic Migration Command

For simple databases:

pgloader mysql://mysql_user:mysql_password@localhost/source_db \
         postgresql://migration_user:secure_password123@localhost/migrated_db

Advanced Migration with Configuration File

Create a file named migration.load:

LOAD DATABASE
     FROM mysql://mysql_user:mysql_password@mysql_host:3306/source_db
     INTO postgresql://migration_user:secure_password123@postgres_host:5432/migrated_db

WITH include drop, create tables, create indexes, reset sequences,
     workers = 4, concurrency = 1,
     multiple readers per thread, rows per range = 25000

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

-- Handle common data type conversions
CAST type datetime to timestamp drop default drop not null using zero-dates-to-null,
     type date drop not null using zero-dates-to-null,
     type tinyint(1) to boolean drop typemod,
     type enum when (= 'active') to boolean drop typemod

-- Skip problematic tables if needed  
INCLUDING ONLY TABLE NAMES MATCHING ~/^(?!temp_|log_|cache_)/

BEFORE LOAD DO
 $$ drop schema if exists mysql cascade; $$,
 $$ create schema mysql; $$;

Execute the migration (grab coffee, this takes forever):

pgloader migration.load

Pro tip: Run this in a screen session because your SSH connection WILL drop at 89% complete. Trust me - I learned this shit on Christmas Eve when my laptop decided to take a nap and nuked 8 hours of migration progress. Yeah, I was migrating databases on Christmas fucking Eve. My wife still roasts me about this during family dinners. "Remember when you chose PostgreSQL over presents?"

Monitor Migration Progress

During migration, pgloader provides real-time feedback:

• Table creation progress
• Row insertion counts
• Error reporting
• Performance statistics

Example output:

     table name     errors       rows      bytes      total time
--------------     ------     ------     ------      ----------
         fetch          0         10       2.4 kB          0.086s
        create          0         11       1.6 kB          0.048s
         users          0       5420     1.2 MB          2.234s
        orders          0      12450     3.8 MB          4.567s
      products          0       1250   456.7 kB          0.892s
--------------     ------     ------     ------      ----------
        TOTAL          0      19141     5.4 MB          7.827s

Phase 4: Post-Migration Verification

Data Integrity Checks

Compare row counts between databases:

-- MySQL
SELECT 
    TABLE_NAME,
    TABLE_ROWS as ROW_COUNT
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'source_db'
ORDER BY TABLE_NAME;

-- PostgreSQL  
SELECT 
    schemaname,
    tablename,
    n_tup_ins as row_count
FROM pg_stat_user_tables
ORDER BY tablename;

Verify data types were converted correctly:

-- PostgreSQL - Check converted data types
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_catalog = 'migrated_db' 
AND table_schema = 'public'
ORDER BY table_name, ordinal_position;

Application Connection Update

Update your application's database configuration:

PHP (Laravel example):

// config/database.php
'pgsql' => [
    'driver' => 'pgsql',
    'host' => 'localhost',
    'port' => '5432', 
    'database' => 'migrated_db',
    'username' => 'migration_user',
    'password' => 'secure_password123',
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
],

Python (Django example):

## settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'migrated_db',
        'USER': 'migration_user', 
        'PASSWORD': 'secure_password123',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

Node.js (using pg library):

const { Pool } = require('pg')

const pool = new Pool({
  user: 'migration_user',
  host: 'localhost',
  database: 'migrated_db', 
  password: 'secure_password123',
  port: 5432,
})

With these steps completed, your MySQL database should be successfully migrated to PostgreSQL. The next phase involves testing your applications and optimizing performance for your new PostgreSQL setup.

Performance Tuning Your New PostgreSQL Database

So you got the data moved over? Cool. Now comes the part where PostgreSQL runs like ass until you tune it:

Essential PostgreSQL Configuration

After migration, PostgreSQL runs like garbage until you tune it. Default settings are made for a fucking Raspberry Pi. I nearly lost my shit when our main dashboard went from 2 seconds in MySQL to 45 seconds in PostgreSQL - I was ready to nuke the whole thing until I fixed the config. For comprehensive configuration tuning, check PostgreSQL Performance Tuning Guide and use PGTune for automated configuration calculations based on your hardware.

Update postgresql.conf with recommended settings:

## Memory settings (adjust based on your system)
shared_buffers = '256MB'              # 25% of available RAM  
work_mem = '64MB'                     # Memory per query operation
maintenance_work_mem = '512MB'        # For VACUUM, CREATE INDEX, etc.
effective_cache_size = '1GB'          # Available system cache

## Checkpoint settings for better write performance
checkpoint_completion_target = 0.9     # Spread checkpoints over time
wal_buffers = '16MB'                  # Write-ahead log buffer size
max_wal_size = '2GB'                  # Maximum WAL size

## Connection settings
max_connections = 100                 # Concurrent connections limit

## Query planner settings
random_page_cost = 1.1                # Cost of random disk access (SSD)
effective_io_concurrency = 200        # Concurrent I/O operations (SSD)

Apply configuration changes:

sudo systemctl reload postgresql
## or
sudo pg_ctl reload

Rebuild Statistics for Optimal Query Performance

PostgreSQL's query planner relies on table statistics to choose efficient execution plans. After migration, update these statistics (PostgreSQL ANALYZE documentation, performance tuning with EXPLAIN ANALYZE):

-- Connect to your migrated database
psql -U migration_user -d migrated_db

-- Update all table statistics (can take several minutes for large databases)
ANALYZE VERBOSE;

-- For better statistics on large tables, increase sample size
ALTER TABLE large_table_name ALTER COLUMN column_name SET STATISTICS 1000;
ANALYZE large_table_name;

-- Check if statistics are current
SELECT schemaname, tablename, last_analyze, last_autoanalyze 
FROM pg_stat_user_tables 
WHERE schemaname = 'public'
ORDER BY last_analyze DESC;

Index Optimization

pgloader recreates indexes, but you may need additional optimizations:

Identify missing indexes:

-- Find tables without indexes on foreign key columns
SELECT 
    t.table_name,
    c.column_name,
    c.data_type
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k ON t.constraint_name = k.constraint_name
JOIN information_schema.columns c ON k.table_name = c.table_name AND k.column_name = c.column_name
WHERE t.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
    SELECT 1 FROM pg_indexes 
    WHERE tablename = t.table_name 
    AND indexdef LIKE '%' || c.column_name || '%'
);

Create commonly needed indexes:

-- Index on foreign key columns (if not already present)
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_order_items_product_id ON order_items(product_id);

-- Index on frequently queried date columns
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);
CREATE INDEX CONCURRENTLY idx_users_last_login ON users(last_login);

-- Composite indexes for common query patterns
CREATE INDEX CONCURRENTLY idx_orders_status_date ON orders(status, created_at);

Use CONCURRENTLY to avoid locking tables during index creation.

Application Compatibility and Testing

SQL Syntax Differences to Address

Common MySQL to PostgreSQL syntax updates needed in applications:

1. String concatenation:

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

-- PostgreSQL  
SELECT first_name || ' ' || last_name as full_name FROM users;

2. LIMIT with OFFSET:

-- MySQL
SELECT * FROM users LIMIT 10, 20;  -- OFFSET, LIMIT

-- PostgreSQL
SELECT * FROM users LIMIT 20 OFFSET 10;  -- LIMIT, OFFSET

3. Date functions:

-- MySQL
SELECT NOW(), DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;

-- PostgreSQL
SELECT CURRENT_TIMESTAMP, TO_CHAR(created_at, 'YYYY-MM-DD') FROM orders;

4. Case sensitivity:

-- MySQL (case insensitive by default)
SELECT * FROM Users WHERE Name = 'john';

-- PostgreSQL (case sensitive)
SELECT * FROM users WHERE name = 'john';  -- Use lowercase
-- or use ILIKE for case-insensitive matching
SELECT * FROM users WHERE name ILIKE 'john';

Testing Application Functionality

Create a systematic testing approach:

1. Database connection test:

## Python example
import psycopg2

try:
    conn = psycopg2.connect(
        host="localhost",
        database="migrated_db", 
        user="migration_user",
        password="secure_password123"
    )
    print("PostgreSQL connection successful!")
    conn.close()
except Exception as error:
    print(f"Connection failed: {error}")

2. Query performance comparison:

-- Enable query timing
	iming on

-- Test your most important queries
EXPLAIN ANALYZE SELECT * FROM users 
WHERE email = 'user@example.com';

-- Compare execution times with MySQL baseline

3. Data integrity verification:

-- Verify foreign key constraints work
INSERT INTO orders (user_id, total) VALUES (99999, 100.00);  
-- Should fail with foreign key error

-- Test unique constraints
INSERT INTO users (email) VALUES ('existing@email.com');
-- Should fail if email already exists

Backup and Maintenance Schedule

Automated Backup Strategy

Set up regular PostgreSQL backups (pg_dump best practices, PostgreSQL backup strategies):

#!/bin/bash
## daily_backup.sh - Place in /etc/cron.daily/

BACKUP_DIR="/backups/postgresql"
DB_NAME="migrated_db"
DB_USER="migration_user"
DATE=$(date +%Y%m%d_%H%M%S)

## Create backup directory
mkdir -p $BACKUP_DIR

## Create database backup
pg_dump -U $DB_USER -h localhost $DB_NAME | gzip > $BACKUP_DIR/backup_${DB_NAME}_${DATE}.sql.gz

## Keep only last 7 days of backups
find $BACKUP_DIR -name "backup_${DB_NAME}_*.sql.gz" -mtime +7 -delete

## Log backup completion
echo "$(date): Backup completed for $DB_NAME" >> /var/log/postgresql_backup.log

Make script executable and test:

sudo chmod +x /etc/cron.daily/daily_backup.sh
sudo /etc/cron.daily/daily_backup.sh  # Test manually

Regular Maintenance Tasks

Weekly maintenance script:

-- maintenance.sql - Run weekly via cron
-- Update table statistics
ANALYZE;

-- Clean up deleted rows and update indexes  
VACUUM ANALYZE;

-- For tables with frequent updates/deletes, use FULL vacuum monthly
-- VACUUM FULL large_table_name;  -- This locks the table

-- Check for bloated indexes
SELECT schemaname, tablename, indexname, 
       pg_size_pretty(pg_relation_size(indexname::regclass)) as index_size
FROM pg_indexes 
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC;

Execute via cron weekly:

## Add to root's crontab (sudo crontab -e)
0 2 * * 0 psql -U migration_user -d migrated_db -f /path/to/maintenance.sql

Monitoring and Alerting

Set up basic monitoring for your PostgreSQL instance:

1. Disk space monitoring:

-- Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) 
FROM pg_database 
WHERE datistemplate = false;

-- Check table sizes
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

2. Connection monitoring:

-- Monitor active connections
SELECT count(*) as active_connections,
       state,
       wait_event_type
FROM pg_stat_activity 
WHERE state IS NOT NULL
GROUP BY state, wait_event_type;

-- Check for long-running queries (over 5 minutes)
SELECT pid, now() - query_start as duration, query 
FROM pg_stat_activity 
WHERE state = 'active' 
AND now() - query_start > interval '5 minutes';

Your PostgreSQL database is now optimized and ready for production use. Regular monitoring and maintenance will keep it performing well as your data grows.

One last thing: Put a damn reminder in your calendar for 3 months from now to review this config. What works for 10GB turns to shit at 100GB. I've watched teams celebrate their "successful" migration, then come crying 6 months later because their queries crawled to a death. Don't be me circa 2022 when I ignored this and spent 4 hours at 2am figuring out why our entire app ground to a halt.