Zero-Downtime Database Migrations: A Survival Guide for Nordic Systems

Let’s be honest: moving a production database is the systems engineering equivalent of open-heart surgery. You are keeping the patient alive (serving requests) while transplanting the most vital organ. One wrong command, one missed configuration flag, and you aren't just looking at downtime—you are staring at data corruption.

I have spent the last decade watching developers try to mysqldump 500GB datasets over a public network connection, only to wonder why the restore takes 14 hours and the data is stale by the time it finishes. That is not a migration strategy; that is hope. And hope is not a tactic in DevOps.

Whether you are repatriating data to Norway to satisfy Schrems II compliance or simply moving from a legacy provider to high-performance infrastructure, you need a plan that guarantees consistency. This guide covers the replication-based migration strategy we use for high-value workloads moving to CoolVDS.

1. The Hardware Reality Check

Before you even touch a config file, look at the destination storage. Database imports are heavy on random writes. If your target VPS is running on standard SATA SSDs (or heaven forbid, spinning rust) with noisy neighbors, your replication lag will never catch up to the master.

Pro Tip: Always verify the disk scheduler on your Linux target. In 2021, for NVMe drives, you should be using none or kyber, not cfq.

We built CoolVDS on pure NVMe arrays specifically to handle the IOPS storms that occur during the "catch-up" phase of replication. If your destination disk creates backpressure, the migration fails. Period.

2. The Strategy: Replication, Not Dump

For any database larger than 5GB, "maintenance windows" are a lie. You cannot afford to shut down for the duration of a transfer. The only professional path is Master-Slave Replication.

The concept is simple: take a snapshot, restore it on the target (CoolVDS), and let the database engine replay the logs to sync the delta. Once lag is zero, you cut over.

Scenario A: MySQL 8.0 / MariaDB

If you are still using MyISAM, stop reading and migrate to InnoDB first. For everyone else, we use GTID (Global Transaction ID) for fail-safe tracking.

Step 1: Configure the Source (Master)
Edit your my.cnf. You need binary logging enabled and a unique server ID. If you change this, a restart is required (sorry).

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

Step 2: Create the Replication User
Do not use root. Create a dedicated user restricted to the CoolVDS IP address range for security.

CREATE USER 'repl_user'@'185.x.x.x' IDENTIFIED WITH mysql_native_password BY 'Complex_Password_2021!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'185.x.x.x';
FLUSH PRIVILEGES;

Step 3: The Non-Blocking Dump
Use Percona XtraBackup if you have root access. If not, mysqldump with --single-transaction is your friend. It prevents table locking on InnoDB.

mysqldump --single-transaction \
  --master-data=2 \
  --routines \
  --triggers \
  --all-databases \
  -u root -p > dump.sql

Scenario B: PostgreSQL 13/14

Postgres makes this slightly more elegant with Logical Replication (Pub/Sub), available natively since PG 10. This allows you to replicate specific databases rather than the entire cluster instance.

On the Source (Provider A):

-- In postgresql.conf, ensure wal_level is logical
-- wal_level = logical

-- Create a publication
CREATE PUBLICATION my_migration_pub FOR ALL TABLES;

On the Destination (CoolVDS):

-- Create the subscription connecting back to source
CREATE SUBSCRIPTION my_migration_sub 
CONNECTION 'host=source_ip port=5432 dbname=mydb user=rep_user password=secret' 
PUBLICATION my_migration_pub;

This method bypasses a lot of the file-level overhead and is exceptionally fast on our infrastructure due to the low latency network stack we maintain in Oslo.

3. The Compliance Layer: Schrems II & GDPR

Here is the boring but dangerous part. In July 2020, the CJEU invalidated the Privacy Shield (Schrems II). If you are migrating data from a US-owned cloud provider to a European one, you are doing the right thing for compliance.

However, during the migration, traffic is flowing across the public internet. The Norwegian Data Protection Authority (Datatilsynet) is watching.

• Encryption is mandatory: Ensure your replication stream is SSL/TLS encrypted.
• Certificates: Generate self-signed certs or use Let's Encrypt, but do not send data in cleartext.

Example of enforcing SSL in MySQL:

CHANGE MASTER TO 
  MASTER_HOST='source_ip', 
  MASTER_USER='repl_user', 
  MASTER_PASSWORD='password', 
  MASTER_SSL=1, 
  MASTER_SSL_CA='/path/to/ca.pem';

4. The Cutover (The Scary Part)

Your CoolVDS instance is now a real-time mirror of your production site. The Seconds_Behind_Master is oscillating between 0 and 1. It is time to switch.

1. Lower TTLs: 24 hours before migration, lower your DNS TTL to 300 seconds.
2. Maintenance Mode: Place the application in read-only mode.
3. Wait for Sync: Watch the logs. When the master receives no writes and the slave has executed the last transaction, you are consistent.
4. Promote Slave:

   -- On CoolVDS (MySQL)
   STOP SLAVE;
   RESET MASTER;
   -- On CoolVDS (Postgres)
   ALTER SUBSCRIPTION my_migration_sub DISABLE;
   -- Then promote the server to read/write

5. Point Application: Update your app config to the new IP.

Why We Obsess Over This

We see too many developers treating hosting like a commodity. But when you are debugging a deadlock at 3 AM, "commodity" doesn't help you. Control does.

At CoolVDS, we don't oversubscribe our CPU cores. When you run a heavy pg_restore or catch up on replication lag, you get the cycles you paid for. In the Nordic market, where latency to Oslo exchanges (NIX) is measured in single-digit milliseconds, having a database that responds instantly is the difference between a bounce and a conversion.

Don't let slow I/O kill your migration. If you are planning a move, spin up a high-frequency compute instance with us and benchmark the disk IO first. The numbers usually do the selling for us.