Database Migrations Don't Have To Be a 3 AM Nightmare

I haven't slept through a scheduled maintenance window since 2018. If you are reading this, you probably haven't either. The sheer panic of watching a pg_restore progress bar crawl at 2MB/s while your CEO asks why the shop is still down is a rite of passage for every Systems Architect. But it doesn't have to be the standard.

In Norway, where data sovereignty is strictly enforced by Datatilsynet and latency to the NIX (Norwegian Internet Exchange) can make or break a high-frequency trading bot or a real-time bidding platform, the "dump and restore" method is dead. It is too slow, too risky, and honestly, unprofessional.

We are going to discuss how to move a production database with minimal downtime using Logical Replication. This isn't theoretical. This is the exact strategy I used last month to move a 4TB financial dataset from a legacy bare-metal cluster to a modern KVM-based infrastructure.

The Hardware Bottleneck: Why Migrations Fail

Before we touch a single config file, we need to address the elephant in the server room: I/O Wait.

Most failed migrations aren't caused by bad SQL; they are caused by disk choking. When you are syncing terabytes of data, your disk I/O is saturated. If you are attempting this on a budget VPS with shared spinning rust (HDD) or throttled SSDs, you will fail. The replication lag will grow faster than the data transfer.

Pro Tip: Never attempt a live migration on shared hosting. You need guaranteed IOPS. This is why for critical workloads, we default to CoolVDS instances. Their NVMe storage stack passes the raw PCIe lanes through KVM, effectively eliminating the virtualization overhead that usually kills write performance during massive imports.

Phase 1: The Architecture of a Zero-Downtime Move

We aren't shutting down the old server. Instead, we are building a parallel reality. The new server (The Target) will subscribe to the changes of the old server (The Source) in real-time. Once they are identical, we flip the DNS.

1. Network Prerequisites

Latency kills replication. If your Source is in Frankfurt and your Target is in Oslo, the speed of light is your enemy. Ensure your new CoolVDS instance has a clean route. Check the latency:

mtr --report --report-cycles=10 185.x.x.x

If you see packet loss or jitter >5ms within Norway, abort and fix the routing. Stability is non-negotiable.

Phase 2: Preparing the Source (PostgreSQL Example)

Let's assume we are running PostgreSQL 16 or 17 (standard in 2025). We need to change the wal_level to logical. This requires a restart, which is the only required downtime on the Source.

# postgresql.conf on SOURCE
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

You also need to allow the replication connection in pg_hba.conf. Be specific with IPs. Do not use 0.0.0.0/0.

# pg_hba.conf
host    replication     rep_user        192.168.1.50/32         scram-sha-256

Phase 3: Tuning the Target for Ingestion

The default Postgres config is designed for safety, not for swallowing 4TB of data in an hour. On your CoolVDS Target instance, temporarily tune PostgreSQL to be greedy. We want it to use every bit of RAM available.

# postgresql.conf on TARGET (Temporary settings!)
fsync = off  # DANGER: Only during initial sync. Turn back ON immediately after!
synchronous_commit = off
maintenance_work_mem = 2GB
max_wal_size = 4GB
checkpoint_timeout = 30min

Warning: Turning off fsync means if power fails, the DB is corrupted. Since this is a fresh replica, we don't care—we'd just restart the migration. But never run production with fsync off.

Phase 4: Execution

First, create the publication on the Source.

-- On SOURCE DB
CREATE PUBLICATION my_migration_pub FOR ALL TABLES;

Next, dump the schema only. We don't want the data yet, just the structure.

pg_dump -h source_ip -U user -s -f schema.sql dbname
psql -h target_ip -U user -d dbname -f schema.sql

Now, the magic moment. We create the subscription on the Target. This command instructs the Target to pull all existing data and then stay in sync.

-- On TARGET DB
CREATE SUBSCRIPTION my_migration_sub 
CONNECTION 'host=source_ip port=5432 dbname=dbname user=rep_user password=secret' 
PUBLICATION my_migration_pub;

Monitor the process. Do not just walk away. Use this query on the Target to check status:

SELECT subscription_name, status, last_msg_send_time 
FROM pg_stat_subscription_stats;

Comparison of Migration Methods

Phase 5: The Cutover and GDPR Compliance

Once the replication lag hits 0 bytes, you are ready. But wait. Is your data legally allowed to live on this new server? If you are dealing with Norwegian citizen data, Schrems II requirements mean you need to verify the physical location of the hosting provider.

This is where infrastructure transparency matters. When we provision on CoolVDS, we know the hardware is sitting in a compliant datacenter in the Nordics, not replicated to a bucket in Virginia without our knowledge. Always verify your provider's sub-processors.

The Cutover Sequence:

1. Stop the application servers (or put them in Read-Only mode).
2. Wait for the last WAL entries to sync (usually milliseconds on CoolVDS NVMe).
3. Reset sequences on Target (Logical replication doesn't always sync sequences perfectly).

-- Sync sequences manually if needed
SELECT setval('my_table_id_seq', (SELECT MAX(id) FROM my_table));

4. Turn fsync = on on Target. Restart Postgres.
5. Point App config to new IP.
6. Start App.

Why Infrastructure Choice Determines Success

You can script the perfect migration, but if the underlying host steals your CPU cycles, you will fail. I've seen migrations stall at 90% because a "noisy neighbor" on a cheap VPS started mining crypto.

For mission-critical databases, predictable performance is the only metric that matters. The combination of KVM isolation and NVMe storage ensures that when you hit COMMIT, the disk actually writes. Don't risk your reputation on budget hardware.

Ready to test your replication strategy? Spin up a high-performance sandbox on CoolVDS in under 60 seconds and simulate the load before you touch production.