Logical Replication: Publication Missing Column Changes

Scenario

A developer adds a GENERATED ALWAYS AS (price * quantity) STORED column to the order_items table on the publisher. The subscriber schema is updated manually to add the column. Two days later, the analytics team reports that the total_price values on the subscriber don’t match the publisher — they’re recomputing locally from stale data. No replication errors were logged.

How to Identify

Conditions:

• Subscriber data differs from publisher with no error messages
• GENERATED ALWAYS AS (STORED) columns on subscriber recomputing from old base values
• ALTER TABLE ADD COLUMN applied to publisher but not refreshed on subscription
• pg_publication_tables shows column-level filtering in use

Analysis Steps

-- On PUBLISHER: check what columns are being published
SELECT pubname, schemaname, tablename, attnames
FROM   pg_publication_tables
WHERE  pubname = 'my_publication';
-- attnames = NULL means all columns; or lists specific columns

-- Check if GENERATED columns exist
SELECT attname, attgenerated
FROM   pg_attribute
WHERE  attrelid = 'order_items'::regclass
  AND  attnum > 0
  AND  NOT attisdropped;
-- attgenerated = 's' means STORED generated column

-- On SUBSCRIBER: check schema matches publisher
SELECT attname, atttypid::regtype
FROM   pg_attribute
WHERE  attrelid = 'order_items'::regclass
  AND  attnum > 0
  AND  NOT attisdropped;

Pitfalls

• GENERATED ALWAYS AS (expr) STORED columns are not replicated — they are regenerated locally on the subscriber using the subscriber’s copy of the expression. If the base columns have stale values, the generated column will be wrong.
• Logical replication does not replicate DDL — every ALTER TABLE must be manually applied to the subscriber in the correct order (subscriber first, then publisher for ADD COLUMN; publisher first, then subscriber for DROP COLUMN).
• Schema drift between publisher and subscriber is silent — no error, data is just wrong.
• REFRESH PUBLICATION must be run on the subscriber after any schema change affects which tables are published.

Resolution Approach

Apply DDL changes to the subscriber before the publisher when adding columns (prevents gap). After schema changes, run ALTER SUBSCRIPTION ... REFRESH PUBLICATION. For generated columns, ensure base column values are correctly replicated first.