Case Study: 120GB PostgreSQL Logical Replication Migration

The Problem

A production database that couldn't afford an hour of downtime — let alone a weekend

PostgreSQL (120GB+) was on AWS EC2 serving a live production application. The client needed to migrate to another VPS to reduce costs — but had no maintenance window. pg_dump and restore would require 4–6 hours of downtime. Logical replication was the only viable path.

Risk: Logical replication has strict requirements around replica identity, partition tables, sequences, and OIDs — any one of which can silently cause replication to lag or fail without obvious errors.

Migration Approach

Six phases — the wrong order would be catastrophic

1

Schema migration first (pg_dump schema-only)Tables, indexes, constraints, and sequences created on target before replication starts. Replication cannot create tables — only replicate rows.

2

Set replica identity on all tablesSeveral legacy tables lacked primary keys. Applied REPLICA IDENTITY FULL to those — with the trade-off of larger WAL records for updates and deletes.

3

Create publication on source, subscription on targetCREATE PUBLICATION rsb_pub FOR ALL TABLES on source. CREATE SUBSCRIPTION rsb_sub on target. PostgreSQL begins table sync then streams live WAL changes.

4

Monitor replication lag until near-zeroMonitored pg_stat_replication.write_lag. Waited for lag under 100ms across all tables before scheduling cutover.

5

Cutover — app config switch + sequence syncMaintenance mode (30s) → sync all sequences manually (sequences don't replicate) → update DATABASE_URL → remove maintenance mode. Total downtime: 47 seconds.

6

Post-migration nightly backup pipelineShell script: pg_dump at 02:00 IST → gzip → rsync to backup server → SMTP alert on success or failure.

Key Challenges

Four problems that nearly derailed the migration

🔢

OID mismatches breaking FK constraints on targetpg_dump schema-only doesn't preserve OIDs. Custom type references broke on target. Fixed with --no-oid flag and manual review of all custom type definitions.
💾

WAL slot exhaustion filling EC2 diskA 4-hour network interruption caused the slot to hold 18GB of WAL, nearly filling the disk. Fixed with max_slot_wal_keep_size = 10GB and CloudWatch alarm.
📊

Partition tables not replicating — silent failureLogical replication doesn't auto-replicate partition child tables. Monthly audit_log partitions were silently excluded. Fixed by explicitly listing all partition children in the publication.
🌐

pg_hba.conf blocking replication connectionpg_hba.conf was missing the replication entry for the target IP. Subscription silently failed to connect. Fixed by adding: host replication rsb_repl_user {target_ip}/32 md5.

Outcomes

120GB moved — 47 seconds of customer-facing downtime

47s

Total application downtime

120GB+

Data migrated successfully

0

Rows lost during migration

60%

Infrastructure cost reduction

Result: 100% data integrity confirmed via row counts, sequence values, and spot-check queries against both databases post-cutover.