PostgreSQL Logical Replication: Set Up, Monitor and Fix Slot Errors

PostgreSQL logical replication sends row-level changes from a publication on one server to a subscription on another: set wal_level = logical on the publisher, create a role with the REPLICATION attribute and SELECT on the tables, run CREATE PUBLICATION, copy the schema to the subscriber, and run CREATE SUBSCRIPTION there to copy existing rows and stream new changes. Most production problems come from three places: tables without a replica identity, replication slots that hold WAL after a subscriber stops, and conflicts that stop the apply worker. This guide sets up replication on PostgreSQL 18 and shows how to detect and fix each of those.

Who this is for and what you will have at the end

This is for DBAs and platform engineers who need to replicate a subset of tables between PostgreSQL servers, feed a reporting database, or migrate to a new major version with minimal downtime. The commands use PostgreSQL 18 syntax and defaults; most of them also work on 16 and 17, and the differences are called out.

At the end you will have:

• A publisher and subscriber configured with correct server settings and a least-privilege replication role.
• A publication and subscription replicating your tables, with the initial copy verified.
• Monitoring queries for slots, apply workers and conflicts.
• A troubleshooting playbook for the errors you are most likely to see.

If you are using logical replication to move to PostgreSQL 18, the pg_upgrade runbook explains the in-place alternative, and the Patroni high availability guide covers physical replication for failover, which logical replication does not replace.

How logical replication works

• The publisher decodes WAL into row changes using the pgoutput plugin and a logical replication slot. The slot remembers how far the subscriber has confirmed, and the publisher keeps WAL from that point.
• The subscriber runs a leader apply worker per subscription, table synchronization workers for the initial copy, and, when streaming = parallel, parallel apply workers for large in-progress transactions. Progress is tracked in a replication origin on the subscriber.
• Each subscribed table moves through states shown in pg_subscription_rel.srsubstate: i initialize, d data is being copied, f finished table copy, s synchronized, r ready (normal replication).

What is not replicated matters as much as what is. DDL is not replicated. Sequence values are not replicated (the column values are, but the sequence on the subscriber stays at its start value). Large objects are not replicated. Only tables, including partitioned tables, can be published; views, materialized views and foreign tables cannot.

Prerequisites

• Network access from the subscriber to the publisher on the PostgreSQL port.
• Superuser or equivalent access on both servers for the configuration changes.
• A maintenance window to restart the publisher if wal_level is not already logical.
• Free disk space on the publisher for WAL retained during the initial copy.
• Tables with a primary key, or a plan for those without one (Step 3).

Step 1: Configure the publisher

wal_level, max_replication_slots and max_wal_senders can only be set at server start, so change them together and restart once.

# postgresql.conf on the publisher
wal_level = logical
max_replication_slots = 10        # >= subscriptions + reserve for table sync
max_wal_senders = 12              # >= max_replication_slots + physical replicas
max_slot_wal_keep_size = 100GB    # cap WAL a slot may retain; -1 (default) is unlimited

max_slot_wal_keep_size is optional but strongly recommended. With the default of -1 a stuck slot can retain WAL until the disk fills. With a limit, the slot is invalidated instead, which breaks that subscription but protects the primary. Size it to the outage you are willing to ride out, within your free space. PostgreSQL 18 also adds idle_replication_slot_timeout, which invalidates slots that stay inactive longer than the configured duration; it is disabled (0) by default.

Allow the replication role to connect to the published database in pg_hba.conf. Logical replication connections name a database, so the replication keyword (which only matches physical replication) does not apply:

# TYPE  DATABASE  USER       ADDRESS          METHOD
host    sales     repl_user  10.20.0.0/24     scram-sha-256

Restart the publisher and confirm:

SHOW wal_level;

Step 2: Create the replication role

The role used for the connection needs the REPLICATION and LOGIN attributes and SELECT on published tables for the initial copy.

CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'use-a-strong-secret';
GRANT USAGE ON SCHEMA public TO repl_user;
GRANT SELECT ON public.orders, public.order_lines, public.customers TO repl_user;

If the role is not a superuser and does not have BYPASSRLS, row security policies on the publisher can run during replication. If you don't trust every table owner, add options=-crow_security=off to the subscription connection string so replication stops instead of executing a policy.

Step 3: Check replica identity

To replicate UPDATE and DELETE, every published table needs a replica identity. The default is the primary key. Find tables that have none:

SELECT c.oid::regclass AS table_name, c.relreplident
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND c.relreplident IN ('d', 'n')
  AND NOT EXISTS (
        SELECT 1 FROM pg_index i
        WHERE i.indrelid = c.oid AND i.indisprimary);

For each table returned, either add a primary key, point the replica identity at a suitable unique index, or as a last resort use FULL:

ALTER TABLE public.audit_log REPLICA IDENTITY USING INDEX audit_log_event_id_key;
ALTER TABLE public.legacy_import REPLICA IDENTITY FULL;

With FULL, the whole row is the key. The subscriber can use a btree or hash index whose leftmost column is a plain column to find rows; without one, every update or delete scans the table. A table without a usable replica identity can still be published, but UPDATE and DELETE on it will then fail on the publisher.

