Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster

> The assumption you're making is that --new-port will be the new
> cluster's port number after it's started for real.
I'd argue that this assumption is mostly correct in such situations
where the user explicitly specifies the --new-port argument. In case a
nonce configuration is wanted, why would a user explicitly specify the
--new-port argument instead of just using pg_upgrade with default settings?

Also, the analyze script considers neither the new nor the old port of
the upgraded cluster, but apparently just simply always connects to the
cluster on the default 5432 port.

I followed the steps outlined at
https://www.postgresql.org/docs/12/pgupgrade.html to upgrade a cluster
running on port 5433 (not port 5432). Thus, I've run pg_upgrade with
both --old-port and --new-port set to 5433.

In such a scenario, I'd definitely expect the analyze script to connect
to the cluster on port 5433. But it will instead analyze the cluster on
port 5432, which is completely unrelated to the upgrade.

This problem can be hard to detect if a different cluster is running on
port 5432 (in my situation this was the case) because obviously you
won't get any error message then. I didn't notice the problem until I
found out that some queries were painfully slow after the upgrade, which
was a result of some indices not being used due to missing statistics.

> It's a bit moot now anyway, since pg_upgrade doesn't create
> that script anymore in v14 and up.

The docs at https://www.postgresql.org/docs/14/pgupgrade.html (step 15
"Statistics") though say the user must "run a command to regenerate that
information". Is the documentation up-to-date?

Best regards
Mathias Kunter

Am 28.12.21 um 17:00 schrieb Tom Lane:
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
>> When upgrading a PostgreSQL cluster with pg_upgrade (tested while upgrading
>> from 11.14 to 12.9, but supposedly affects all versions), and the --new-port
>> argument is used to specify a new port number for the upgraded cluster, then
>> the generated analyze_new_cluster script will connect to and analyze a
>> different cluster instead of the upgraded one.
>> The vacuumdb command found within the analyze_new_cluster script should
>> explicitly specify the port number of the upgraded cluster, so that it will
>> actually analyze the correct cluster after the upgrade.
>
> Hmm. I'm not sure whether changing that would be an improvement.
> The assumption you're making is that --new-port will be the new
> cluster's port number after it's started for real. But it seems
> at least as likely that it's just a nonce specification used for
> long enough to finish sucking data out of your old cluster, and
> then you'll start the new server in place of your old one (ie
> using the old port).
>
> It's a bit moot now anyway, since pg_upgrade doesn't create
> that script anymore in v14 and up.
>
> regards, tom lane
>