Postgres Logical Replication: Slots, DDL, and Failover

At 2 a.m. on a Tuesday, a client's reporting replica silently stopped applying changes. The publisher kept accepting writes. The replica kept answering read queries. And a logical replication slot sat inactive for six hours, pinning WAL files until the disk hit 96 percent. Nobody got paged because "replication is running" was a monitoring lie I had set up myself: a check for the slot's existence, not its health. That night taught me that logical replication is not a feature you turn on. It is a pipeline you operate.

This is the guide I wish I had that night. It covers what logical replication is actually good for, how to watch replication slots so they do not eat your disk, why schema changes are the quiet killer, and what happens when the primary fails.

What logical replication is (and is not)

Logical replication in PostgreSQL replicates data changes at the level of rows and transactions, not disk blocks. A publisher marks tables in a publication. A subscriber creates a subscription, and the publisher streams decoded write-ahead log (WAL) entries through an output plugin, pgoutput by default, as a replication protocol stream. Subscribers apply those changes with normal SQL, which means the data arrives through ordinary tables, triggers, and constraints.

That design choice is the source of both its power and its traps:

Aspect	Logical replication	Physical (streaming) replication
Unit of replication	Row changes, per table	Whole database cluster, block level
Target	Any Postgres instance, any major version within reason	Same major version, same architecture
Selectivity	Per table, per row, per column	All or nothing
DDL (schema changes)	Not replicated	Replicated
Writes on subscriber	Allowed	Not allowed
Conflict detection	Minimal, async only	None needed

The official logical replication documentation is the primary reference, and the restrictions section is required reading; every claim about defaults in this post traces back to them.

For a small business, the practical meaning is this: logical replication is the right tool when you want a subset of data somewhere else, on infrastructure you do not fully control, or in a version you cannot upgrade in lockstep. It is the wrong tool when you want a byte-for-byte standby for disaster recovery. Use physical replication for that.

Use cases that justify the operational cost

I only recommend logical replication when one of these is true. Each one I have run in production.

Reporting and analytics offloading

Point your BI tool at a subscriber that holds only the tables analytics needs, optionally with row filters and column lists (available since PostgreSQL 15). Writers keep their I/O profile; readers stop hammering the OLTP primary. This is the most common and the most forgiving use case.

Zero-downtime major version upgrades

Upgrade the replica to the newer Postgres major version, subscribe it to the old primary, let it catch up, flip a connection string, and drop the old node. Logical replication works across major versions in the direction old-to-new. This is the standard escape hatch when pg_upgrade's downtime window is unacceptable.

Cross-region read scaling and consolidation

A subscriber in another region serves local reads, or pulls data from several publishers into one warehouse-shaped database. Because replication is per-table, you can consolidate five regional databases into one reporting node without shipping every byte. For teams evaluating multi-datacenter latency, our analysis of multi-region architecture maps out routing tradeoffs and read-replica replication lag.

Selective data sharing

Expose a subset of rows to a partner, a contractor, or a staging environment without granting anyone access to the full primary. Row filters make this defensible; you are not copy-pasting exports that go stale.

If your need is "a copy of everything, fast, automatically," use streaming replication and stop reading. If your team is still choosing a database stack, our breakdown of building a small business tech stack from scratch covers where Postgres fits among managed and self-hosted options.

Replication slots: the part that bites you

Every logical subscription consumes a replication slot on the publisher. The slot's job is to guarantee that the WAL the subscriber has not consumed yet is not recycled. That guarantee is unconditional. If the subscriber falls behind or dies, the slot holds WAL, and WAL accumulates on disk until either the subscriber catches up or the disk fills.

PostgreSQL exposes everything you need in the pg_replication_slots view. These are the columns I alert on:

• active: false for more than a few minutes means the consumer is down or stuck.
• inactive_since: tells you exactly when the slot went quiet.
• wal_status: anything other than reserved means trouble. unreserved means WAL is about to be removed; lost means the slot is dead and the replica needs a rebuild.
• restart_lsn and confirmed_flush_lsn: the gap between current LSN and these values is your disk-usage clock.
• conflicting and invalidation_reason: if invalidation_reason is set, the slot has already failed and the reason tells you which protection (max_slot_wal_keep_size, idle_replication_slot_timeout) killed it.

One short query covers a morning health check:

SELECT slot_name, active, inactive_since, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots WHERE slot_type = 'logical';

Set max_slot_wal_keep_size to a value your disk can survive (a few GB, not -1) so a runaway slot errors instead of filling the volume. Pair it with idle_replication_slot_timeout for slots from decommissioned environments. Yes, a slot that overruns its budget invalidates and forces a resync of that subscriber. That is the correct trade. An invalidated slot is a bad afternoon. A full disk is a bad week.

WAL retention pressure is only one flavor of the resource problem. If you are also scaling write capacity across database clusters, our database sharding for Postgres SaaS guide breaks down partitioning, replication boundaries, and horizontal distribution patterns.

