pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster
4+ messages / 4 participants
[nested] [flat]
* BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster
@ 2021-12-28 09:47 PG Bug reporting form <noreply@postgresql.org>
2021-12-28 16:00 ` Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2021-12-28 09:47 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: mathiaskunter@gmail.com
The following bug has been logged on the website:
Bug reference: 17347
Logged by: Mathias Kunter
Email address: mathiaskunter@gmail.com
PostgreSQL version: 12.9
Operating system: Debian 10.11
Description:
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster
2021-12-28 09:47 BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster PG Bug reporting form <noreply@postgresql.org>
@ 2021-12-28 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-28 18:12 ` Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster Mathias Kunter <mathiaskunter@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-12-28 16:00 UTC (permalink / raw)
To: mathiaskunter@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster
2021-12-28 09:47 BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster PG Bug reporting form <noreply@postgresql.org>
2021-12-28 16:00 ` Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-12-28 18:12 ` Mathias Kunter <mathiaskunter@gmail.com>
2021-12-29 10:20 ` Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mathias Kunter @ 2021-12-28 18:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster
2021-12-28 09:47 BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster PG Bug reporting form <noreply@postgresql.org>
2021-12-28 16:00 ` Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster Tom Lane <tgl@sss.pgh.pa.us>
2021-12-28 18:12 ` Re: BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster Mathias Kunter <mathiaskunter@gmail.com>
@ 2021-12-29 10:20 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2021-12-29 10:20 UTC (permalink / raw)
To: Mathias Kunter <mathiaskunter@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
On 28.12.21 19:12, Mathias Kunter wrote:
>> 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?
It actually says "you will be instructed to run a command [...]", which
is what happens: It prints out a command that you can use. It also says
"You might need to set connection parameters to match your new
cluster.", which could apply to your situation.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-12-29 10:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-28 09:47 BUG #17347: pg_upgrade: analyze_new_cluster script analyzes wrong cluster PG Bug reporting form <noreply@postgresql.org>
2021-12-28 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-28 18:12 ` Mathias Kunter <mathiaskunter@gmail.com>
2021-12-29 10:20 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox