How to migrate a database between servers

Whether you are moving to a bigger plan or changing servers, moving a database is three steps: dump on the old server, transfer, restore on the new server.

MySQL

# on the old server
mysqldump -u root -p mydb | gzip > mydb.sql.gz
# transfer to the new server
scp mydb.sql.gz root@NEW_IP:/tmp/
# on the new server
gunzip < /tmp/mydb.sql.gz | mysql -u root -p mydb

PostgreSQL

# on the old server
pg_dump -U postgres mydb | gzip > mydb.sql.gz
scp mydb.sql.gz root@NEW_IP:/tmp/
# on the new server
gunzip < /tmp/mydb.sql.gz | psql -U postgres mydb

How to reduce downtime

1. Do a first dump and restore in advance, so the new server has most of the data.
2. At cutover, stop writes on the old server, take a final dump and restore it.
3. Switch the application to the new database address.

Tip

With hourly billing, you can run both servers in parallel during the migration and delete the old one as soon as you are sure everything works. Keep the dump on a Backup Box as a safety net.