truemogician/upgrading_postgresql_in_windows.md

## upgrading_postgresql_in_windows.md

      
    Raw
  

              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.
No results found