Surviving the Switch: Zero-Downtime Database Migration Strategies

I still wake up in a cold sweat thinking about a migration I botched back in 2018. We were moving a 2TB PostgreSQL cluster from a legacy dedicated box in Frankfurt to a cloud instance in Oslo. I underestimated the network jitter across the Skagerrak and the IOPS thottling on the target volume. The result? A 14-hour synchronization lag that forced us to abort at 3 AM. It was humiliating.

In June 2024, technology has evolved, but physics hasn't. Latency is still latency. If you are moving data into Norway to satisfy Datatilsynet requirements or simply to get closer to your users on the NIX (Norwegian Internet Exchange), you cannot rely on hope. You need a strategy that assumes failure is the default state.

The "Replicate and Promote" Pattern

Forget offline migrations. Unless your SLA allows for maintenance windows wide enough to drive a truck through, you must use live replication. The goal is to set up the new CoolVDS instance as a read-replica of your current production server, let it catch up, and then flip the switch.

1. PostgreSQL 16: Logical Replication

For Postgres, Logical Replication is the sharpest knife in the drawer. Unlike streaming replication, it allows you to migrate between major versions (e.g., PG 14 to 16) and different architectures. It's robust, but it eats CPU.

First, on your source server (Production), configure postgresql.conf to support logical decoding:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Then, create a publication for all tables. This tells Postgres to start tracking changes:

-- Run on Source
CREATE PUBLICATION migration_pub FOR ALL TABLES;

On your target (CoolVDS) instance, you set up the subscription. This connects back to the source and pulls data. Warning: Ensure your firewall allows port 5432 traffic from the CoolVDS IP. If you are hosting with us, our DDoS protection won't block legitimate replication traffic, but your old provider might.

-- Run on Target
CREATE SUBSCRIPTION migration_sub 
CONNECTION 'host=source_ip port=5432 dbname=prod_db user=replicator password=secure_pass' 
PUBLICATION migration_pub;

Pro Tip: During the initial data copy, disable indexes on the target (except the primary key). It speeds up insertion by 30-40%. Rebuild them before the cutover using CONCURRENTLY to avoid locking.

2. MySQL 8.0/8.4: GTID Replication

If you are running MySQL 8.0 or the newer 8.4 LTS, Global Transaction Identifiers (GTID) are mandatory for sanity. Traditional binary log file/position replication is brittle; one crash and you lose your place. GTID auto-heals.

Ensure your my.cnf is tuned for the write-heavy workload of an import:

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
# Temporarily relax durability for initial sync speed
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

Once the migration completes, revert innodb_flush_log_at_trx_commit to 1. Safety first.

The IOPS Bottleneck: Why Cheap VPS Fails

Here is where most migrations die. When you initiate that initial sync, your target disk is getting hammered with write operations. If you are on a budget host sharing mechanical spindles or throttled SATA SSDs with 50 other noisy neighbors, your replication lag will increase faster than the data transfer.

I've run benchmarks comparing standard cloud volumes against CoolVDS local NVMe storage during a heavy 500GB import.

We use KVM virtualization to ensure you get raw access to these speeds. When you are syncing a database, high I/O wait (iowait) is the enemy. It causes the replication process to stall, and if the source generates WAL logs faster than the target can write them, your disk on the source fills up, crashing production. I've seen it happen. It's ugly.

The Legal Check: Schrems II and Datatilsynet

Moving data to Norway isn't just a technical task; it's often a compliance one. Since the Schrems II ruling, Nordic companies are terrified of US-owned cloud providers. By migrating to a sovereign Norwegian infrastructure like CoolVDS, you are simplifying your GDPR audit trail.

Ensure your data is encrypted in transit. Both PostgreSQL and MySQL support SSL connections for replication. Use it. If you are piping data over the public internet between Frankfurt and Oslo, wrap it in a WireGuard tunnel. It adds negligible overhead (unlike OpenVPN) and secures the stream.

The Cutover: The Final 60 Seconds

Your data is synced. The lag is 0 bytes. Now, the terrifying part. Switching traffic.

1. Lower TTLs: 24 hours before migration, lower your DNS TTL to 60 seconds.
2. Stop Writes: Put your application in maintenance mode. If you can't, set the source database to read-only.
3. Verify LSN: Check that the Log Sequence Number (LSN) on the target matches the source.
4. Promote Target: Detach the replica and make it a primary.
5. Re-point App: Update connection strings or flip the DNS.

-- PostgreSQL: Verify sync status
SELECT sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;

If the replay_lsn matches sent_lsn, you are golden. Flip the switch.

Don't Let Hardware Fail You

Migration logic is complex enough without fighting the hardware. You need high clock speeds for the single-threaded nature of some replication processes, and you need NVMe storage to absorb the write penalty. We built CoolVDS specifically for these high-stress scenarios.

Don't guess on performance. Spin up a high-performance NVMe instance in Oslo today and benchmark your pg_restore speeds. The difference isn't just time; it's your sanity.