09. Upgrade PostgreSQL 9.4 to 9.5 on Windows

Follow this article if you want to upgrade PostgreSQL database engine from version 9.4 to version 9.5.

Step 1: Download and Install PostgreSQL 9.5

Download PostgreSQL 9.5 here.

After downloading, double click on the downloaded file and follow the installation wizard to install it.

When installation completes, verify that the PostgreSQL service is running by following below steps:

  • Open command prompt and type services.msc to open Services window 
  • On Services window, look for the service named postgresql-x64-9.5 and make sure its Status is Running and its Startup Type is set to Automatic. You should also notice that there are 2 postgres services running, one is PostgreSQL 9.4 and another one is PostgreSQL 9.5, as below screenshot

postgres-upgrade.png

Step 2 - Stop PostgreSQL services

On Services windows, perform following steps to stop both PostgreSQL services

  • Right clicking on a service named postgresql-x64-9.4 and select Stop in the popup menu
  • Right clicking on a service named postgresql-x64-9.5 and select Stop in the popup menu

stop-postgresql-services.png

Step 3 - Create a new Windows user

Create a new user on your PC named postgres. This is a Windows user, not a database user. Add it to your PC’s Administrators group.

create-postgres-windows-user.png

Step 4 - Grant permission to postgres Windows user

Go to Windows Explorer, give the Windows postgres account Full Control permission on C:\Program Files\PostgreSQL\ and all children.

grant-permission-to-postgres-windows-user.png

Step 5 - Edit the pg_hba.conf files

Edit the pg_hba.conf files for both servers. They are in C:\Program Files\PostgreSQL\[version]\data. If they are stock, comment out ALL lines, then add the below lines at the bottom.

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

Step 6 - Run command prompt as postgres Windows user

Open a command prompt window in administrator mode. Run this command: RUNAS /USER:postgres “CMD.EXE”

runas-command.png

If being asked, enter the password of the postgres Windows user.

On the command prompt console, change directory to the folder C:\Program Files\PostgreSQL\9.5\bin and type below command, in one line.

C:\Program Files\PostgreSQL\9.5\bin>pg_upgrade.exe --old-datadir "C:\Program Files\PostgreSQL\9.4\data" --new-datadir "C:\Program Files\PostgreSQL\9.5\data" --old-bindir "C:\Program Files\PostgreSQL\9.4\bin" --new-bindir "C:\Program Files\PostgreSQL\9.5\bin"

Tip: you can copy above command and paste to the command prompt window then press Enter.

The upgrade process will start to execute, this will take a while to complete if you have a lot of data. Wait until this is done before continuing.

Step 7 - Update PostgreSQL port in postgresql.conf

Edit the postgresql.conf file of the new PostgreSQL 9.5 install at C:\Program Files\PostgreSQL\9.5\data\postgresql.conf and change the listening port to 5432.

NOTE: if your qTest and/or qTest Sessions are connecting to PostgreSQL on a port which is different than the default 5432, e.g. 9999, make sure you change the value to 9999

update-postgresql-9-5-port.png

Step 8 - Start PostgreSQL service

In Services window, start PostgreSQL 9.5 service

start-postgres-9.5-service.png

 

Step 9 - Verify

Access to your qTest and/or qTest Sessions site to verify the application is working and the data are being shown.

If you created a postgres Windows account above, remove it.

Once you have verified that everything works properly, you can optionally uninstall the old PostgreSQL 9.4.