PostgreSQL Logical Replication: Setup, Use Cases, and Limitations

PostgreSQL logical replication publishes row changes from selected tables and applies them through subscriptions. It is useful for selective copies, data distribution, and major-version migrations because publisher and subscriber do not need byte-identical storage. That flexibility has explicit boundaries: schema DDL, sequence state, and large objects are not copied by ordinary built-in logical replication, and writes on the subscriber can create conflicts. A reliable deployment assigns owners to those gaps before the first subscription is created.

Choose logical replication for the right job

Logical replication is not a backup, a general event bus, or automatic bidirectional conflict resolution. It can reproduce destructive DML, a retained slot can consume storage, and an independently written subscriber can stop apply. Use physical replication when you need a close binary standby, and use a change-data-capture platform when downstream event semantics, transformations, or many consumers are the primary requirement. The PostgreSQL replication comparison covers those choices.

Prepare the publisher and network

Set wal_level=logical on the publisher and budget max_wal_senders and max_replication_slots for subscriptions, table synchronization workers, maintenance, and headroom. These are server settings; changing wal_level requires a restart. A logical slot can retain WAL until the consumer confirms progress, so storage monitoring is part of capacity planning. max_slot_wal_keep_size can bound retained WAL, but exceeding it can invalidate a lagging slot and force recovery work rather than making lag harmless.

Create a dedicated login with REPLICATION, grant CONNECT to the actual published database, and grant USAGE on schemas plus SELECT on published tables for initial synchronization. Arrange default privileges or an explicit deployment step for future tables. Built-in logical replication connects to a real database; do not use the physical-replication pseudo-database in pg_hba.conf. Restrict the source network and require certificate verification and a supported authentication method.

# pg_hba.conf on the publisher
hostssl  appdb  app_logical_repl  10.20.30.0/24  scram-sha-256

The subscription connection string is stored in PostgreSQL catalogs and may appear in administrative workflows. Generate its credential in an approved secret system, run setup through a protected channel, restrict catalog and backup access, and rotate after bootstrap if policy requires. A sample with sslmode=disable is not suitable for production.

Create matching tables before the subscription

Logical replication does not create schemas, tables, indexes, constraints, roles, extensions, or functions. Apply a reviewed schema to the subscriber first. Target tables can contain additional columns when those columns have defaults or otherwise accept incoming rows, but every published column needs a compatible target. Keep table names and schemas aligned unless a separately tested transformation layer is involved.

A published table needs a replica identity for UPDATE and DELETE. The primary key is the default. A qualifying unique index can be selected with REPLICA IDENTITY USING INDEX. REPLICA IDENTITY FULL is a fallback that sends the old row and can make apply searches expensive; it is not a substitute for a stable key without measurement.

-- Run as an owner on the publisher.
CREATE PUBLICATION app_publication
  FOR TABLE public.customers, public.orders
  WITH (publish = 'insert, update, delete');

Excluding TRUNCATE here is deliberate when a subscriber must never receive an unfiltered whole-table truncate. The publication's publish setting does not constrain the initial table copy. Verify existing data separately before assuming an insert-only publication creates an insert-only initial state.

Create and observe the subscription

On the subscriber, create the subscription with an owner that has the required target-table privileges. By default, CREATE SUBSCRIPTION creates a remote slot and starts an initial copy. The transaction that creates it can contact the publisher, so follow the documented restrictions when running it in a transaction or when creating a slot separately.

CREATE SUBSCRIPTION app_subscription
CONNECTION 'host=pg-old.internal port=5432 dbname=appdb user=app_logical_repl sslmode=verify-full sslrootcert=/etc/postgresql/ca.pem'
PUBLICATION app_publication
WITH (copy_data = true, create_slot = true, enabled = true);

Do not treat successful creation as completed synchronization. On the subscriber, pg_stat_subscription reports the apply leader and table-synchronization workers, received locations, and message times. pg_subscription_rel.srsubstate holds per-table states: initialize, copying, finished copy, synchronized, and ready. There is no generic substatus column in pg_subscription. On the publisher, inspect pg_replication_slots and WAL sender activity. Alert on inactive slots, growing WAL retained from restart_lsn, apply errors, a table that does not reach ready, and business-data freshness.

-- Subscriber: table synchronization state.
SELECT r.srrelid::regclass AS table_name, r.srsubstate, r.srsublsn
FROM pg_subscription_rel AS r
ORDER BY 1;

-- Publisher: slot activity and retained WAL.
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
WHERE slot_type = 'logical';

WAL-byte distance is backlog, not a time prediction. Pair it with transaction rate, apply state, errors, disk headroom, and a heartbeat row whose commit time the application can observe on both sides. latest_end_time can remain unchanged on an idle system; distinguish idle and current from disconnected and stale.

Use row filters and column lists with version checks

PostgreSQL 15 and later support publication row filters and column lists. Qualify guidance by the oldest supported publisher and subscriber: an older subscriber can handle initial synchronization differently. A row filter is attached to each named table. For publications that send UPDATE or DELETE, every filter column must be covered by replica identity, and a column list must include the replica-identity columns. Filters do not affect TRUNCATE.

