How We Migrated a 2TB PostgreSQL Database with Zero Downtime

The challenge

Last autumn, one of our enterprise customers needed to move their primary PostgreSQL cluster from a co-located data center to our Frankfurt infrastructure. The database was 2.1TB, transactional, and could not afford more than a few seconds of read-only mode — certainly no hard downtime window.

Their engineering team had attempted a previous migration six months earlier using pg_dump and a maintenance window. It failed halfway through due to a timeout, corrupted their standby, and cost them a Sunday plus four engineers' weekends. They came to us with a mandate: make it invisible to users.

The approach: logical replication + traffic cutover

We used PostgreSQL's built-in logical replication to stream changes continuously from the source cluster to the new Aureus cluster while the initial base backup completed. This is the same mechanism that powers Postgres CDC pipelines — but here we're using it for migration rather than analytics.

The high-level steps:

1. Provision the target cluster on Aureus (Cluster L, Frankfurt)
2. Take a base backup with pg_basebackup using --checkpoint=fast
3. Create a publication on the source and a subscription on the target
4. Monitor replication lag until it reaches under 100ms consistently
5. Freeze writes on source for ~3 seconds, confirm lag = 0, update DNS
6. Promote the new cluster

The replication slot trap

One subtlety we hit: logical replication slots on the source will hold onto WAL segments indefinitely if the subscriber falls behind. On a busy database generating 8GB/hour of WAL, this is dangerous. We set max_slot_wal_keep_size = 50GB and monitored pg_replication_slots obsessively.

We also ran a dedicated replication monitoring loop that paged on-call if lag exceeded 500ms for more than 30 seconds. It fired once, during a batch job at 2am. We throttled the job; lag recovered in 4 minutes.

The cutover

We coordinated with the customer's engineering lead over a shared Slack channel. At 3:17am UTC on a Tuesday — their lowest-traffic 15-minute window — we:

1. Set default_transaction_read_only = on on the source
2. Watched the replication dashboard reach 0 bytes lag
3. Updated the CNAME record (60-second TTL we'd pre-lowered)
4. Confirmed writes landing on the new cluster via a canary query
5. Set read-only mode off on source (now a warm standby)

Total user-visible impact: 4 seconds of read-only mode. Zero errors in application logs.

Lessons

The most important preparation step was not the technical setup — it was lowering the DNS TTL 48 hours in advance. Engineers forget this constantly. A 300-second TTL means a 5-minute cutover delay you cannot compress.

Also: always test the replication setup on a non-production copy of the schema first. We spent 2 hours discovering that one table used GENERATED ALWAYS AS IDENTITY in a way that needed a schema-level tweak before logical replication would accept it.

If you're planning a large PostgreSQL migration and want a second pair of eyes on the plan, open a support ticket — this is something we do regularly for customers on Cluster M and above.