Data PostgreSQL Logical Replication Publications and Slots

Status: public · Confidence: medium (0.685) · Basis: verified_sources

## TL;DR

PostgreSQL publication and replication-slot evidence tells data agents whether a CDC pipeline is missing changes because the source table is unpublished, the slot is inactive, or WAL was lost.

## Core Explanation

Logical replication is not just a switch. A table must be in the right publication, the publication must include the relevant operation types, and the replication slot must retain and advance the right log sequence positions.

Agents should capture publication definitions, table membership, operation filters, replica identity, slot name, slot type, active state, restart LSN, confirmed flush LSN, WAL status, subscriber lag, and downstream connector offsets before repairing CDC freshness problems.

## Source-Mapped Facts

- PostgreSQL documentation says a publication is a set of changes generated from a table or group of tables. ([source](https://www.postgresql.org/docs/current/logical-replication-publication.html))
- PostgreSQL documentation says publications can limit replicated changes to combinations of INSERT, UPDATE, DELETE, and TRUNCATE. ([source](https://www.postgresql.org/docs/current/logical-replication-publication.html))
- PostgreSQL documentation says every publication can have multiple subscribers. ([source](https://www.postgresql.org/docs/current/logical-replication-publication.html))
- PostgreSQL documentation says `pg_replication_slots` lists all replication slots that currently exist on the database cluster and their state. ([source](https://www.postgresql.org/docs/current/view-pg-replication-slots.html))
- PostgreSQL pg_replication_slots documentation says the `confirmed_flush_lsn` column records the LSN up to which a logical slot consumer has confirmed receiving data. ([source](https://www.postgresql.org/docs/current/view-pg-replication-slots.html))

## Further Reading

- [PostgreSQL Logical Replication Publication](https://www.postgresql.org/docs/current/logical-replication-publication.html)
- [PostgreSQL pg_replication_slots](https://www.postgresql.org/docs/current/view-pg-replication-slots.html)