Creating Publications

A publication is the declarative exposure boundary of a PostgreSQL logical replication topology, and defining it correctly is the first operational decision in the Logical Replication Setup & Management workflow — it fixes exactly which tables, columns, rows, and DML operations leave the primary before any replication slot or subscriber ever attaches. For database engineers, data platform teams, and DevOps operators, CREATE PUBLICATION is not a routine DDL statement; it sets the network egress ceiling, governs Write-Ahead Log (WAL) decoding cost, and dictates the consistency contract every downstream Change Data Capture (CDC) consumer must honor.

Get the publication wrong and the failure is rarely loud at creation time — it surfaces days later in production. FOR ALL TABLES silently enrolls every future CREATE TABLE into the stream, so an unrelated migration begins shipping a multi-terabyte audit table over the wire. A missing REPLICA IDENTITY lets INSERTs replicate cleanly while every UPDATE and DELETE aborts the apply worker. An overly broad row filter leaks tenant data across a boundary that was supposed to be enforced at the source. Because a publication is evaluated inside the decoding path on the publisher, each of these mistakes converts directly into WAL retention, replication lag, or a data-governance incident. This page is the reference for building publications that stay stable under production churn, versioned against PostgreSQL 14 through 17.

Prerequisites & Configuration Objects #

A publication cannot be created — or rather, cannot usefully stream — unless the server-level decoding surface is already in place. The single non-negotiable prerequisite is wal_level = logical, which requires a restart and roughly doubles per-row WAL volume for UPDATE/DELETE because the old-tuple identity is now logged. Provision the slot and sender ceilings at the same time so the first subscription does not fail with FATAL: too many replication slots.

-- Publisher server prerequisites (wal_level change requires a restart).
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;   -- one per subscriber + headroom
ALTER SYSTEM SET max_wal_senders   = 12;       -- >= slots, plus any physical standbys
-- Cap WAL a stalled slot can pin so one dead consumer cannot fill the disk (PG 13+).
ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';
SELECT pg_reload_conf();   -- wal_level still needs a full restart to take effect.

Publications are owned objects and creating one requires the CREATE privilege on the current database; the owner must additionally own every table added to the publication (or be a superuser). PostgreSQL 15 relaxed this for row-filtered and column-list publications but the ownership rule on FOR TABLE still holds. Provision a dedicated, least-privilege replication role rather than reusing an application superuser — the full privilege model is covered in security boundaries and permissions.

-- Dedicated replication role: can log in over the replication protocol,
-- reads the published tables, but owns nothing and cannot write.
CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD '…';
GRANT USAGE ON SCHEMA public TO repl;
GRANT SELECT ON public.orders, public.order_items TO repl;
-- PG 16+: CREATE SUBSCRIPTION on the subscriber can be delegated with the
-- pg_create_subscription predefined role instead of superuser.

Before writing any DDL, confirm every table you intend to publish can describe a changed row. A table with a primary key is ready; a table without one must be given an explicit REPLICA IDENTITY (see the implementation steps below) or its UPDATE/DELETE events will fail to replicate. The decoding mechanics that make all of this work — how committed WAL becomes a row-change stream — are documented in WAL stream mechanics.

Step-by-Step Implementation #

The following sequence builds a production publication from an empty exposure boundary to a slot-backed, identity-correct stream. Each step is idempotent-friendly and safe to run against a live primary.

1. Create a narrowly scoped publication. Enumerate tables explicitly. Prefer this over FOR ALL TABLES, which enrolls every current and future table, amplifies decoding cost, and makes each later migration an unplanned replication change.

-- Expose an explicit table set and only the DML you actually consume downstream.
CREATE PUBLICATION orders_pub
  FOR TABLE public.orders, public.order_items
  WITH (publish = 'insert,update,delete');   -- omit 'truncate' if subscribers differ in shape

2. Narrow the payload with column lists and row filters (PG 15+). Both are evaluated on the publisher, trading extra decoding CPU for reduced network egress — a good trade when the filter is selective. A column list also acts as a data-minimization control, keeping columns such as pii_ssn out of the stream entirely.

-- PG 15+: column list + WHERE row filter, applied at the source.
CREATE PUBLICATION eu_orders_pub
  FOR TABLE public.orders (id, customer_id, total, status)
    WHERE (region = 'EU');

