I have a PostgreSQL 13 installation on a Windows 10 server (on port 5432) that I want to migrate to a new PG 16.2 installation.
To this end, I downloaded the Windows x86-64 installer for PostgreSQL 16.2 here: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
During the run of the installer I manually set up:
- the default postgres user named
postgresa password, - the port of the cluster to 5433,
- the locale to en_US,
The PostgreSQL folder was successfully created at "C:\Program Files\PostgreSQL\16" with everything it needs. Then it proposed to execute the Stack Builder utility to install add-ons which I only used to install PostGIS.
To test the newly installed PostgreSQL, I added an entry in the %APPDATA%\postgresql\pgpass.conf file as explained here: https://www.postgresql.org/docs/current/libpq-pgpass.html
The pgpass file looks as follow:
#hostname:port:database:username:password
localhost:5432:*:postgres:************************************
localhost:5433:*:postgres:************************************
Then I successfully connected to PG with this command (it correctly fetched the password from the pgpass file):
"C:\Program Files\PostgreSQL\16\bin\psql.exe" -d postgres://postgres@localhost:5433/postgres
psql (16.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# SELECT version();
version
PostgreSQL 16.2, compiled by Visual C++ build 1937, 64-bit
(1 row)
I followed the steps as described here https://www.postgresql.org/docs/current/app-pgrestore.html to give pg_upgrade (16) a try:
- I gracefully shut down the two PG services (both 13 and 16)
- Because there is no
postgresuser at the operating system level, I created a folder atC:\tmpwith read/write access to everyone and from this folder I run the following command from a cmd.exe terminal:
"C:\Program Files\PostgreSQL\16\bin\pg_upgrade" -b "C:\Program Files\PostgreSQL\13\bin" -B "C:\Program Files\PostgreSQL\16\bin" -d "C:\Program Files\PostgreSQL\13\data" -D "C:\Program Files\PostgreSQL\16\data" -p 5432 -P 5433 -U postgres --check
Performing Consistency Checks
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for incompatible "aclitem" data type in user tables ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Clusters are compatible
I decided to run the exact same command again after removing the --check flag but it failed:
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for incompatible "aclitem" data type in user tables ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
template1
failure
Consult the last few lines of "C:/Program Files/PostgreSQL/16/data/pg_upgrade_output.d/20240415T170235.602/log/pg_upgrade_dump_1.log" for
the probable cause of the failure.
Failure, exiting
The mentioned pg_upgrade_dump_1.log file contains:
command: "C:/Program Files/PostgreSQL/16/bin/pg_dump" --port 5432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file="C:/Program Files/PostgreSQL/16/data/pg_upgrade_output.d/20240415T173939.552/dump/pg_upgrade_dump_1.custom" ^"dbname^=template1^" >> "C:/Program Files/PostgreSQL/16/data/pg_upgrade_output.d/20240415T173939.552/log/pg_upgrade_dump_1.log" 2>&1
command: "C:/Program Files/PostgreSQL/16/bin/pg_restore" --port 5433 --username postgres --clean --create --exit-on-error --verbose --dbname postgres "C:/Program Files/PostgreSQL/16/data/pg_upgrade_output.d/20240415T173939.552/dump/pg_upgrade_dump_1.custom" >> "C:/Program Files/PostgreSQL/16/data/pg_upgrade_output.d/20240415T173939.552/log/pg_upgrade_dump_1.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: error: connection to server at "localhost" (::1), port 5433 failed: FATAL: password authentication failed for user "postgres"
password retrieved from file "C:\Users<WindowsUser>\AppData\Roaming/postgresql/pgpass.conf"
I'm wondering why this automatic tool fails at this stage, because if I remove the PG 16 entry in the pgpass file, the pg_upgrade command fails much faster, which means that the pgpass file is correctly used at least for dumping the schemas, but apparently not for restoring them in the new PG 16 cluster.
Can anyone explain why exactly pg_upgrade fails and how to solve this problem? Because I really run both the PG 16 installer and the pg_upgrade tool in a vanilla way, without fancy options or setup.
which means that the PG 16 config files (such as pg_hba.conf or postgresql.conf) were not touched after the installation.
The pg_hba.conf file of PG 16 is exactly the same as the one described here.
The most relevant part being:
# PostgreSQL Client Authentication Configuration File
# ===================================================
# comments (...)
TYPE DATABASE USER ADDRESS METHOD
"local" is for Unix domain socket connections only
local all all scram-sha-256
IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
IPv6 local connections:
host all all ::1/128 scram-sha-256
Allow replication connections from localhost, by a user with the
replication privilege.
local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
The PG 13 pg_hba.conf file looks like this:
# TYPE DATABASE USER ADDRESS METHOD
"local" is for Unix domain socket connections only
local all all scram-sha-256
IPv4 local connections:
host all all 127.0.0.1/32 md5
host all postgres 0.0.0.0/0 md5
IPv6 local connections:
host all all ::1/128 md5
I'm therefore wondering if the issue would not be related to a different password encoding... But the documentation of the pg_upgrade tool doesn't say anything about it.
Side question:
How could I properly (i.e. ending with the same state as a fresh install) re-initialize the PG 16 cluster according to the following pg_upgade sentence: "If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing." ?
