PostgreSQL: Windows, Major Upgrade

Post Date: 2023-04-28

PostgreSQLToday we will be upgrading PostgreSql from version 14 to 15, which would be considered a Major upgrade (14->15). Below, I provide step-by-step instructions on how to do this on Windows.

Step 1: Install postgres 15 on the machine, this will create a seperate folder in the install directory /14 vs /15

  • Recommend you keep the postgres user password the same

Step 2: Stop both versions of pg running on the system, here is the powershell to do that 

net stop postgresql-x64-14
net stop postgresql-x64-15

Step 3: In the /data directory of each version, keep around the existing pg_hba.config files. I typcially rename them to pg_hba.conf.org

Step 4: Drop in a new pg_hba.conf in each /data directory with the following contents, this helps security getting in the way of this upgrade, full local trust

host		all		postgres		127.0.0.1/32	trust
host		all		postgres		::1/128		trust

Step 5: Upgrade Data

Our server has the bin directories located here:

C:\\Program Files\\PostgreSQL\\14\\bin
C:\\Program Files\\PostgreSQL\\15\\bin

And our data directories located here:

D:\\Data\\PgSql\\14
D:\\Data\\PgSql\\15

To upgrade the data to 15 we will be using pg_upgrade.exe. I typically like to execute this in a temp directory to isolate the files that get generated during this process. The pattern for updating via pg_upgrade.exe is as follows:

NEWVERSION\\bin\\pg_upgrade.exe --old-datadir "OLDVERSION\\data" --new-datadir "NEWVERSION\\data" --old-bindir "OLDVERSION\\bin" --new-bindir "NEWVERSION\\bin" -U USER

So for us it would be as follows (NOTE: I'm executing this command in C:\\Temp via powershell, note the & '' is just to handle the space in the executable path)

& 'C:\\Program Files\\PostgreSQL\\15\\bin\\pg_upgrade.exe' --old-datadir "D:\\Data\\PgSql\\14" --new-datadir "D:\\Data\\PgSql\\15" --old-bindir "C:\\Program Files\\PostgreSQL\\14\\bin" --new-bindir "C:\\Program Files\\PostgreSQL\\15\\bin" -U postgres

Step 6: Post Upgrade: After you have a successful upgrade you still need to do a few things to get your server back up

  • Copy your pg_hba.conf from the old version into your new version data directory (remove the .org also if you don't need it)
  • Update your postgresql.conf file in the new version. The install probably saw that port 5432 was in use (from the old version running) and gave you something like 5433. Change this to 5432.
  • Move any SSL data over to the new version. See our ssl post here.
  • Start the new server (with powershell)
    net start postgresql-x64-15
  • Uninstall old version
  • Clear data old version

After this, you should be up and running in version 15.

Feedback? Better Idea? General Comment?