key violation errors in logical replication subscriber

Question

Initial phase of the situation, I have following architecture

PG 16.x Master --> streaming replication --> PG 16.x Replica --> streaming replication --> PG 16.x second replica

and then

I have create publication with couple of schemas in master server and then created logical replication slot on first replica

and then I used select pg_promote function on second replica and also applied pg_replication_slot_advance command in first replica

There were no errors up to this point

So my architecture turned into following state

PG 16.x Master --> streaming replication --> PG 16.x Replica --> logical replication --> PG 16.x Subscriber

And then I have created subscription on subscriber server. When I checked logs in subscriber, I saw following errors on some tables

ERROR:  duplicate key value violates unique constraint "sdevi_cst_did_key". 

When I check this constraints and it's table on master server there is no key value violates issue.

So why I am getting this error in subscriber, even there is no related issue in the publisher

Thanks

Answer 1

If I understand correctly, when you use logical replication such as in your case between the Replica and the Subscriber, you will be subject to the restrictions on logical replication. According to the docs as of version 16, this includes:

• Sequence data is not replicated. The data in serial or identity columns backed by sequences will of course be replicated as part of the table, but the sequence itself would still show the start value on the subscriber. If the subscriber is used as a read-only database, then this should typically not be a problem. If, however, some kind of switchover or failover to the subscriber database is intended, then the sequences would need to be updated to the latest values, either by copying the current data from the publisher (perhaps using pg_dump) or by determining a sufficiently high value from the tables themselves.

I ran into this recently and suspect you are seeing the same thing. I see this is being worked on but seems to have missed the boat for PG17.

See this thread for how to copy the sequence data.