Date: Mon, 21 Mar 2022 15:43:41 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: vignesh C <vignesh21@gmail.com>
Cc: pgsql-bugs mailing list <pgsql-bugs@postgresql.org>
Subject: Re: Logical replication stops dropping used initial-sync replication
 slots
Message-ID: <20220321144341.GA27089@depesz.com>
Reply-To: depesz@depesz.com
References: <20220317142426.GA727@depesz.com>
 <20220318122758.GA25022@depesz.com>
 <CALDaNm0+k0M6SDSVEsRWoS8VkwYtTz3WAQcjPWRheKu2-ym-pQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <CALDaNm0+k0M6SDSVEsRWoS8VkwYtTz3WAQcjPWRheKu2-ym-pQ@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20220321144341.GA27089%40depesz.com>
Precedence: bulk

On Mon, Mar 21, 2022 at 07:48:20PM +0530, vignesh C wrote:
> > What can I do about it?
> 
> >2022-03-17 13:32:48.119 UTC,,,2923553,,62333880.2c9c21,2,,2022-03-17 13:32:48 UTC,8/447946,23877409,ERROR,53400,"could not find free replication state slot for replication origin with OID 51",,"Increase max_replication_slots and try again.",,,,,,,"","logical replication worker",,0
> Each Tablesync worker needs a replication slot to synchronize the data
> to the subscriber, this error means there are not enough replication
> slots. Try to increase max_replication_slots configuration if it is
> set to lower value as you are using around 60k tables.

yeah, but I don't need 60k replication slots?

Please note that replication (initial sync) *IS* working happily for
*some* time, and only then starts "eating" replication slots for
whatever reason.

Specifically - I have max_replication_slots set to 50 (per my original
mail), and sync workers shouldn't exceed 10.

> > 2022-03-17 18:28:54.189 UTC,"lrep_user","dbname",17706,"10.2.210.180:35794",62337de5.452a,13,"idle",2022-03-17 18:28:53 UTC,16/0,0,LOG,00000,"logical decoding found initial starting point at 8FB/83FCA7D8","Waiting for transactions (approximately 1) older than 160417372 to end.",,,,,"START_REPLICATION SLOT
> 
> Snapshot builder is waiting for the open transaction to finish. Is it
> possible that there are some open transactions that were started
> during this logical replication. I felt once the transaction
> completes, snapshot building and tablesync operation should finish.

There are no long open transacitons there. This is test box with *zero*
traffic (aside from running in a loop: create table, drop table), but
*NOT* in transaction.

Best regards,

depesz