More replication slots created/requested than max_sync_workers_per_subscription

Question

We are setting up logical replication between a Postgres 14 instance to a Postgres 15 instance. When we upgraded from 12 to 14, we never had this issue of over provisioning replication slots.

We have set max_sync_workers_per_subscription to 6 on our destination database server. When we create a subscription, our subscription creates 6 replication slots initially, but slowly over time creates more replication slots and create 17 or more replication slots and attempts to create more replication slots than our max_replication_slots limit.

[7415]:STATEMENT:  CREATE_REPLICATION_SLOT "pg_85959_sync_82836_7356045360683676565" LOGICAL pgoutput USE_SNAPSHOT
[7416]:ERROR:  all replication slots are in use
[7416]:HINT:  Free one or increase max_replication_slots.

Why is the source database server attempting to create more replication slots than what is allowed?

Answer 1

In short: there is a known bug with an excessive number of replication slots due to a race condition between the end of one tableworker process (synchronizing one table) and the start of the next one.

Was discussed in pgsql-bugs mailing list here, fixed by commit. But it was decided to apply the fix only to HEAD and therefore was included only in postgresql 16. A suggested workaround for older releases is to raise max_replication_slots higher.