MySQL vs PostgreSQL Backup and Recovery Performance: A Practical Guide

Scenario

A SaaS company needs to migrate 500GB core databases from on-premises to the cloud. Backup window is 2 hours. Currently MySQL 8.0 (InnoDB) backup via mysqldump takes >3h, and recovery is unpredictable. PostgreSQL 15 backup with pg_dump takes 2.8h, recovery exceeding 4h. Both overrun the SLA.

Symptoms

• MySQL: mysqldump backup 3.5h, recovery 4h; XtraBackup backup 1.5h, but recovery spikes disk I/O causing application latency.
• PostgreSQL: pg_dump backup 2.8h, recovery slow due to WAL replay; pg_basebackup backup 1.2h, but recovery requires manual WAL application.

Diagnosis

MySQL

1. Backup bottleneck: Check if mysqldump uses --single-transaction (avoids table locks) and --quick (row-by-row). Use perf top to identify CPU spent on compression (default gzip).
2. Recovery bottleneck: High I/O wait due to single-threaded mysql < dump.sql and non-striped disks.

PostgreSQL

1. Backup: pg_dump runs serially by default; use -j for parallelism. Check shared_buffers and work_mem.
2. Recovery: pg_restore also serial; specify -j. Improper WAL archiving causes excessive WAL replay.

Commands

MySQL Fast Backup with XtraBackup

xtrabackup --backup --parallel=4 --compress --compress-threads=4 --target-dir=/backup/mysql/full

MySQL Restore

xtrabackup --prepare --target-dir=/backup/mysql/full
xtrabackup --copy-back --target-dir=/backup/mysql/full

PostgreSQL Parallel Backup

pg_dump -j 4 -Fd -f /backup/pg/dump mydb

PostgreSQL Parallel Restore

pg_restore -j 4 -d mydb /backup/pg/dump

Risk Controls

• Verify free disk space before backup to avoid filling the volume.
• Use --single-transaction or --lock-wait-timeout on production to prevent long waits.
• Never restore directly to production; always test on a staging instance.
• Checksum backup files and perform periodic restore drills.

Rollback

• On backup failure, immediately revert to the last successful backup.
• MySQL: Use XtraBackup incremental backups (--incremental-basedir) for rollback.
• PostgreSQL: Use PITR to restore to a specific point in time.

Verification

1. Run table-level checksums: MySQL: CHECKSUM TABLE table_name; PostgreSQL: Use pg_surgery (carefully) or custom hash-based verification.
2. Execute key queries comparing row counts and business summaries.
3. Monitor application logs for duplicate key errors or missing data.

When to Submit an OpsGlobal Ticket

• Backup duration consistently exceeds the window by >50%.
• Restore fails or data inconsistency cannot be resolved internally.
• Need to tune backup strategy (compression algorithm, parallelism, etc.).
• Disk or network I/O bottlenecks affecting other workloads and requiring infrastructure support.

This guide provides technical reference only. Always follow your change management process. For expert assistance, contact OpsGlobal.