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 a New Windows User

This step is missing in the official documentation and can be quite perplexing. I stumbled upon this solution thanks to this answer.

The pg_upgrade utility attempts to connect to both servers using the system user (I'm not sure why they couldn't just use the default postgres user). So, you'll need a Windows user named postgres for the time being.

To create this user, navigate to the Control Panel and create an administrator user named postgres. This user will be used to execute pg_upgrade.

5. Manage File Security

The postgres Windows user should have access to both data directories. You can achieve this as follows:

Right-click on the data directory.
Go to the Security tab.
Click Edit.
Click Add.
Type "postgres" and hit Enter.
Select "postgres," check "Full Control," and hit "OK."

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

6. Open Console with User "postgres"

To proceed, you'll need to open a console session with the "postgres" user. There are a couple of ways to do this:

You can simply log in using the "postgres" user if you've created it.
Alternatively, if you'd rather not switch accounts, open PowerShell and run Start-Process powershell -Credential ''

This command will prompt you to enter a username and password. Once you do, a new PowerShell console will open. Make sure to close the old console, as the new one won't be able to receive input otherwise.

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

For example, this command expands to:

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'

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

8. 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.
Remove the file security entries for user "postgres" in both data directories.
Delete the Windows user "postgres."
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.

truemogician/upgrading_postgresql_in_windows.md