CREATE UNIQUE INDEX orders_tenant_identity
  ON public.orders (tenant_id, id);
ALTER TABLE public.orders
  REPLICA IDENTITY USING INDEX orders_tenant_identity;

CREATE PUBLICATION tenant_42_publication
  FOR TABLE public.orders (tenant_id, id, status, updated_at)
  WHERE (tenant_id = 42)
  WITH (publish = 'insert, update, delete');

The identity-index columns must meet PostgreSQL's eligibility rules. Test updates that move a row into and out of the filter: PostgreSQL can transform those boundary changes into an insert or delete for the subscriber. If the same table arrives through several publications, filters can combine with OR semantics, and an unfiltered publication defeats the intended subset. Column lists reduce normal published columns but are not a security boundary against a malicious subscriber. Apply authorization and data-separation controls at the publisher.

Partitioned tables require an explicit choice about publish_via_partition_root, which controls whether root or leaf identity, filters, and column lists are used. Test the exact publisher and subscriber partition layouts. Adding a table to a publication does not automatically make an existing subscription aware of it; run ALTER SUBSCRIPTION ... REFRESH PUBLICATION through the change process and observe the new table's copy state.

Own unsupported and version-specific objects

• DDL: table and schema changes are not replicated. Deploy compatible DDL to both sides in an order that old and new application versions can tolerate.
• Sequences: values written into serial or identity columns replicate as row data, but the sequence object does not advance with them. Synchronize sequence state only after source writes are fenced for cutover.
• Large objects: PostgreSQL large objects are not replicated. Move them with a separate verified process or redesign storage.
• Generated columns: PostgreSQL 18 adds opt-in publication of stored generated columns through publication settings or column lists. Releases before 18 do not publish generated columns. A generated target can calculate its own value when the published column is omitted; test cross-version behavior.
• Privileges and row-level security: apply runs under subscription ownership rules. Missing target privileges or applicable row-level-security restrictions can stop replication.

Extensions and custom types need compatible definitions on both sides before table copy. Do not assume logical replication upgrades an extension's binary format or background-worker state. Validate extension support on the target major release first.

Prevent and resolve conflicts

Keep subscriber tables read-only to ordinary application roles unless local writes are a consciously designed feature. A duplicate unique key or other constraint violation can stop apply. Missing target rows for incoming updates or deletes and origin differences are also observable conflict classes in current PostgreSQL. Monitor pg_stat_subscription_stats and subscriber logs, which include the affected relation and remote transaction context where available.

When apply stops, preserve the logs and identify the exact transaction, local row, remote row, and business owner. Prefer correcting unauthorized local data or the target schema so the remote transaction can apply. ALTER SUBSCRIPTION ... SKIP can skip a known remote transaction at its finish LSN, but skipping discards every change in that transaction; use it only after reconciliation and approval. Do not advance a replication origin from a guessed LSN. After recovery, reconcile counts, keys, and domain invariants, then document why prevention failed.

Major-version cutover runbook

1. Prove target compatibility. Install the target PostgreSQL release and approved extensions, apply the schema, and pass application and restore tests.
2. Create the subscription. Start with named tables, observe each initial copy, and confirm the slot and worker counts stay within budget.
3. Keep DDL compatible. Freeze incompatible schema changes or deploy expand-and-contract changes to both clusters. Refresh publications for new tables deliberately.
4. Validate continuously. Compare row counts by stable partitions, sampled hashes of canonicalized columns, constraints, recent business records, and a heartbeat. Explain every difference.
5. Prepare routing and fencing. Lower routing time-to-live where relevant, preconfigure secrets and pools, pause migration jobs, and define how the old writer will reject writes.
6. Quiesce source writes. Stop application and background writes at an agreed boundary. Record the publisher WAL location and wait until the subscription has applied through it with no errors.
7. Synchronize sequences. While the source remains fenced, set each target sequence to a reviewed value at or above the source state and existing maximum. Test the next generated key without exposing a duplicate.
8. Cut over clients. Point the application to the target, drain stale pools, run read and idempotent write canaries, and verify only the target accepts writes.
9. Keep a controlled fallback. Retain the source read-only for the approved window. Once target-only writes begin, a rollback needs a separately designed reverse data path or reconciliation; changing DNS alone can lose new data.
10. Close safely. After acceptance, disable and remove subscriptions and slots in the documented order, confirm retained WAL falls, restore a resilient topology, and archive evidence.

This can reduce the write interruption to a short, measured cutover, but it should not be advertised as zero downtime until the complete application exercise proves that outcome. The PostgreSQL replication monitoring guide provides a deeper lag and slot checklist.

Official primary documentation

• PostgreSQL logical replication overview
• Publications and replica identity
• Subscriptions and replication slots
• Logical replication security and privileges
• Row-filter rules and limitations
• Publication column lists
• Logical replication restrictions
• Monitoring publications and subscriptions
• Conflict detection and response
• PostgreSQL 18 generated-column replication