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. pg_upgrade_dump_1.log Description: pg_upgrade_dump_1.log
Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters
Hi, From: Thomas Boussekey Sent: Tuesday, July 12, 2022 3:39 AM > Hi, > > Le lun. 11 juil. 2022 à 21:22, Ken Yeung 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 pg_upgrade_dump_12292.log Description: pg_upgrade_dump_12292.log pg_upgrade_dump_28137747.log Description: pg_upgrade_dump_28137747.log
Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters
From: Laurenz Albe 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
