Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters
In the course of upgrading a bunch of database clusters from 10 to 14 using pg_upgrade tool, some databases result in the following error recorded in pg_upgrade_dump_1.log, such that cluster upgrade fails.
FATAL: password authentication failed for user "postgres"
This was done on Microsoft Windows 11.
PostgreSQL 10 and 14 used are built using Visual Studio 2015.
Only a small number of database clusters has this problem, around 2 of 200.
It appears these database are used from earlier cluster upgrade from 9.4 to 10 as well.
It would be very helpful if there is any possible workaround to overcome this problem.
I attached a sample pg_upgrade_dump_1.log reported this error.
Attachments:
Hi,
Le lun. 11 juil. 2022 à 21:22, Ken Yeung <Ken.Yeung@rib-software.com> a
écrit :
In the course of upgrading a bunch of database clusters from 10 to 14
using pg_upgrade tool, some databases result in the following error
recorded in pg_upgrade_dump_1.log, such that cluster upgrade fails.
FATAL: password authentication failed for user "postgres"
This error happens on the restoration-side (Pg14)
pg_restore: error: connection to server at "localhost" (::1), port 50432
failed: FATAL: password authentication failed for user "postgres"
means that you try to use ipv6 (is it possible, according to your target
postgresql.conf file?)
Does your Pg14 target pg_hba.conf file allow this kind of connection?
Last year, I had to migrate 200+ PostgreSQL instance from Pg9.5 to Pg12, I
discovered some configuration differences while performing this kind of
pre-upgrade tests.
This was done on Microsoft Windows 11.
PostgreSQL 10 and 14 used are built using Visual Studio 2015.
Only a small number of database clusters has this problem, around 2 of 200.
It appears these database are used from earlier cluster upgrade from 9.4
to 10 as well.It would be very helpful if there is any possible workaround to overcome
this problem.I attached a sample pg_upgrade_dump_1.log reported this error.
Hope it helps,
Thomas
Hi,
From: Thomas Boussekey <thomas.boussekey@gmail.com>
Sent: Tuesday, July 12, 2022 3:39 AM
Hi,
Le lun. 11 juil. 2022 à 21:22, Ken Yeung <Ken.Yeung@rib-software.com> a écrit :
In the course of upgrading a bunch of database clusters from 10 to 14 using pg_upgrade tool, some databases result in the following error recorded in pg_upgrade_dump_1.log, such that cluster upgrade fails.
FATAL: password authentication failed for user "postgres"This error happens on the restoration-side (Pg14)
pg_restore: error: connection to server at "localhost" (::1), port 50432 failed: FATAL: password authentication failed for user "postgres"
means that you try to use ipv6 (is it possible, according to your target postgresql.conf file?)
Does your Pg14 target pg_hba.conf file allow this kind of connection?
Although we have some different settings in pg_hba.conf in different clusters, but both at least allows local connection to ::1.
Specific to each cluster, there are some other databases upgraded/restored in the same cluster without this problem, but only "template1" reported this error. I attached some another log file recorded.
There may be a possibility some tables were created in the cluster with older pg_hba.conf settings, and then pg_hba.conf changed afterwards. Would this situation possibly caused the reported error?
Best regards,
Ken
On Mon, 2022-07-11 at 05:37 +0000, Ken Yeung wrote:
In the course of upgrading a bunch of database clusters from 10 to 14 using pg_upgrade tool, some databases result in the following error recorded in pg_upgrade_dump_1.log, such that cluster upgrade
fails.
FATAL: password authentication failed for user "postgres"This was done on Microsoft Windows 11.
PostgreSQL 10 and 14 used are built using Visual Studio 2015.
Only a small number of database clusters has this problem, around 2 of 200.
It appears these database are used from earlier cluster upgrade from 9.4 to 10 as well.It would be very helpful if there is any possible workaround to overcome this problem.
I attached a sample pg_upgrade_dump_1.log reported this error.
You configured "pg_hba.conf" so that a local login without password is
not possible. The easiest solution is to change the "pg_hba.conf"
entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of
the upgrade.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Wednesday, July 13, 2022 3:16 AM
You configured "pg_hba.conf" so that a local login without password is
not possible. The easiest solution is to change the "pg_hba.conf"
entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of
the upgrade.
I am afraid this workaround may not be suitable for our case because of certain security consideration.
Best regards,
Ken
On Wed, 2022-07-13 at 02:53 +0000, Ken Yeung wrote:
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Wednesday, July 13, 2022 3:16 AMYou configured "pg_hba.conf" so that a local login without password is
not possible. The easiest solution is to change the "pg_hba.conf"
entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of
the upgrade.I am afraid this workaround may not be suitable for our case because of certain security consideration.
If you want to make your life difficult, that's ok. Remember that this change
would only be for *local* connections for the duration of the upgrade.
As an alternative, you could create a password file or, if that is also too
insecure, set the PGSSLCERT and PGSSLKEY environment variables so that you can
use a client certificate to connect. Other authentication methods might also
be possible; you'll have to experiment.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com