Upgrading PostgreSQL in Windows

upgrading_postgresql_in_windows.md

Upgrading PostgreSQL in Windows

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.

1. Install the New Version of PostgreSQL

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.

2. Stop Both PostgreSQL Services

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+R and type in services.msc to open the Services application.
• Look for services with names like postgresql-{major_version} (or postgresql-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

3. Configure Authentication

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.

4. Create Temporary Directory

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."

5. Check Release Notes

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.

6. Execute 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 postgres

For 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 postgres

But 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).

7. Clean Up

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-256 in pg_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.

navigate to Temp.
run this command on cmd as adminstrator,
{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}" --old-port=5432 --new-port=5433 -U postgres
It'll go well

I didn't create new Windows user

gracias @kasoir. Tu respuesta me funcionó

gracias @truemogician por el posteo. Saludos

Thank @KurvanM and @kasoir for the feedback. I verified the -U option and it works. I published a new version utilizing this option instead of the verbose Windows user approach.

In my case I had an extension preventing me from upgrading from 16 to 17.
The extension was adminpack, which was removed from 17, which means that if you had a regular installation, following this guide will throw an error.
In order to fix this issue, you have to first remove the extension:

{old_bin_dir}/psql.exe -U postgres
DROP EXTENSION adminpack CASCADE;

Thanks for the hint with adminpack. That saved my migration! 🎉

Hello everyone, my problem is an update to 12 from 15.
This command works :
"C:\Program Files\PostgreSQL\15\bin\pg_upgrade.exe" --old-datadir="C:\Program Files\PostgreSQL\12\data" --new-datadir="C:\Program Files\PostgreSQL\15\data" --old-bindir="C:\Program Files\PostgreSQL\12\bin" --new-bindir="C:\Program Files\PostgreSQL\15\bin" -U postgres

But I have a "simple" problem with collation :

Values lc_collate of database « template1 » ne correspondent pas : old « French_France.1252 », new « fr-FR »
Failure, end

ALTER COLLATION gives me :

« French_France.1252 » pour l'encodage « UTF8 » doesn't exist !

I'm a bit stuck. Have you any idea to solve this problem ?

"Merci" in advance

Hello everyone, my problem is an update to 12 from 15. This command works : "C:\Program Files\PostgreSQL\15\bin\pg_upgrade.exe" --old-datadir="C:\Program Files\PostgreSQL\12\data" --new-datadir="C:\Program Files\PostgreSQL\15\data" --old-bindir="C:\Program Files\PostgreSQL\12\bin" --new-bindir="C:\Program Files\PostgreSQL\15\bin" -U postgres

But I have a "simple" problem with collation :

Values lc_collate of database « template1 » ne correspondent pas : old « French_France.1252 », new « fr-FR »
Failure, end

ALTER COLLATION gives me :

« French_France.1252 » pour l'encodage « UTF8 » doesn't exist !

I'm a bit stuck. Have you any idea to solve this problem ?

"Merci" in advance

try look at this: https://stackoverflow.com/questions/5090858/how-do-you-change-the-character-encoding-of-a-postgres-database

Just want to say that this is a very nicely put together walkthrough for simplifying the upgrading process.

Also a few notes from my experience:

• My Postgres directories were located in C:\Program Files\PostgreSQL which had it's own share of permission issues.
• Running pg_upgrade.exe from my temp directory still required both Postgres version directories to have full permissions granted to them otherwise the program wouldn't have permissions to access each of the files. In my case, it was version 16 and 17.

Thanks also to @Zenoo and @sitrucp for pointing out some very unexpected but helpful solutions to some roadblocks I came across.

I found a way to set the password, before running pg_upgrade you need to run this in the same PowerShell instance:

[System.Environment]::SetEnvironmentVariable('PGPASSWORD','your_password')

Then I ran into another issue:

Checking for presence of required libraries                   fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    C:/Program Files/PostgreSQL/17/data/pg_upgrade_output.d/20250526T203224.248/loadable_libraries.txt```

Which contained:

could not load library "$libdir/adminpack": ERROR:  could not access file "$libdir/adminpack": No such file or directory
In database: postgres

Removing the extension as Zenoo described fixed that particular problem. Of course, you need to do that while the old instance is still running. If you already stopped it, you need to start it, run the command, and stop it again, like this (my old installation is version 15):

PS C:\Users\vladn\pg_upgrade> Start-Service -Name postgresql-x64-15
PS C:\Users\vladn\pg_upgrade> C:\Program` Files\PostgreSQL\15\bin\psql.exe -U postgres -c 'DROP EXTENSION adminpack CASCADE;'
DROP EXTENSION
PS C:\Users\vladn\pg_upgrade> Stop-Service -Name postgresql-x64-15

Also, the --new-bindir parameter doesn't seem to be mandatory, it ran fine without it, it probably deduced it from the directory of the pg_upgrade executable. I used this command and it worked:

C:\Program` Files\PostgreSQL\17\bin\pg_upgrade.exe -U postgres -b 'C:\Program Files\PostgreSQL\15\bin' -d 'C:\Program Files\PostgreSQL\15\data' -D 'C:\Program Files\PostgreSQL\17\data'

The upgrade also created a delete_old_cluster.bat file in the temporary directory, which would just delete the old data directory, but from what I see it doesn't uninstall the associated Windows service. For now I'll probably just keep the old instance, as in my case it doesn't use a lot of space.

Other than this, I needed to copy some configuration from pg_hba.conf between the two installations and restart the new instance again.

Thanks for making this guide, and thanks to everyone which also contributed, you probably saved me a lot of time.

The funny thing is that at the end I discovered that I didn't actually need this upgrade, I was getting an error which I thought was caused by some feature missing from v15, but that was not the case.