Step 4: Create the publication

The role creating the publication needs CREATE on the database and ownership of each table. Publishing all tables, or all tables in a schema, requires a superuser.

-- Specific tables
CREATE PUBLICATION sales_pub FOR TABLE public.orders, public.order_lines, public.customers;
 
-- Only some columns and rows
CREATE PUBLICATION eu_customers_pub
  FOR TABLE public.customers (customer_id, name, country) WHERE (country = 'DE');
 
-- Everything in a schema, including tables created later
CREATE PUBLICATION reporting_pub FOR TABLES IN SCHEMA reporting;

Column lists must include the replica identity columns for updates and deletes to be published, and row filters may only reference replica identity columns for the same reason. For partitioned tables, WITH (publish_via_partition_root = true) publishes changes using the root table's identity, which lets the subscriber use a different partition layout or a plain table. In PostgreSQL 18, stored generated columns can be replicated by listing them in a column list or setting publish_generated_columns = stored.

There are no privileges on publications: any subscription that can connect can read any publication in the database. Don't rely on a column list or row filter to hide data if another publication exposes the same table.

Step 5: Create the schema on the subscriber

Logical replication does not create tables. Copy the definitions:

pg_dump --schema-only --table='public.orders' --table='public.order_lines' \
  --table='public.customers' -h pg-pub01.contoso.com -U postgres sales \
  | psql -h pg-sub01.contoso.com -U postgres sales

The subscriber's tables don't have to be identical, but each published column must exist on the subscriber with a compatible type, and if the publisher uses a replica identity other than FULL, the subscriber needs one with the same or fewer columns.

Step 6: Configure the subscriber

In PostgreSQL 18, the number of subscriptions a server can hold is limited by max_active_replication_origins (previously max_replication_slots on the subscriber).

# postgresql.conf on the subscriber
max_active_replication_origins = 10            # >= subscriptions + reserve for table sync
max_logical_replication_workers = 8            # apply + table sync + parallel apply workers
max_worker_processes = 16                      # >= max_logical_replication_workers + 1, plus other users
max_sync_workers_per_subscription = 2          # parallelism of the initial copy
max_parallel_apply_workers_per_subscription = 2

max_active_replication_origins and max_logical_replication_workers can only be set at server start, so restart the subscriber after changing them. Their defaults are 10 and 4; the two per-subscription settings default to 2 and can be reloaded.

Step 7: Create the subscription

The subscription owner needs the privileges of pg_create_subscription and CREATE on the database.

CREATE SUBSCRIPTION sales_sub
  CONNECTION 'host=pg-pub01.contoso.com port=5432 dbname=sales user=repl_user password=use-a-strong-secret'
  PUBLICATION sales_pub
  WITH (disable_on_error = true);

Notes on the options:

• By default the command creates a slot named after the subscription, copies existing data (copy_data = true) and starts replicating. It can't run inside a transaction block when it creates a slot.
• In PostgreSQL 18 the default for streaming changed from off to parallel, so large in-progress transactions are applied by parallel apply workers when available.
• disable_on_error = true stops the subscription on the first apply error instead of retrying in a loop, which keeps the log readable and makes the failure obvious.
• failover = true lets the slot be synced to physical standbys of the publisher so replication can continue after a publisher failover.
• password_required defaults to true: for a subscription not owned by a superuser, the connection must use password authentication with the password in the connection string. Use a dedicated replication password and rotate it like any other secret.

Verification

On the subscriber, watch the initial copy finish. Every table should reach r:

SELECT s.subname, sr.srrelid::regclass AS table_name, sr.srsubstate
FROM pg_subscription_rel sr
JOIN pg_subscription s ON s.oid = sr.srsubid
ORDER BY 1, 2;
 
SELECT subname, worker_type, pid, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

On the publisher, check the slot and how much WAL it holds:

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

wal_status is reserved while retained WAL is within max_wal_size, extended when it is beyond that but still kept, unreserved when some required WAL will be removed at the next checkpoint, and lost when the slot is no longer usable. Then insert a test row on the publisher and confirm it arrives, and compare row counts for each table.

For conflicts and errors, PostgreSQL 18 records counters per subscription:

SELECT subname, apply_error_count, sync_error_count,
       confl_insert_exists, confl_update_missing, confl_delete_missing
FROM pg_stat_subscription_stats;

Alert on any slot that is inactive for longer than your tolerance, on wal_status other than reserved, and on increasing error counters.

Day-two operations

Adding tables. Add the table to the subscriber schema first, then to the publication, then refresh the subscription. The refresh copies the new table's existing rows.

-- Publisher
ALTER PUBLICATION sales_pub ADD TABLE public.invoices;
-- Subscriber
ALTER SUBSCRIPTION sales_sub REFRESH PUBLICATION;

Schema changes. Apply additive changes, such as a new nullable column, on the subscriber before the publisher. If changes arrive for a column the subscriber doesn't have, replication errors until you add it.

