Re: Excessive number of replication slots for 12->14 logical replication

On Thu, Jul 14, 2022 at 5:22 PM hubert depesz lubaczewski
<depesz@depesz.com> wrote:
>
> Hi,
> We're using logical replication to replicate database from pg 12.9 to
> 14.4.
>
> Given the number of objects we made decision to use 10 separate
> publications, each containing subset of tables.
>
> So, we have:
>
...
>
> On both sides we have max_sync_workers_per_subscription set to 2.
>
> So, I would assume that during initial subscription, we can go up to 30
> connections. To make it safe we set limit to 50, but apparently it's not
> enough.
>
> I'm getting *LOTS* of errors about number of replication slots:
>
> 2022-07-14 11:40:30.612 UTC,"upgrayedd","dbname",11829,"10.1.191.34:37394",62d000ae.2e35,5,"idle in
transaction",2022-07-1411:40:30 UTC,38/350893,0,ERROR,53400,"all replication slots are in use",,"Free one or increase
max_replication_slots.",,,,"CREATE_REPLICATION_SLOT""pg_1337070_sync_189406_7119125406647933415"" LOGICAL pgoutput
USE_SNAPSHOT",,,"pg_1337070_sync_189406_7119125406647933415"
>
> We're getting them (in the beginning of sync) to the tune of 2-4 thousand per minute.!
>
...
>
> What is happening, and why Pg is trying to get more than 30 concurrent
> slots in this case?
>

As per my understanding, each subscription will use three slots (one
for apply worker and one for each of the sync workers) in your case in
the sync phase. So, in your case, if you have 10 subscriptions then
ideally it should be no more than 30. Can you please check and share
the subscriber logs to see if there are any errors in the initial sync
phase? Also, please confirm the number of subscriptions you create on
the subscriber?

--
With Regards,
Amit Kapila.