The official documentation for PostgreSQL provides a guide to upgrading using pg_upgrade. However, it can be a bit like a treasure hunt with hidden traps. This gist will walk you through a clear and robust method for upgrading PostgreSQL on Windows, using the upgrade from version 14 to 16 as an example.
Before you embark on the upgrade journey, make sure you have the new version installed. This is because the pg_upgrade utility used during the upgrade process belongs to the newer version.
To get started, visit the official download page, download the installer, and run it. A word of caution: do not overwrite the old installation, as you'll need its binaries and data for the upgrade.
To ensure a smooth transition, you'll need to stop both the old and new PostgreSQL services. Here's how you can do it:
- Press
Win+Rand type inservices.mscto open the Services application. - Look for services with names like
postgresql-{major_version}(orpostgresql-x64-{major_version}for x64 machines), and stop them manually. - Alternatively, you can use PowerShell to stop the services with these commands:
Stop-Service -Name postgresql-x64-14 Stop-Service -Name postgresql-x64-16
During the PostgreSQL upgrade, pg_upgrade connects to both the old and new PostgreSQL instances. However, there isn't an obvious way to specify credentials (or perhaps there is, but it's well-hidden). To work around this, you'll need to temporarily modify the authentication settings in {data_dir}/pg_hba.conf.
Open the {data_dir}/pg_hba.conf file, which should resemble the following at the bottom:
# 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
Change the METHOD of IPv4 and IPv6 local connections to trust, 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 trust
# IPv6 local connections:
host all all ::1/128 trust
# 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
Don't forget to update the authentication configurations for both the old and new PostgreSQL services.
pg_upgrade requires write permission in the directory where it's executed. You can easily solve this by creating a temporary directory, let's say D:\Temp, and granting Write permission to "Everyone."
The release notes of each PostgreSQL version list the breaking changes that may cause incompatibility issues and corresponding solutions during an upgrade. You may skip this step and come back to it when you encounter unexpected errors when executing pg_upgrade.
Now, navigate to the temporary directory you configured in step 5 (you can run cd D:/Temp for my example).
It's finally time to execute the actual command. Run the following command:
{new_bin_dir}/pg_upgrade.exe --old-datadir {old_data_dir} --new-datadir {new_data_dir} --old-bindir {old_bin_dir} --new-bindir {new_bin_dir} -U postgresFor example, this command expands to for my example:
D:\Program Files\PostgreSQL\16\bin\pg_upgrade.exe --old-datadir 'D:\Data\PostgreSQL\14.4' --new-datadir 'D:\Data\PostgreSQL\16' --old-bindir 'D:\Program Files\PostgreSQL\14.4\bin' --new-bindir 'D:\Program Files\PostgreSQL\16\bin' -U postgresBut don't hit enter just yet. There's a subtle bug: spaces in the binary path can cause issues. Typically, you'd wrap the path in quotes and use the call operator & to run the command. However, this approach doesn't work here. When you use &, the standard input of the command isn't redirected, leading to a bug in xcopy, an old Windows command used by pg_upgrade for copying directories.
If you run the command with &, it's likely that the upgrade will fail when copying pg_xact from the old data directory to the new one. The log might look like this:
command: xcopy /e /y /q /r "D:/Data/PostgreSQL/14.4/pg_xact" "D:/Data/PostgreSQL/16/pg_xact\" >> "D:/Data/PostgreSQL/16/pg_upgrade_output.d/20231009T132036.390/log/pg_upgrade_utility.log" 2>&1
Invalid path
0 files copied
I discovered this after much trial and error, and you can find more details in this post.
To avoid this issue, use the PowerShell escape character ` to escape the spaces in the path of the binary. It should look like this:
D:\Program` Files\PostgreSQL\16\bin\pg_upgrade.exe --old-datadir 'D:\Data\PostgreSQL\14.4' --new-datadir 'D:\Data\PostgreSQL\16' --old-bindir 'D:\Program Files\PostgreSQL\14.4\bin' --new-bindir 'D:\Program Files\PostgreSQL\16\bin'Now everything should work smoothly (at least to the best of my knowledge).
To wrap up the upgrade, you'll need to revert the temporary adjustments you made. Here's a checklist:
- Delete the temporary directory used as the current directory to execute
pg_upgrade. - Change the authentication methods of localhost back to
scram-sha-256inpg_hba.conf. - Start the new PostgreSQL service (or the old one if needed).
With these steps, you'll have successfully upgraded PostgreSQL on Windows while ensuring a smooth transition and cleaning up any temporary changes made during the process.
Thanks for the hint with adminpack. That saved my migration! 🎉