Database Migration: MySQL and PostgreSQL Step by Step

Why Database Migration Is the Hardest Part

The database is the heart of any application. Code can be copied in seconds, but data requires careful handling. A single missing record or corrupted table means a real disaster. Migrating a 50GB database is a fundamentally different operation from migrating a 500GB one, and each database engine has its own tools and procedures.

This guide covers migration for the most common database engines: MySQL/MariaDB, PostgreSQL, and MongoDB, with practical steps you can execute immediately.

Pre-Migration Preparation

1. Document Your Current Environment

Before anything else, document every detail of your current setup:

• Engine type and version (e.g., MySQL 8.0.35)
• Total database size and per-table sizes
• Number of tables, views, and stored procedures
• Custom engine configurations (my.cnf or postgresql.conf)
• Users and their permissions
• Replication topology if any

2. Baseline Performance Metrics

Record performance metrics before migration so you can compare afterward. This proves the migration succeeded or reveals any regressions:

Metric	How to Measure
Query response time	EXPLAIN ANALYZE on key queries
Concurrent connections	SHOW PROCESSLIST or pg_stat_activity
Buffer pool usage	SHOW STATUS LIKE 'Innodb_buffer%'
Read/write throughput	iostat or server monitoring tools

MySQL / MariaDB Migration

Method 1: mysqldump (Databases Under 10GB)

The simplest and most common approach, suitable for small to medium databases:

1. Create a full dump: mysqldump --single-transaction --routines --triggers --all-databases
2. Transfer the dump file to the new server via scp or rsync
3. Import the dump: mysql < dump.sql
4. Verify row counts in every table

Method 2: Percona XtraBackup (Databases Over 10GB)

For large databases, mysqldump is too slow. Percona XtraBackup copies data physically without locking tables:

1. Take a physical backup: xtrabackup --backup --target-dir=/backup
2. Prepare the backup: xtrabackup --prepare --target-dir=/backup
3. Transfer to the new server and place in the data directory
4. Start MySQL and verify data integrity

Method 3: Replication (For Zero Downtime)

Enable replication between the old server (Master) and the new one (Slave), then promote the new server to Master during the switchover. This method guarantees zero data loss when executed correctly.

PostgreSQL Migration

Method 1: pg_dump / pg_restore

Similar to mysqldump but with more powerful features:

• Use custom format: pg_dump -Fc database_name > backup.dump
• Parallel restore: pg_restore -j 4 -d database_name backup.dump
• The -j flag enables multiple parallel workers to speed up restoration significantly

Method 2: pg_basebackup + Streaming Replication

For large databases or zero downtime migration. Creates a full replica, then promotes it:

1. Create a base backup: pg_basebackup -h old_server -D /var/lib/postgresql/data
2. Enable Streaming Replication
3. At switchover time: pg_ctl promote on the new server

MongoDB Migration

MongoDB provides built-in tools:

• mongodump / mongorestore: For small to medium databases
• Replica Set: Add the new server as a member, then promote it to Primary
• mongosync: A modern tool for continuous synchronization between two clusters

Post-Migration Verification

1. Compare row counts in every table between old and new databases
2. Run key queries and compare results
3. Verify indexes were transferred completely
4. Test stored procedures and triggers
5. Compare performance metrics against your baseline measurements
6. Confirm user permissions are correct

Post-Migration Performance Tuning

Migration is an opportunity to improve database performance. After confirming data integrity, apply these optimizations:

InnoDB Tuning (MySQL/MariaDB)

• innodb_buffer_pool_size: Set to 70-80% of available RAM. This is the single most important performance setting
• innodb_log_file_size: 256MB or 512MB for active databases — reduces disk write operations
• innodb_flush_log_at_trx_commit: Value 1 for full safety, 2 for better performance with minimal risk
• OPTIMIZE TABLE: Run on large tables after migration to rebuild indexes

PostgreSQL Tuning

• shared_buffers: 25% of available RAM
• effective_cache_size: 50-75% of available RAM
• work_mem: 32-64MB depending on concurrent connection count
• VACUUM ANALYZE: Run after migration to update planner statistics

Estimating Migration Time by Database Size

Database Size	mysqldump/pg_dump	XtraBackup/pg_basebackup	Replication
Under 1GB	5-15 minutes	2-5 minutes	Not needed
1-10GB	15-60 minutes	5-15 minutes	Optional
10-50GB	1-4 hours	15-45 minutes	Recommended
50-200GB	4-12 hours	1-3 hours	Required
Over 200GB	Impractical	3-8 hours	Mandatory

These estimates depend on disk and network speed. SSD storage accelerates the process by 3-5x compared to traditional HDD. Servers in the same data center transfer data much faster than geographically distant servers.

Database Migration on CLOUD

CLOUD (cloud.net.sa) servers are equipped with high-performance SSD storage, making import operations and query execution noticeably faster. The CLOUD team provides free technical guidance sessions for clients' developers to help them choose the optimal migration strategy based on database size and application requirements.

The golden rule: never migrate without a verified backup and a clear rollback plan. Your database is the most valuable asset you own, and it deserves to be treated with extreme care.

--- ### Get Started Want to try it yourself? Choose the right hosting plan or build your custom server with the specs you need.