Publications & Subscriptions

Publications define what changes leave the publisher; subscriptions connect a subscriber to one or more publications and control initial copy behavior.

Recipe

Publish a subset of columns with row filter (PostgreSQL 18).

-- Column list + row filter on publisher
CREATE PUBLICATION events_pub FOR TABLE events (id, tenant_id, payload, created_at)
  WHERE (tenant_id = 42);
 
CREATE SUBSCRIPTION events_sub
  CONNECTION 'host=primary dbname=appdb user=repl password=secret'
  PUBLICATION events_pub;

When to reach for this: Multi-tenant fan-out, partial table sync, or hiding sensitive columns from a downstream store.

Working Example

Multiple publications, one subscription, and schema prep on subscriber:

-- Publisher
CREATE PUBLICATION core_pub FOR TABLE customers, orders;
CREATE PUBLICATION audit_pub FOR TABLE audit_log;
 
-- Subscriber schema must exist before subscription applies DML
CREATE TABLE customers (LIKE public.customers INCLUDING ALL);
CREATE TABLE orders (LIKE public.orders INCLUDING ALL);
CREATE TABLE audit_log (LIKE public.audit_log INCLUDING ALL);
 
CREATE SUBSCRIPTION warehouse_sub
  CONNECTION 'host=primary.db.internal dbname=appdb user=repl_user password=secret sslmode=verify-full'
  PUBLICATION core_pub, audit_pub
  WITH (copy_data = true, streaming = on);

-- Add table to existing publication
ALTER PUBLICATION core_pub ADD TABLE order_items;
 
-- Subscriber picks up new table after REFRESH (PostgreSQL 18+)
ALTER SUBSCRIPTION warehouse_sub REFRESH PUBLICATION;

What this demonstrates:

• Publications can target multiple tables; subscriptions can consume multiple publications.
• Subscriber tables must exist with compatible columns before apply starts.
• ALTER PUBLICATION ... ADD TABLE requires REFRESH PUBLICATION on subscriber.

Deep Dive

Publication Options

Subscription Parameters

Managing Lifecycle

-- Pause apply
ALTER SUBSCRIPTION warehouse_sub DISABLE;
ALTER SUBSCRIPTION warehouse_sub SET (enabled = false);
 
-- Drop cleanly (drops slot on publisher if owned)
DROP SUBSCRIPTION warehouse_sub;

Gotchas

• Row filter not immutable - Publication creation fails or behavior undefined. Fix: Use stable expressions only; no volatile functions.
• Column list mismatch - Apply errors if subscriber column types differ. Fix: LIKE ... INCLUDING ALL from publisher schema dump.
• FOR ALL TABLES surprises - New sensitive table auto-replicates. Fix: Prefer explicit table lists in regulated environments.
• Forgot REFRESH after ADD TABLE - New table never copies. Fix: ALTER SUBSCRIPTION ... REFRESH PUBLICATION and verify pg_subscription_rel.
• DROP SUBSCRIPTION without connect - Orphan logical slot on publisher. Fix: Drop from publisher: pg_drop_replication_slot if needed.

Alternatives

FAQs

Related

• Logical Replication Basics - architecture
• Logical vs Physical - when to use logical
• Upgrade with Logical Replication - cutover pattern
• Logical Replication Best Practices - DDL pitfalls

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.