The Anxiety of the "Cutover"

There is a specific kind of silence that falls over a Slack channel at 03:00 AM CET when a database migration is about to execute. It’s the silence of a team praying that the rsync caught everything and that the DNS TTL actually expired. I have managed infrastructure for over a decade, from bare metal racks in basement data centers to distributed cloud clusters, and one truth remains constant: moving stateful data is the most dangerous operation in IT.

For Norwegian businesses in 2021, the stakes are higher. Following the Schrems II ruling last year, relying on US-owned cloud providers involves a legal tightrope walk regarding data sovereignty. Many CTOs are now frantically repatriating data to domestic infrastructure to satisfy Datatilsynet (The Norwegian Data Protection Authority). But moving terabytes of active SQL data from a data center in Frankfurt to a server in Oslo without downtime isn't just about copying files. It is about physics, latency, and I/O throughput.

The Fallacy of "Maintenance Windows"

The traditional method of "Dump, Transfer, Restore" is dead for any serious application. If you have a 500GB database and a moderate network connection, a full mysqldump or pg_dump restore cycle can take hours. In an era where 99.99% uptime is the baseline expectation, telling a client their e-commerce store will be offline for six hours is unacceptable.

The only professional path forward is Live Replication. This involves setting up the new server (the target) as a replica of the current live server (the source), letting them sync until the lag is zero, and then promoting the replica to master. This sounds simple in theory, but the implementation details are where projects die.

Prerequisite: The Hardware Bottleneck

Before touching a config file, we must address storage. Database replication is incredibly I/O intensive. The target server must write incoming replication logs to disk faster than the source server receives new queries. If your target server uses standard SATA SSDs (or worse, spinning rust) while your source is under heavy load, the replica will never catch up. You will be stuck in a permanent state of "replication lag."

Pro Tip: When provisioning your target instance on CoolVDS, always select the NVMe storage tier. During the "catch-up" phase of replication, the disk queue length often spikes. NVMe interfaces handle high queue depths significantly better than SATA, preventing the replication process from stalling due to I/O wait. We see a 40% reduction in sync time purely by switching to NVMe backplanes.

Strategy: PostgreSQL Streaming Replication

For PostgreSQL (versions 12 or 13 are standard today), the most robust method is physical streaming replication. This creates an exact binary copy of your cluster. It requires the PostgreSQL versions to match exactly, but it is far more performant than logical replication for full migrations.

1. Configure the Source (Primary)

On your existing server, you need to create a replication user and allow the IP address of your new CoolVDS instance. Edit postgresql.conf to ensure the WAL (Write Ahead Log) level is sufficient.

# postgresql.conf
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 500  # Critical: keeps logs available if network drops

Then, update pg_hba.conf. Security is non-negotiable, especially traversing public networks. Always use SSL.

# pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl replication     replicator      185.xxx.xxx.xxx/32      md5

2. The Base Backup

On the target machine (your empty CoolVDS instance), stop the postgres service and clear the data directory. Then run the base backup utility. This pulls the initial snapshot without stopping the source database.

# Run on the TARGET server
systemctl stop postgresql-13
rm -rf /var/lib/pgsql/13/data/*

# The P flag enables progress, R creates the standby.signal file automatically
pg_basebackup -h source-db.example.com -D /var/lib/pgsql/13/data -U replicator -P -R -X stream

Once this command finishes, start the service. The database is now running in "Hot Standby" mode. It is read-only and is consuming WAL files from the source server in near real-time.

The Latency Factor: Oslo vs. The World

When replicating across borders—say, from AWS eu-central-1 to a CoolVDS datacenter in Oslo—latency becomes a variable. The round-trip time (RTT) affects synchronous replication, but for migration, we use asynchronous replication to avoid slowing down the production master. However, you must monitor the lag.

Use this query on the Source to check how far behind the target is:

SELECT client_addr, state, sync_state, 
       (pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn))::float / 1024 / 1024 as lag_mb
FROM pg_stat_replication;

If lag_mb is consistently growing, your network pipe or your target disk I/O is insufficient. This is often where budget VPS providers fail; their "shared" vCPUs get throttled during the high-load catch-up phase. At CoolVDS, we utilize KVM with dedicated resource allocation to ensure that CPU stealing doesn't corrupt your migration timeline.

The MySQL/MariaDB Approach (GTID)

If you are running MySQL 8.0, the logic is similar but relies on Global Transaction IDs (GTIDs). GTIDs make failover much safer than the old binary log file/position method because the server tracks transaction IDs automatically.

Target Configuration

On the CoolVDS target, add this to my.cnf:

[mysqld]
server-id = 2  # Must be unique
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = 1

After dumping and restoring the initial dataset (using Percona XtraBackup is recommended for non-blocking backups), you configure the replication channel:

CHANGE MASTER TO
  MASTER_HOST='source-db.example.com',
  MASTER_USER='replicator',
  MASTER_PASSWORD='SecurePassword123!',
  MASTER_AUTO_POSITION=1,
  MASTER_SSL=1;

START SLAVE;

The Cutover Moment

Once your replication lag is effectively zero (typically under 100ms), you are ready to migrate. This is the procedure to ensure zero data loss:

1. Lower TTL: Set your DNS TTL to 60 seconds at least 24 hours in advance.
2. Block Writes: Reconfigure your application or firewall to block writes to the Source DB. This ensures the data state is frozen.
3. Verify Sync: Ensure the target has processed the final transaction.
4. Promote Target: Run pg_ctl promote (Postgres) or disable read_only (MySQL) on the CoolVDS instance.
5. Switch App Config: Point your application strings to the new IP.

If executed correctly, the total "downtime" (write unavailability) is less than 30 seconds. The application continues to serve read traffic throughout the process.

Why Sovereignty Matters

Beyond the technical achievement, this migration solves a critical business problem. By moving your database to a Norwegian provider like CoolVDS, you drastically simplify your GDPR compliance posture. You are no longer subject to the ambiguity of the US CLOUD Act. Your data resides on physical disks in Norway, protected by Norwegian law.

Technical excellence requires control. Control over your I/O, control over your latency, and control over your data's legal jurisdiction. Don't let a slow disk or a legal grey area compromise your infrastructure.

Ready to bring your data home? Spin up a high-performance NVMe instance on CoolVDS today and start your replication stream.