Zero-Downtime Database Migrations: A Survival Guide for Norwegian DevOps

I have seen servers melt. Not literally, of course, but I have watched load averages spike to 500 on a dual-core instance because a junior dev decided to run a non-transactional pg_dump on a live production master at 2:00 PM. The site went down. The CEO started screaming. It wasn't pretty.

Migrations are the heart surgery of infrastructure. You don't pause the heart to fix a valve; you bypass it. Yet, too many teams in Oslo still schedule "maintenance windows" at 3 AM to scp terabytes of data across the internet. It’s 2021. We can do better. If your strategy involves downtime, you have already failed.

This is how you move terabytes of data without dropping a single packet, keeping the Datatilsynet happy, and ensuring your IOPS don't hit the floor.

The Latency Trap: Why Distance Matters

Before we touch a config file, let's talk about physics. If you are migrating a database from a legacy provider in Frankfurt to a new home in Norway, you are fighting the speed of light. Round-trip time (RTT) matters when you are syncing binary logs.

When we built the infrastructure for CoolVDS, we placed our racks directly in Oslo to minimize hops to the NIX (Norwegian Internet Exchange). Why? Because synchronous replication over high latency kills write throughput. For a migration, you want asynchronous replication initially, flipping to synchronous only at the very end (if at all).

Pro Tip: Always measure your network throughput before starting a replication stream.
iperf3 -c target_ip -p 5201
If you aren't getting near line speed, check your MTU settings. Jumbo frames (MTU 9000) can save your life on internal networks, but over the public internet, standard 1500 is the law.

Strategy: The Replica-Promotion Method

Forget dump and restore for anything larger than 10GB. The only robust way to migrate a live database is to make the new server a replica of the old one.

1. The MySQL/MariaDB Approach (GTID is your friend)

If you are still using file-based positioning for replication in 2021, stop. Global Transaction IDs (GTID) make failover and migration sane. Here is how you prepare the new CoolVDS instance to act as a slave (replica) to your current master.

First, on your current master, ensure binary logging is enabled and a replication user exists:

-- On the OLD Master
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'StrongPassword_2021!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Next, dump the data. Crucially, use --single-transaction to avoid locking InnoDB tables and --master-data=2 to include the log position in the dump file.

mysqldump -u root -p --all-databases --single-transaction --quick --master-data=2 --triggers --routines --events > full_dump.sql

Import this to your new CoolVDS NVMe instance. Our KVM virtualization ensures that when you import, you are getting dedicated I/O queues, not fighting a noisy neighbor for disk access. This significantly speeds up the restore process.

Once imported, configure the link:

-- On the NEW CoolVDS Server (The Replica)
CHANGE MASTER TO
  MASTER_HOST='old_server_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPassword_2021!',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

2. The PostgreSQL Method (Logical Replication)

PostgreSQL 13 (current stable) has excellent logical replication support. It’s often cleaner than streaming replication for migrations because it allows you to migrate across major version numbers with less friction.

Step 1: Configure the Publisher (Old Server)

Edit postgresql.conf:

wal_level = logical
max_replication_slots = 5
max_wal_senders = 5

Add to pg_hba.conf to allow the CoolVDS IP:

host    replication     all             185.xxx.xxx.xxx/32          md5

Step 2: Create the Publication

-- On Old Database
CREATE PUBLICATION my_migration_pub FOR ALL TABLES;

Step 3: Subscribe from the New Server

-- On New CoolVDS Database
CREATE SUBSCRIPTION my_migration_sub 
CONNECTION 'host=old_server_ip port=5432 dbname=mydb user=postgres password=secret' 
PUBLICATION my_migration_pub;

This will snapshot the data and then stream changes. No downtime yet.

The Hardware Reality: Why NVMe Matters

You can have the best DBA scripts in the world, but if your disk I/O chokes during the catch-up phase, you will never reach zero lag. This is where the underlying infrastructure becomes the bottleneck.

During a migration, the new server is under double stress: it's writing the initial dataset and applying the incoming stream of changes from the master. On standard SATA SSDs, write amplification can cause latency spikes. You see this as iowait in top.

We built CoolVDS on pure NVMe storage arrays precisely for this scenario. When you are replaying 50GB of binary logs to catch up, you need raw throughput. Don't let cheap hosting kill your migration.

The "Cutover": Compliance and Switch

Once Seconds_Behind_Master is 0, you are ready. But wait. Are you legally ready?

The Schrems II Factor

Since the CJEU ruling last year (July 2020), moving personal data to US-owned cloud providers is a compliance minefield. If your database contains Norwegian user data, simply hosting it on a US giant's "Oslo region" might not be enough to satisfy Datatilsynet if they can theoretically access the data under FISA 702.

Migrating to a Norwegian-owned provider like CoolVDS isn't just a technical upgrade; it's a compliance shield. We are governed by Norwegian law. Your data stays here.

Executing the Switch

1. Lower TTLs: 24 hours before migration, lower your DNS TTLs to 60 seconds.
2. Read-Only Mode: Set the old master to read-only to ensure data consistency.

SET GLOBAL read_only = ON;

3. Verify Sync: Ensure the new server has processed the final transaction.
4. Promote New Master:

   STOP SLAVE;
   RESET MASTER;

5. Point App: Update your connection strings or switch the Floating IP to the new CoolVDS instance.

Total write downtime? About 5 to 10 seconds. Your users won't even blink.

Performance Tuning for the New Home

Now that you are on fresh hardware, don't just copy the old my.cnf. You likely have more resources now.

If you are on a 16GB RAM instance, ensure your Buffer Pool is sized correctly, but leave room for the OS:

[mysqld]
# Allocating approx 70-80% of RAM for InnoDB
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
# Vital for data integrity on crash
innodb_flush_log_at_trx_commit = 1

On CoolVDS, because we don't steal CPU cycles, you can be more aggressive with innodb_io_capacity. Try setting it to 2000 or higher, whereas on shared clouds you might be limited to 200.

Conclusion

Migrations are risky, but they are necessary. The key is removing variables. You remove the variable of "network flakiness" by choosing a host with peering at NIX. You remove the variable of "disk contention" by using NVMe. And you remove the variable of "legal gray areas" by hosting in Norway.

Don't let slow I/O kill your SEO or your uptime. If you are planning a move, do it right.

Ready to test the throughput? Deploy a high-performance NVMe instance on CoolVDS in 55 seconds and see the difference raw power makes.