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

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.

If the problem was: upgrade server that doesn't have any traffic,
I could just pg_dump + pg_restore, and be done with it. Unfortunately
this is not the case.

Best regards,

depesz