Sequences before cutover. If you will promote the subscriber, set each sequence above the highest value in use, for example SELECT setval('public.orders_order_id_seq', (SELECT max(order_id) FROM public.orders));, or copy sequence values from the publisher with pg_dump.

Troubleshooting

Conflict: duplicate key on the subscriber

The subscriber log shows an error like this, and the apply worker stops or retries:

ERROR:  conflict detected on relation "public.test": conflict=insert_exists
DETAIL:  Key already exists in unique index "t_pkey", which was modified locally in transaction 740 at 2024-06-26 10:47:04.727375+08.
Key (c)=(1); existing local row (1, 'local'); remote row (1, 'remote').
CONTEXT:  processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378

Someone wrote to the subscriber, or the row was copied twice. The preferred fix is to correct the data on the subscriber (delete or update the local row) so the remote change applies. If you must discard the remote transaction, skip it using the finish LSN from the CONTEXT line:

ALTER SUBSCRIPTION sales_sub SKIP (lsn = '0/14C0378');
ALTER SUBSCRIPTION sales_sub ENABLE;   -- if disable_on_error stopped it

Skipping discards every change in that transaction, not only the conflicting row. With streaming = parallel the finish LSN might not be logged; temporarily set streaming to on or off and let the conflict recur to get it. update_missing and delete_missing conflicts are logged and skipped automatically; enable track_commit_timestamp on the subscriber if you need origin details in conflict logs.

WAL keeps growing on the publisher

An inactive slot is holding WAL. Find it with the slot query above (active = false, a large retained_wal). If its subscriber is gone for good, drop the slot:

SELECT pg_drop_replication_slot('old_sub');

If the subscriber still exists and might reconnect, fix the subscriber instead. Set max_slot_wal_keep_size, and on PostgreSQL 18 consider idle_replication_slot_timeout, so this can't fill the disk again. Old slots also hold back catalog_xmin, which stops vacuum from cleaning system catalogs, so they cause bloat as well as disk growth; see the autovacuum tuning guide.

The slot was invalidated

wal_status shows lost and invalidation_reason shows wal_removed (WAL exceeded max_slot_wal_keep_size) or idle_timeout. The subscription can't resume from that slot. Recreate it: on the subscriber, disable the subscription, detach and drop it, truncate the target tables or drop and re-create them, and create the subscription again with copy_data = true. Drop the invalidated slot on the publisher if it still exists.

UPDATE or DELETE fails on the publisher

The table is in a publication that publishes updates and deletes but has no usable replica identity: DEFAULT without a primary key, NOTHING, or USING INDEX on an index that was dropped. Fix it with a primary key, REPLICA IDENTITY USING INDEX, or REPLICA IDENTITY FULL (Step 3).

DROP SUBSCRIPTION fails

DROP SUBSCRIPTION connects to the publisher to drop the slot and fails if it can't. If the publisher is unreachable:

ALTER SUBSCRIPTION sales_sub DISABLE;
ALTER SUBSCRIPTION sales_sub SET (slot_name = NONE);
DROP SUBSCRIPTION sales_sub;

Then drop the leftover slot (and any table synchronization slots) on the publisher when it is reachable again, otherwise they keep reserving WAL.

Tables stay in state i or d

The initial copy is waiting for workers. Increase max_logical_replication_workers (restart required) and, if needed, max_worker_processes on the subscriber, and check the subscriber log for permission errors: the replication role needs SELECT on each table, and the subscription owner must be able to SET ROLE to each table owner unless run_as_owner = true.

TRUNCATE fails on the subscriber

Truncate is replicated for the group of tables truncated on the publisher. If a subscriber table has a foreign key to a table that is not in the same subscription, the truncate fails. Keep foreign-key-linked tables in the same publication, or don't publish truncate.

Checklist

• wal_level = logical, slot and sender limits set, publisher restarted.
• max_slot_wal_keep_size set to a value your disk can absorb.
• Replication role with REPLICATION, LOGIN and SELECT; pg_hba.conf entry for the database.
• Every published table has a primary key, a replica identity index, or FULL with a usable index.
• Publication created; schema copied to the subscriber.
• Subscriber origins and worker limits set; subscription created with disable_on_error.
• All tables in state r; test row verified; row counts compared.
• Alerts on inactive slots, wal_status, and pg_stat_subscription_stats counters.
• Runbook entries for adding tables, schema changes, sequences and conflict handling.

References

• Logical replication configuration settings (PostgreSQL 18)
• Publication and replica identity (PostgreSQL 18)
• Logical replication conflicts (PostgreSQL 18)
• Logical replication restrictions (PostgreSQL 18)
• Logical replication security (PostgreSQL 18)
• Upgrading logical replication clusters (PostgreSQL 18)
• CREATE PUBLICATION
• ALTER PUBLICATION
• CREATE SUBSCRIPTION
• ALTER SUBSCRIPTION
• DROP SUBSCRIPTION
• pg_replication_slots
• pg_subscription_rel
• Replication server settings (PostgreSQL 18)
• The pg_hba.conf file (PostgreSQL 18)
• System administration functions (PostgreSQL 18)
• Cumulative statistics system (PostgreSQL 18)
• PostgreSQL 18 release notes