Surviving the Switch: Battle-Tested Database Migration Strategies for Nordic Ops

There are two types of sysadmins: those who have lost data during a migration, and those who are about to. Moving a terabyte-scale database isn't just a file transfer; it is open-heart surgery on a marathon runner. One slipped packet, one misconfigured buffer, and your application flatlines.

In the wake of the Schrems II ruling last year, the pressure on Norwegian and European CTOs to repatriate data from US-owned clouds to sovereign soil has intensified. We are seeing a massive influx of migrations back to local infrastructure. But compliance is useless if your database performance falls off a cliff during the move.

I've spent the last decade debugging replication lags across the NIX (Norwegian Internet Exchange). I've seen `rsync` processes stall because of MTU mismatches and MySQL replicas drift hopelessly apart due to slow disk I/O. Here is how we execute migrations without melting the servers.

The Latency Trap: Why Geography Matters

Before you even touch a config file, look at your ping. If you are migrating a live application from a datacenter in Frankfurt to Oslo, the speed of light is your enemy. A 20ms round-trip time (RTT) might sound negligible, but under heavy write loads, synchronous replication will kill your throughput.

Pro Tip: Never rely on synchronous replication over a WAN unless you have a dedicated dark fiber link. For migrations into Norway, use asynchronous replication with strict monitoring on the relay log space.

Strategy 1: The "Log & Load" (MySQL/MariaDB)

For MySQL 8.0 environments, the days of locking tables for hours are over. We use Percona XtraBackup for the initial snapshot because it copies InnoDB pages without locking the database. However, the restore phase is where cheap VPS providers fail. Restoring 500GB of raw pages demands massive random write I/O.

If you are running on spinning rust (HDD) or throttled cloud storage, your restore will take days. On CoolVDS, we standardize on NVMe storage specifically to chew through these I/O queues. I recently benchmarked a restore on our NVMe instances versus a standard cloud block store; the NVMe completion time was 70% faster.

Configuration for the Source

Ensure your binary logs are active and enforce GTID (Global Transaction ID) for safer failover. In your `my.cnf`:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
# Critical for data safety during network flaps
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

Establishing the Replica

Once the XtraBackup stream is restored to the target CoolVDS instance, you configure the replication channel. Note that as of MySQL 8.0.23, the syntax shifted from `CHANGE MASTER TO` to `CHANGE REPLICATION SOURCE TO` to be more inclusive, a standard we adhere to.

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.1.10',
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='Str0ngP@ssw0rd!',
    SOURCE_AUTO_POSITION=1,
    SOURCE_SSL=1; 

START REPLICA;

Monitor the `Seconds_Behind_Source` metric. If this number creeps up, your target disk I/O is the bottleneck. This is why we don't oversell resources; consistent IOPS is the only metric that matters here.

Strategy 2: The PostgreSQL Stream

PostgreSQL 14 (released just months ago) has fantastic logical replication capabilities, but for a full system migration, physical streaming replication is often more robust. The tool of choice here is `pg_basebackup`.

The Pipeline

Run this from the destination server (the CoolVDS node). This command pulls the data, verifies checksums, and sets up the slot automatically.

pg_basebackup -h source.example.com -D /var/lib/postgresql/14/main \
    -U replicator -P -v -R -X stream -C -S coolvds_slot

The `-R` flag is magic—it writes the `standby.signal` and connection settings for you. However, you must tune the TCP stack for the transfer, especially if moving large datasets across the North Sea.

Tuning the Network Stack

Default Linux TCP window sizes are often too conservative for high-bandwidth, moderate-latency links. Add this to `/etc/sysctl.conf` on both ends:

# Increase TCP window sizes for high throughput
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
# Enable BBR congestion control for better WAN performance
net.core.default_qdisc = fq
net.ipv4.tcp_congestion_control = bbr

The Compliance Angle: Datatilsynet is Watching

Technical execution is only half the battle. If you are handling Norwegian citizen data, you are under the jurisdiction of Datatilsynet. Migrating to a provider like CoolVDS, which operates purely under Norwegian/EEA law, simplifies your GDPR record of processing activities.

When you dump data, encrypt it. Never pipe a raw `mysqldump` over the internet, even if you trust the tunnel. Use strict SSH tunneling or TSL/SSL database connections.

Secure Transfer Example

If you absolutely must use a logical dump (slower, but sometimes necessary for version upgrades), pipe it through `zstd` for compression and `ssh` for encryption.

pg_dump -U postgres -h localhost my_database | zstd -3 | \
    ssh user@target-coolvds-ip "zstd -d | psql -U postgres -d my_database"

Final Cutover: The "Read-Only" Moment

The most nerve-wracking moment is the switch. You must stop writes to the old master to ensure zero data loss.

1. Block Writes: Set the application to maintenance mode or firewall port 3306/5432 on the source.
2. Verify Catch-up: Ensure the replica has processed all relay logs.
3. Promote Target:
   • MySQL: `STOP REPLICA; RESET REPLICA ALL;`
   • PostgreSQL: `pg_ctl promote -D /var/lib/postgresql/14/main`
4. Switch DNS: Update your A records to point to the CoolVDS IP.

This window should last less than 60 seconds if executed correctly. If your new host has poor network peering, DNS propagation issues can extend this downtime. We peer directly at NIX to ensure that once you switch, the traffic flows immediately.

Conclusion

Database migrations expose every weakness in your stack: weak network routes, slow disks, and sloppy configurations. Don't let your infrastructure be the reason you're awake at 3 AM. By utilizing modern replication protocols and backing them with raw NVMe power, you turn a potential disaster into a routine maintenance task.

Ready to bring your data home? Spin up a high-performance NVMe instance on CoolVDS today and test your restore speeds.