Logical Replication: Table Sync Failure

Scenario

A new payments table is added to a logical replication publication. The subscriber never receives data from it. pg_stat_subscription shows no sync worker for that table. Investigation reveals payments has no primary key — UPDATE and DELETE statements on the publisher cannot be replicated.

How to Identify

Conditions:

• New table not appearing on subscriber after REFRESH PUBLICATION
• pg_stat_subscription_stats shows sync errors
• Subscriber PostgreSQL logs contain constraint or schema errors during sync
• Table missing primary key causes UPDATE/DELETE replication to fail

Analysis Steps

-- On PUBLISHER:
-- 1. Check what tables are in the publication
SELECT pubname, schemaname, tablename
FROM   pg_publication_tables
WHERE  pubname = 'my_publication';

-- 2. Check if table has primary key or replica identity
SELECT c.relname,
       CASE i.indisprimary WHEN true THEN 'PRIMARY KEY'
            ELSE 'NO PRIMARY KEY' END AS pk_status,
       c.relreplident  -- 'd'=default(pk), 'f'=full, 'n'=nothing, 'i'=index
FROM   pg_class c
LEFT JOIN pg_index i ON i.indrelid = c.oid AND i.indisprimary
WHERE  c.relname = 'payments';

-- On SUBSCRIBER:
-- 3. Check subscription worker status
SELECT subname, pid, relid::regclass, received_lsn, last_msg_send_time
FROM   pg_stat_subscription;

-- 4. Check for sync errors
SELECT subname, relid::regclass, last_error_msg, last_error_time
FROM   pg_stat_subscription_stats;

Pitfalls

• Tables without a primary key can receive INSERT replication but cannot replicate UPDATE or DELETE — PostgreSQL cannot identify which row to update without a unique identifier.
• Schema changes (ADD COLUMN, ALTER COLUMN) on the publisher are not automatically replicated — DDL must be applied manually to the subscriber before or after the publisher change.
• REFRESH PUBLICATION is required on the subscriber every time a new table is added to the publication.
• TRUNCATE replication requires PostgreSQL 11+.

Resolution Approach

Add a primary key to tables that need UPDATE/DELETE replication. If adding a primary key is not possible, use REPLICA IDENTITY FULL as a fallback (replicates full row for identity, less efficient). Then REFRESH PUBLICATION on the subscriber.