Обсуждение: 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
thefollowing 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.

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
inthe 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
failedfor 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
connectionto ::1. 

Specific to each cluster, there are some other databases upgraded/restored in the same cluster without this problem,
butonly "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.confchanged 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
thefollowing 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 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.

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