Re: pg_upgrade and logical replication

On Sat, Jul 20, 2024 at 09:03:07PM -0500, Nathan Bossart wrote:
>> This is an extremely expensive way to perform that check, and so I'm
>> wondering why we don't just do
>>
>> SELECT count(*) FROM pg_catalog.pg_subscription;
>>
>> once in count_old_cluster_subscriptions().
>
> Like so...

Ah, good catch. That sounds like a good thing to do because we don't
care about the number of subscriptions for each database in the
current code.

This is something that qualifies as an open item, IMO, as this code
is new to PG17.

A comment in get_db_rel_and_slot_infos() becomes incorrect where
get_old_cluster_logical_slot_infos() is called; it is still referring
to the subscription count.

Actually, on the same grounds, couldn't we do the logical slot info
retrieval in get_old_cluster_logical_slot_infos() in a single pass as
well? pg_replication_slots reports some information about all the
slots, and the current code has a qual on current_database(). It
looks to me that this could be replaced by a single query, ordering
the slots by database names, assigning the slot infos in each
database's DbInfo at the end. That would be much more efficient if
dealing with a lot of databases.
--
Michael