Subject: Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong
 cluster
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
References: <17347-27b988f51d9dee1a@postgresql.org>
 <1760674.1640707235@sss.pgh.pa.us>
From: Mathias Kunter <mathiaskunter@gmail.com>
Message-ID: <10f71bb9-2ca6-24c4-0feb-f64138a0e4b3@gmail.com>
Date: Tue, 28 Dec 2021 19:12:06 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: <1760674.1640707235@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: de-AT
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/10f71bb9-2ca6-24c4-0feb-f64138a0e4b3%40gmail.com>
Precedence: bulk

> 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@postgresql.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
>