Zero-Downtime Database Migration: A Survival Guide for Moving Data Home to Norway

There is no silence quite as loud as the one in a Slack channel at 3:00 AM when a database migration fails. I’ve been there. The "maintenance window" is closing, the restore is only at 60%, and the CEO is asking why the checkout page is throwing 500 errors.

In 2022, moving a database is rarely just about technical debt. For many of us operating in the Nordics, it's about compliance. Since the Schrems II ruling, I've seen a massive exodus of data from US-owned cloud giants back to European soil. But moving 500GB+ of transactional data from AWS or Google Cloud to a sovereign VPS in Norway without killing your SLA is surgical work.

If you think mysqldump is a migration strategy for production workloads, stop reading. This guide is for the rest of us who can't afford more than 60 seconds of downtime.

The Latency Trap: Why Location Matters

Before we touch a config file, let's talk about physics. If you are replicating data from Frankfurt to Oslo, you are dealing with roughly 15-20ms of round-trip time (RTT). That sounds fast until you are pushing millions of binary log events.

High latency increases the replication lag. If your write throughput on the source exceeds the network's ability to acknowledge packets, your slave will never catch up. This is where the underlying infrastructure of your target matters.

We use CoolVDS for these targets because they sit directly on the NIX (Norwegian Internet Exchange) backbone. When every millisecond of lag delays your cutover, you don't want your packets bouncing through three different hops in Stockholm first.

Strategy: The Hot-Standby Switchover

Forget offline migrations. The only professional way to move a live database is to set up the target server as a replica (slave) of the live production server (master), let it sync, and then promote it.

Step 1: The Initial Seed (Without Locking)

For MySQL/MariaDB, mysqldump locks tables. That causes downtime. Instead, we use Percona XtraBackup. It copies InnoDB data files physically while the server runs.

Here is how to stream a backup directly from your Source (e.g., old cloud) to your Target (CoolVDS NVMe) over SSH, compressing it on the fly to save bandwidth:

# On the Source Server
xtrabackup --backup --stream=xbstream --parallel=4 \
  | ssh user@target-coolvds-ip "xbstream -x -C /var/lib/mysql/backup/"

This command uses 4 parallel threads. If your target disk is a spinning HDD, this will choke. This is why I insist on NVMe storage for the target. During the restore phase, IOPS are the only metric that counts.

Step 2: Preparing the Target

Once the data is transferred, you need to prepare the backup (apply the transaction logs) before MySQL can use it.

# On the Target Server (CoolVDS)
xtrabackup --prepare --target-dir=/var/lib/mysql/backup/

After this, ensure your permissions are correct and move the data into place:

chown -R mysql:mysql /var/lib/mysql/backup/
mv /var/lib/mysql/backup/* /var/lib/mysql/
systemctl start mysql

Step 3: Configuring Replication

Check the xtrabackup_binlog_info file created on the target. It contains the exact binary log file and position where the snapshot was taken.

cat /var/lib/mysql/xtrabackup_binlog_info
# Output: mysql-bin.000456  982374

Now, configure the CoolVDS instance to catch up with the Source:

CHANGE MASTER TO
  MASTER_HOST='source_db_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='secure_password',
  MASTER_LOG_FILE='mysql-bin.000456',
  MASTER_LOG_POS=982374;

START SLAVE;

Monitor the status with SHOW SLAVE STATUS \G. Watch Seconds_Behind_Master. It should drop to 0.

Pro Tip: If your replication is slow, it might be the TCP stack. On the target CoolVDS server, tune the window scaling to allow for "Long Fat Networks" (high bandwidth, moderate latency). Add this to /etc/sysctl.conf:

net.ipv4.tcp_window_scaling = 1
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

The PostgreSQL Approach (Logical Replication)

If you are running Postgres 10 or newer (and since it's 2022, you should be on PG 13 or 14), Logical Replication is superior to WAL shipping for major version upgrades or cross-platform migrations.

1. Enable Logical Replication on Source

In postgresql.conf:

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

Restart Postgres, then create a publication:

-- On Source DB
CREATE PUBLICATION my_migration_pub FOR ALL TABLES;

2. Subscribe from the Target

On your CoolVDS instance, first restore the schema (use pg_dump --schema-only), then create the subscription:

-- On Target DB
CREATE SUBSCRIPTION my_migration_sub 
CONNECTION 'host=source_ip port=5432 dbname=mydb user=repl_user password=secret' 
PUBLICATION my_migration_pub;

Postgres will perform an initial data copy and then automatically switch to streaming changes. It is incredibly robust.

The "CoolVDS Factor": Why Hardware Defines Success

I mentioned IOPS earlier. When you trigger that initial sync, your disk is writing as fast as the network allows. On shared hosting or budget VPS providers with "noisy neighbors," your write speeds fluctuate. This causes the sync to take hours longer than necessary, extending your risk window.

In our benchmarks, KVM-based virtualization (which we use exclusively) isolates resources far better than OpenVZ containers. When you migrate to a CoolVDS NVMe instance, the OS sees a raw disk interface, not a containerized loopback file. This matters when you are flushing dirty pages from the InnoDB buffer pool.

Furthermore, moving to a Norwegian datacenter satisfies Datatilsynet requirements regarding data sovereignty. You aren't just getting a faster database; you are getting a legally compliant one.

The Cutover (The Scary Part)

Once replication lag is zero, it's time.

1. Lower TTL: Set DNS TTL to 60 seconds well in advance.
2. Read-Only Mode: Set the source DB to read-only to prevent split-brain data. SET GLOBAL read_only = ON;
3. Wait: Ensure the target executes the final transactions.
4. Promote: Stop the slave on the target: STOP SLAVE; RESET SLAVE ALL;
5. Switch: Point your application config or update DNS to the new CoolVDS IP.

With this method, your total write-downtime is usually under 10 seconds.

Final Thoughts

Migrations expose the weak points in your infrastructure. If your target server has slow I/O, the catch-up phase will fail. If the network jitters, replication breaks. Don't fight the hardware.

If you need a target environment that respects your need for raw I/O throughput and keeps your data strictly within Norwegian borders, we have built the infrastructure you need.

Ready to test your replication speed? Spin up a high-performance NVMe instance on CoolVDS in under 60 seconds.