Row filters may only reference columns covered by the table’s replica identity for UPDATE/DELETE; a filter on a non-identity column silently applies to INSERTs only. If a row is UPDATEd so it moves out of the filter, the change is transformed into a DELETE on the subscriber — plan consumer merge logic for that transition.

3. Set a deterministic replica identity on every published table. Logical replication needs to identify which row an UPDATE/DELETE touched. Tables with a primary key are covered by the default. Tables using natural keys, composite constraints, or no key at all must be configured explicitly.

-- Default: primary key columns are logged as the identity — nothing to do.
-- Natural/composite key on a UNIQUE, NOT NULL, non-partial index:
ALTER TABLE public.inventory REPLICA IDENTITY USING INDEX inventory_sku_key;
-- No usable key at all: log the entire old row (higher WAL cost, but correct).
ALTER TABLE public.event_log REPLICA IDENTITY FULL;

REPLICA IDENTITY FULL writes the complete pre-image of every changed row into WAL and forces the subscriber to match on all columns, which degrades to a sequential scan when the target lacks a matching index — acceptable for low-churn tables, expensive on hot ones.

4. Add a partitioned table with root-level publishing (PG 13+). By default a publication streams leaf partitions under their own names. publish_via_partition_root = true makes changes appear as the root table, which is what most subscribers and Debezium connectors expect.

CREATE PUBLICATION events_pub
  FOR TABLE public.events           -- partitioned root
  WITH (publish_via_partition_root = true);

5. Bind a slot by creating the subscription. A publication is inert on its own — it streams nothing until a slot advances against it. Creating a subscription implicitly creates the named replication slot on the publisher; for zero-downtime rollouts, pre-create the slot and attach with create_slot = false so a transient network partition during CREATE SUBSCRIPTION cannot orphan WAL.

-- On the subscriber. This creates slot "orders_sub" on the publisher and
-- kicks off the initial COPY, coordinated by the subscription sync procedure.
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=pub.internal port=5432 dbname=app user=repl sslmode=verify-full'
  PUBLICATION orders_pub
  WITH (copy_data = true, streaming = 'parallel', binary = true);  -- streaming=parallel: PG 16+

Coordinate that first COPY with the subscription sync procedures so consumers receive a consistent baseline before incremental changes apply.

6. Handle sequences explicitly. Logical replication does not stream sequence advances. Left unmanaged, a promoted subscriber issues primary-key values that collide with rows the old primary already emitted. Reconcile sequences as part of any cutover.

-- On the publisher: read the current high-water mark.
SELECT last_value FROM public.orders_id_seq;
-- On the subscriber during cutover: advance the local sequence past it.
SELECT setval('public.orders_id_seq', 9000000, true);

7. Evolve the publication without recreating it. ALTER PUBLICATION adds or drops tables online; the change takes effect on subscribers only after they run ALTER SUBSCRIPTION … REFRESH PUBLICATION.

ALTER PUBLICATION orders_pub ADD TABLE public.refunds;
-- Then, on each subscriber:
ALTER SUBSCRIPTION orders_sub REFRESH PUBLICATION WITH (copy_data = true);

Parameter Reference Table #

Diagnostic Queries #

Validate a publication against the catalogs before and after subscribers attach. The row filters and column lists you configured are only trustworthy if pg_publication_tables reports them as expected.

-- 1. What is actually published, including resolved row filters and column lists (PG 15+).
SELECT pubname, schemaname, tablename, attnames, rowfilter
FROM pg_publication_tables
WHERE pubname = 'orders_pub';

-- 2. Confirm each published table has a usable replica identity.
--    relreplident: d=default(PK), i=index, f=full, n=nothing.
SELECT c.relname, c.relreplident,
       (c.relreplident = 'n') AS unreplicatable_updates
FROM pg_class c
JOIN pg_publication_rel pr ON pr.prrelid = c.oid
JOIN pg_publication p ON p.oid = pr.prpubid
WHERE p.pubname = 'orders_pub';

-- 3. Slot health for the bound subscription. Alert when active=false persists
--    or the retained-WAL gap grows without bound.
SELECT slot_name, plugin, active,
       restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_name = 'orders_sub';