Schema changes: the quiet killer

Logical replication does not replicate DDL. Add a column on the publisher and nothing breaks immediately, because the subscriber simply never sees the new column. Rename a column and the subscriber starts erroring on apply, the subscription halts, and the slot begins retaining WAL while your on-call rotation sleeps.

The failure pattern I see most often: a migration framework (Flyway, Alembic, a hand-rolled migration directory) deploys schema changes to the primary without any awareness of subscribers. The team finds out at 9 a.m. from a disk alert.

The playbook that works:

1. Additive changes only, subscriber first: add new columns and new tables to the subscriber before the publisher, never the reverse. Dropping is the dangerous direction.
2. Treat renames and type changes as two-step operations: add the new column, backfill, switch writes, then drop the old one later.
3. Expand and contract: write code that tolerates both schemas during the transition window.
4. Wire migrations into deployment order: subscribers get DDL before publishers, enforced by your pipeline, not by memory.
5. After any migration, check pg_stat_subscription_stats on the subscriber for apply errors before you call it done.

Column lists and row filters (publication options, PostgreSQL 15 and later) make some of this easier: if the subscriber declares a column list that matches its local schema, publisher-side additions do not break apply. Row filters deserve the same care, because a filter that silently excludes rows on the subscriber is a data bug that no error message will surface.

What happens when the primary fails

Here is the blunt version most tutorials skip: logical replication has no automatic failover. If the publisher dies, your subscriptions stop, full stop. Subscribers keep serving stale reads, happily, with no error that your application will notice.

Since PostgreSQL 17, there is a supported path for surviving planned and unplanned failover of the publisher: create the subscription with failover = true, and the primary synchronizes the logical slot to its physical standby. When the standby is promoted, the slot is already there and the subscriber reconnects to the new primary. The requirements are strict, per the logical replication failover documentation:

• You need a physical standby of the publisher anyway.
• The slot sync is asynchronous. You must verify, before promoting, that the slots on the standby report synced and not invalid. The docs provide verification queries for exactly this.
• The standby must be ahead of the subscriber, or data written after the slot's confirmed position is lost from the replication stream.

Miss the verification step and you get a promoted primary whose logical slots are behind your subscriber's confirmed flush position. That gap is silently dropped data. Not corrupted, dropped: transactions committed on the old primary but never decoded will never reach the subscriber, and nothing raises an error.

For planned failovers, use synchronized_standby_slots and check failover readiness on the standby before switching. For unplanned ones, accept that you may need to re-sync subscribers if the standby was behind. And test this. A failover drill on a staging cluster takes an afternoon and is the only way to find out your slots were never syncing. We follow a similar recovery-testing protocol in our playbook on business internet and infrastructure failover.

What most guides get wrong

Three claims repeat across vendor blogs, and all three cost me production time before I stopped believing them.

First: "logical replication is simple streaming with a filter." No. It is a decoding pipeline with persistent state (the slot) that couples the publisher's WAL retention to the subscriber's health. The filter is the easy part. The slot is the product.

Second: "just monitor lag." Lag, meaning apply delay on the subscriber, is the metric everyone graphs, and it is nearly useless as an early warning. Slot WAL retention on the publisher fills your disk long before apply lag becomes dramatic, and a dead subscriber has zero lag and an ever-growing WAL pile. Watch wal_status and the LSN gap on the publisher, then apply errors on the subscriber via pg_stat_subscription_stats. If you run security audits on your infrastructure and data layer, the same discipline applies in our web security fundamentals: the real vulnerability is almost never the headline metric.

Third: "failover = true fixes HA." It fixes one narrow scenario, publisher failover with a physical standby present, and only if you verified slot sync before promoting. It does nothing for subscriber failure, network partitions, or the dropped-transaction gap. Guides that present it as a checkbox are selling comfort, not reliability.

Operator's checklist

Run this daily, automate what you can, page on the starred items:

• [ ] * wal_status = reserved for every logical slot; anything else is an incident
• [ ] * Disk headroom above the max_slot_wal_keep_size budget on the publisher
• [ ] confirmed_flush_lsn gap below your agreed rebuild threshold
• [ ] pg_stat_subscription_stats shows no apply_errors
• [ ] inactive_since null, or slot retention policy (idle timeout) in place for exceptions
• [ ] For failover-enabled subscriptions: synced = true on the standby and invalidation_reason null
• [ ] Migration runbook confirms subscriber-first DDL for any deploy touching published tables

Conclusion

Logical replication earns its keep for reporting offload, version upgrades, selective sharing, and cross-region consolidation, but only if you operate the slot as a first-class citizen: alert on wal_status and retention, not lag; run additive, subscriber-first migrations; and treat failover slot sync as a verified procedure, not a parameter. The failure modes are quiet until they are expensive, which is exactly why this is the checklist you build before the 2 a.m. page, not after.