Logical Replication for Zero-Downtime PostgreSQL Migrations

The Problem#

Moving a busy PostgreSQL database to a new cluster, region, or major version can require downtime if you rely only on dump and restore.

Why It Matters#

Logical replication lets you copy table changes while the source remains online. That makes it useful for major-version upgrades, cloud migrations, and topology changes where downtime has to be measured in minutes, not hours.

Core Concepts#

The source creates a publication. The target creates a subscription. Initial table data is copied, then ongoing changes stream through replication slots. Cutover happens when replication lag is near zero and writes are briefly stopped.

Implementation#

Create a publication on the source:

CREATE PUBLICATION app_migration FOR TABLE users, orders, payments;

Create a subscription on the target:

CREATE SUBSCRIPTION app_migration_sub
CONNECTION 'host=source dbname=app user=replicator password=...'
PUBLICATION app_migration;

Monitor lag until the target is caught up, then freeze writes, advance sequences, switch application connection strings, and run validation queries.

Real Project Scenario#

A SaaS company wants to move from a self-managed Postgres cluster to a managed cloud database. A dump and restore would require hours of downtime. Logical replication allows the new cluster to catch up while the old cluster continues serving traffic, then the team performs a short write freeze for cutover.

Production Setup#

Rehearse the migration with a recent production snapshot. Measure initial copy time, replication lag, sequence sync, validation queries, and rollback time. Keep the old cluster read-only for a short period after cutover so rollback remains possible if hidden issues appear.

Common Mistakes#

• Forgetting that DDL is not automatically replicated.
• Missing tables because the publication list was incomplete.
• Not syncing sequences before cutover.
• Letting long transactions hold back replication slots and grow WAL.

Production Considerations#

Write a cutover checklist and a rollback checklist. Include DNS or secret changes, connection pool drain, schema diff, row counts, checksums for critical tables, and a decision deadline.

Security#

Use a dedicated replication user with only the required privileges. Store connection strings in the same secret manager used by the application.

Performance#

Initial copy can stress the source. Run it during a quiet period, throttle if needed, and watch WAL disk usage on the publisher.

Summary#

Logical replication makes PostgreSQL migrations safer when you plan schema changes, sequence sync, validation, cutover, and rollback as one operation.