Threshold guidance: treat retained_wal above 1 GB, or active = false for longer than 60 s on a slot that should be live, as a paging condition — both mean the publisher is pinning WAL a consumer is not draining. To confirm the payload itself is correct, peek at raw decoded changes without consuming them:

-- 4. Inspect raw decoded output; confirms identity columns and filter behavior.
SELECT data
FROM pg_logical_slot_peek_changes('orders_sub', NULL, 20,
  'proto_version', '4', 'publication_names', 'orders_pub');

The broader set of streaming and lag metrics — pg_stat_replication.sent_lsn vs write_lsn, apply lag, and alert-rule templates — is wired up in asynchronous monitoring integration.

Failure Modes & Gotchas #

FOR ALL TABLES enrolls tables you never intended. Signature: WAL volume and egress jump after an unrelated migration; a large table you never meant to replicate appears in pg_publication_tables. Root cause: FOR ALL TABLES matches every current and future relation, including partitions and audit tables. Remediation: drop and recreate the publication with an explicit FOR TABLE list, then REFRESH PUBLICATION on subscribers. Prevention: never use FOR ALL TABLES outside throwaway prototypes.

UPDATE/DELETE fail while INSERTs replicate fine. Signature: publisher logs cannot update table … because it does not have a replica identity and publishes updates; the apply worker stalls. Root cause: a published table has no primary key and no explicit REPLICA IDENTITY. Remediation: ALTER TABLE … REPLICA IDENTITY USING INDEX <unique_index> or … REPLICA IDENTITY FULL. Validate with diagnostic query 2 before going live.

Row filter silently drops required rows or tombstones. Signature: subscriber is missing rows that clearly exist on the publisher; a delete never arrives. Root cause: a WHERE filter references a non-identity column (so it applies to INSERTs only), or a row updated out of the filter range was converted to a DELETE the consumer did not expect. Remediation: constrain filters to replica-identity columns and design consumer merge logic to treat filter-exit as a delete. Verify with pg_publication_tables.rowfilter and query 4.

Stalled slot exhausts the disk. Signature: pg_wal grows steadily; one slot shows a frozen restart_lsn and active = false. Root cause: the bound consumer (subscription, Debezium task, or Python worker) stopped acknowledging, so WAL past restart_lsn cannot be recycled. Remediation: restart the consumer, or SELECT pg_drop_replication_slot('orders_sub') and re-seed from a fresh snapshot. Prevention: max_slot_wal_keep_size, which trades a bounded re-seed for a full disk.

Sequence gaps and primary-key collisions after cutover. Signature: duplicate-key violations on the subscriber immediately after promotion. Root cause: logical replication never streamed sequence advances, so the promoted node reuses id values. Remediation: reconcile with setval() against the publisher high-water mark (step 6) as a mandatory cutover step, scripted into the runbook rather than performed by hand.

Frequently Asked Questions #

Integration Touchpoints #

A publication is the source-side half of a contract whose other half lives across several adjacent topics. The slot it binds is provisioned and recovered in initializing replication slots; the topology choices behind fan-out, cascading, and partial datasets are reasoned through in publication and subscription models, part of the broader logical replication architecture fundamentals.

Downstream, the publish set and durability you choose here define the data-loss window that tuning synchronous_commit for logical replication trades against throughput, and the initial baseline is driven by the subscription sync procedures. When the consumer is an event-streaming pipeline rather than a native subscriber, the same publication and slot are reused by the Debezium connector and its changes are fanned out through the Kafka event routing integration, where idempotency and offset management move from the SQL apply worker into the streaming layer.

Related guides #

• Initializing replication slots — pre-allocate durable WAL cursors so a publication has something to stream against.
• Subscription sync procedures — drive and recover the initial snapshot the publication feeds.
• Asynchronous monitoring integration — export slot, egress, and apply-lag metrics with SLO alerting.
• Tuning synchronous_commit for logical replication — set the durability boundary for the DML this publication emits.
• Publication and subscription models — the topology reasoning behind fan-out, cascading, and partial datasets.
• Logical Replication Setup & Management — the management layer this publication is the entry point to.