Re: Logical replication stops dropping used initial-sync replication slots

On Tue, Mar 22, 2022 at 9:18 AM hubert depesz lubaczewski
<depesz@depesz.com> wrote:
>
> On Tue, Mar 22, 2022 at 08:37:07AM +0530, vignesh C wrote:
> > I could simulate the similar problem by starting a new transaction
> > while creating a subscription and notice the following log content:
> ...
> > I'm not sure if this is the same scenario or not.
> > Could you also confirm from pg_stat_activity and pg_locks if there are
> > any open transactions and if there is any open transaction, could you
> > see this transaction is on which relation and are these because of the
> > table sync workers?
>
> I don't think it is the same scenario because this is totally isolated
> server, with no applications running, cron stopped and no access from
> outside whatsoever.
> Also, even if I'd assume that some open transaction can pause initial
> sync, which is reasonable, why would it start to leave open replication
> slots, and start new ones, above number specified in
> max_sync_workers_per_subscription?
>
> > Also as an alternative you might be able to use pg_dump to take a
> > backup and restore it to the destination if there are no transactions
> > and create subscription with copy_data as off.
>
> That wouldn't do me any good. The server I'm trying to upgrade now is
> test bench to make sure that I can upgrade using logical replication *at
> all*.
> Once I will have verified that I can, I have couple hundred servers to
> upgrade, that will have concurrent access all the time.

Is it possible to get  a reproducible test or a script for this problem?

Regards,
Vignesh