PostgreSQL 12.1生成列逻辑复制失败问题求助

Let's break down why you're hitting this error and walk through fixes that don't require modifying the subscriber's primary key.

Root Cause #

Your patientid is a STORED generated column, calculated from pk and clientid via fnGetPrimaryKey. Even though you configured the replica identity to use patientid, logical replication doesn’t automatically send generated column values by default when capturing changes—since the column’s value is derived from other physical columns. The subscriber expects patientid (its primary key) to be sent as the replica identity to match rows for updates/deletes, hence the missing column error.

When you switch the primary key to (clientid, pk), replication works because those are physical columns the publisher can easily capture and send, and the subscriber uses them to target the correct rows.

Fixes Without Changing Subscriber's Primary Key #

Option 1: Explicitly Include the Generated Column in the Publication #

Since patientid is a STORED column, its value is physically saved in the table. You just need to ensure your publication explicitly includes it so the publisher sends it with change events:

1. Update your publication to include patientid:

   -- Replace 'your_publication' with your actual publication name
   ALTER PUBLICATION your_publication 
   ADD TABLE patient (pk, patientid, firstname, clientid);
   

   This forces the publisher to include patientid in all change messages sent to subscribers.

2. Keep your existing replica identity configuration:
   No need to change this since it’s already targeting the patientid unique index:

   CREATE UNIQUE INDEX IF NOT EXISTS patient_replica_identity ON patient (patientid);
   ALTER TABLE patient REPLICA IDENTITY USING INDEX patient_replica_identity;
   

Option 2: Use Full Replica Identity (Simpler, Less Efficient) #

If Option 1 doesn’t resolve the issue, you can set the replica identity to FULL, which makes the publisher send all columns (including patientid) with every change. This is a quick fix but increases replication traffic:

ALTER TABLE patient REPLICA IDENTITY FULL;

With this setting, the subscriber will always receive patientid and can use it to match rows against its existing primary key.

Option 3: Validate Subscriber's Generated Column Logic #

Double-check that the fnGetPrimaryKey function on the subscriber is identical to the publisher’s. While this isn’t the direct cause of your current error, mismatched logic could lead to data inconsistencies later when the subscriber calculates patientid locally.

Verification #

After applying either fix, restart your replication subscription (or reinitialize it if needed) to test if the error is resolved. You should see the publisher sending patientid as part of change events, allowing the subscriber to match rows using its existing patientid primary key.

内容的提问来源于stack exchange，提问作者Nayan Rudani