מאגר מידע

Whether you're upgrading to a bigger VPS or switching providers, migrating a database safely requires exporting, transferring, and verifying data integrity before cutting over. This guide covers all three major database engines.

General Migration Plan

1. Export (dump) the database on the source server
2. Transfer the dump file securely to the destination
3. Import it on the new server
4. Verify data integrity before pointing your application at the new database
5. Schedule a brief maintenance window for the final cutover to avoid data loss from writes during the transfer

MySQL / MariaDB Migration

On the source server:

mysqldump -u root -p --all-databases | gzip > full-backup.sql.gz

Transfer it:

scp full-backup.sql.gz user@NEW_SERVER_IP:/tmp/

On the destination server (after installing MySQL/MariaDB):

gunzip < /tmp/full-backup.sql.gz | mysql -u root -p

PostgreSQL Migration

On the source server:

sudo -u postgres pg_dumpall | gzip > full-backup.sql.gz

scp full-backup.sql.gz user@NEW_SERVER_IP:/tmp/

On the destination server:

gunzip < /tmp/full-backup.sql.gz | sudo -u postgres psql

MongoDB Migration

On the source server:

mongodump --out /tmp/mongo-backup

tar czf mongo-backup.tar.gz -C /tmp mongo-backup
scp mongo-backup.tar.gz user@NEW_SERVER_IP:/tmp/

On the destination server:

tar xzf /tmp/mongo-backup.tar.gz -C /tmp
mongorestore /tmp/mongo-backup

Minimizing Downtime: The Two-Pass Approach

For databases too large to comfortably re-transfer during a short maintenance window:

1. Do an initial full export/import while the source stays live
2. Schedule a short maintenance window
3. Do a second, much faster export/import capturing only the changes since the first pass (or briefly pause writes on the source)
4. Switch the application's connection string to the new server

Verifying the Migration

Compare row/document counts and spot-check key tables/collections:

-- MySQL/PostgreSQL
SELECT COUNT(*) FROM orders;

// MongoDB
db.orders.countDocuments()

Updating Your Application

Update the database connection string/host in your application's configuration, then restart the app and thoroughly test before decommissioning the old server.

Common Errors

Character set/collation mismatch (MySQL) — specify matching character sets explicitly during dump/import if source and destination server defaults differ.

Missing roles/permissions after PostgreSQL restore — use pg_dumpall --globals-only separately to capture roles if not already included.

Data appears missing after MongoDB restore — confirm mongodump completed fully (check for errors) and that the correct database names are being restored.

Best Practices

• Keep the old server running and untouched until the new one is fully verified
• Test the application thoroughly against the new database before final DNS/connection string cutover
• Schedule migrations during low-traffic periods
• Take a fresh backup of the source immediately before starting, as a safety net

Continue Reading

• How to Back Up and Restore MySQL/MariaDB Databases
• How to Back Up and Restore PostgreSQL Databases
• How to Transfer Files To and From a VPS (SCP, SFTP & rsync)

Browse more articles in Databases.