Postgresql – How to resolve “logical replication target relation schema.table is missing some replicated columns” error in postgresql logical replication

postgresqlpostgresql-12replication

I'm getting

"logical replication target relation schema.table is missing some replicated columns"

error in logical replication. I'm using all table replication. Due to some changes I wanted to delete a column from both subscriber and publisher. I dropped column from subscriber end first and then from publisher(it should've been other way around). But deletion from publisher took time and logical replication broke.

I tried adding columns at both end in every way. I also tried refreshing the publication at subscriber end. I also enabled and disabled the subscription. Still the postgres gives same error.

I'm using PostgreSQL 12.3.

How do I start the logical replication in such case without any data loss?

————-UPDATE—————

I resolved it by dropping the subscription and recreated it with copy_data = false. Later I copied the data missing on the subscriber side.
This was quite a headache and a long way to do this. Still looking for optimal solutions for this problem. Optimal solutions for this are welcomed.

Best Answer

Simply add the column again at the subscribed database and wait until replication has caught up before dropping the column again.