From: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> |
---|---|
To: | Hubert Lubaczewski <depesz(at)depesz(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Excessive number of replication slots for 12->14 logical replication |
Date: | 2022-07-18 03:37:35 |
Message-ID: | CAA4eK1LAk=9fi7V=XKb7d40hOyKTt5a3MTOaEQpUr-RHgpixxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Thu, Jul 14, 2022 at 5:22 PM hubert depesz lubaczewski
<depesz(at)depesz(dot)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-14 11: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.
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2022-07-18 03:51:21 | Re: Excessive number of replication slots for 12->14 logical replication |
Previous Message | Tom Lane | 2022-07-17 22:59:40 | Re: BUG #17483: postgres_fdw used with text to_tsvector and custom search configuration |