PostgreSQL Logical Replication for Zero-Downtime Schema Migrations

---
title: "Zero-Downtime Schema Migrations with PostgreSQL Logical Replication"
published: true
description: "Logical replication slots let you run old and new schema versions simultaneously. Here is the full cutover playbook — including the slot lag, WAL bloat, and replication identity traps that will wake you up at 3am."
tags: [postgresql, architecture, devops, api]
canonical_url: https://blog.mvpfactory.co/zero-downtime-schema-migrations-postgres-logical-replication
---

What We Are Building

By the end of this tutorial you will have a working playbook for zero-downtime schema migrations using PostgreSQL logical replication. We will run two schema versions simultaneously, drain in-flight writes safely, and execute a clean atomic cutover — without a table lock in sight.

The mental model to carry in: this is blue/green deployment applied to your schema layer, not a database problem.

Prerequisites

• PostgreSQL 10+ (logical replication GA)
• A replication-capable user with REPLICATION privilege
• Access to pg_replication_slots and pg_stat_activity
• Ability to set wal_level = logical on the primary

---

Step 1 — Create the Replication Slot and Publication

On the source (primary), create a logical replication slot and publish the table you are migrating.

-- On the source (primary), create a replication slot
SELECT pg_create_logical_replication_slot(
  'migration_slot',
  'pgoutput'
);

-- Create a publication for the target table
CREATE PUBLICATION migration_pub FOR TABLE orders;

Logical replication decodes the WAL stream into row-level change events. Unlike physical replication it is schema-aware and filterable — you can replicate a single table, transform column names mid-stream, and keep two schema versions live simultaneously.

Step 2 — Subscribe from the Target

The target can be the same cluster under a different schema, or a separate instance entirely.

CREATE SUBSCRIPTION migration_sub
  CONNECTION 'host=primary dbname=prod user=replicator'
  PUBLICATION migration_pub
  WITH (slot_name = 'migration_slot', create_slot = false);

Your application writes to the old schema. Every insert, update, and delete propagates to the new schema in near-real-time. When lag hits zero, you flip the connection string. That is the whole model.

Step 3 — Monitor Slot Lag Continuously

Here is the gotcha that will save you hours: a logical replication slot holds WAL segments until the subscriber confirms consumption. If your subscriber falls behind, WAL accumulates on disk unbounded.

SELECT slot_name, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

Set a hard ceiling before you create any slot in production:

-- postgresql.conf
max_slot_wal_keep_size = 10GB

If a slot exceeds this, Postgres drops the slot rather than crash the primary. That is the correct tradeoff.

Step 4 — Execute the Cutover Playbook

# 1. Monitor lag until it hits zero
watch -n1 "psql -c \"SELECT confirmed_flush_lsn, pg_current_wal_lsn() FROM pg_replication_slots WHERE slot_name='migration_slot'\""

# 2. Quiesce writes (feature flag or maintenance mode)
# 3. Poll pg_stat_activity until active write connections = 0
# 4. Rotate DNS / connection string to new schema
# 5. Smoke test new schema
# 6. Clean up slot — do NOT leave it dangling
psql -c "SELECT pg_drop_replication_slot('migration_slot');"

The window between step 2 and step 4 should be under 10 seconds. If it is not, your drain logic is incomplete.

---

Gotchas

1. Replication Identity Silently Dropping Updates

The docs do not make this obvious enough. For logical replication to propagate UPDATE and DELETE, Postgres needs a row identifier on the subscriber — the replication identity. Tables without a primary key have none under DEFAULT identity, so updates and deletes are silently dropped. You will only discover this when row counts diverge at cutover.

-- Audit every table in scope before you start
SELECT relname, relreplident FROM pg_class WHERE relkind = 'r';
-- 'd' = default (primary key) — safe
-- 'n' = nothing — silent data loss incoming

-- Remediate tables with no primary key
ALTER TABLE orders REPLICA IDENTITY FULL;

2. The Cutover Window You Did Not Actually Drain

Zero lag on the slot does not mean zero in-flight writes. Application connections hold uncommitted transactions. Poll — do not sleep.

-- Force long-running transactions out
SET statement_timeout = '5s';

-- Poll until this returns 0 before proceeding
SELECT count(*) FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT ILIKE 'select%'
  AND backend_type = 'client backend';

A fixed sleep 5 is not a drain. This query returning zero is a drain.

3. WAL Lag Estimates to Calibrate Alerting

Slot lag	WAL retained	Risk
0s	Minimal	Safe
30s	~500MB at 100 TPS	Monitor
5min	Multi-GB	Disk pressure
Stuck slot	Unbounded	Disk full, primary crash

(Assumes ~5KB avg row size; actual figures vary by schema and wal_level setting.)

---

Three Things to Do Before Your Next Migration

Let me show you a pattern I use in every project. Before touching any schema in production:

1. Set max_slot_wal_keep_size — an unmonitored stuck slot will fill your disk and crash your primary. This is not a hypothetical.
2. Audit replication identity on every table in scope. Any value other than 'd' backed by a real primary key needs remediation first.
3. Treat cutover as a traffic coordination exercise. Quiesce at the application layer, poll for zero active write connections, then switch.

The replication slot approach is the only migration strategy that reliably keeps your SLO intact — but only if you respect the WAL retention semantics and the replication identity contract. Get the traffic layer right first, and the database cutover becomes the easy part.