PostgreSQL® Major Version Upgrade

This guide describes the step-by-step procedure for performing a major version upgrade of a PostgreSQL® database on Windows and Linux. It includes important notes on data backup, environment preparation, as well as execution and follow-up of the upgrade process.

Install new PostgreSQL® version (do not uninstall current one, both are necessary for upgrade process)
Important:
It is strongly recommended to create a complete backup copy of the database before upgrading the version so that accidentally deleted data can be restored.

Note:
You can change the PostgreSQL® database indices from BRIN to BTREE before or after the upgrade. See chapter PostgreSQL® Schema. Please note that the database statistics must be recreated - see step 13
Close all programs that could connect to the database (e.g. PGAdmin)
Setup the PGDATA directory for the new PostgreSQL® version:
- Decide which directory you want to use as your PGDATA directory.
- Ensure that the directory is empty.
- Make sure that the permissions for this directory are set correctly:
  - Compare the permissions of the new PostgreSQL® data directory with those of the current PostgreSQL® installation.
  - If you want to run PG as a Windows service, make sure to give the user NETWORK SERVICE Full control.
- Initialize the chosen PGDATA directory using the: initdb from the new PostgreSQL® installation:
  - Windows:
  - Linux (Oracle, RHEL):
  - Linux (Debian):
  "C:\Program Files\PostgreSQL\<new_version>\bin\initdb.exe" -D <new PGDATA directory> -U postgres -E UTF8
  /usr/pgsql-<new_version>/bin/initdb -D <new PGDATA directory> -U postgres
  /usr/lib/postgresql/17/bin/initdb -D <new PGDATA directory> -U postgres
With version 17 of the PostgreSQL® database, the adminpack extension is no longer supported and has been removed. If this extension was previously added to your database, you need to remove it before upgrading. To do this, log in to the database you plan to upgrade and run the following SQL command:
```
DROP EXTENSION IF EXISTS adminpack CASCADE;
```
Alternatively, you can run the provided drop_adminpack.sql script with the following command, e.g.: Windows:
```
<PostgreSQL_old_version_path>\bin\psql -f <WinCC OA install path >\data\NGA\PostgreSQL\sql\Upgrades\drop_adminpack.sql -h <hostname> -p <port> -U postgres –d winccoa
```
Make sure to use the psql binary from the current (upgraded from) PostgreSQL® version.
Set all local addresses to "trust" in the pg_hba.conf file in the old PGDATA directory. On Debian based Linux distributions the configuration files are in /etc/postgresql/<version>/main.
Shutdown the PostgreSQL® database:
- If you run PostgreSQL® as Windows service, then just stop the service postgresql-x64-<old version>.
- On Linux use the systemctl command depending on your distribution:
  - Linux (Oracle, RHEL):
  - Linux (Debian):
  systemctl stop postgresql-<version>
  systemctl stop postgresql@<old_version>-main.service
- Else use pg_ctl from the old PostgreSQL® installation (when using this option, ensure that your operating system is not automatically restarting the database):
  - Windows
  - Linux (Oracle, RHEL):
  - Linux (Debian):
  "C:\Program Files\PostgreSQL\<old_version>\bin\pg_ctl.exe" stop -D <old PGDATA directory> -U postgres
  /usr/pgsql-<old_version>/bin/pg_ctl -D <old PGDATA directory> stop
  /usr/lib/postgresql/<old_version>/bin/pg_ctl -D <old PGDATA directory> stop
Make sure no other instance of PostgreSQL® is running and blocking the standard port.

Upgrade your old PostgreSQL® database to new PostgreSQL® using pg_upgrade from the new PostgreSQL® installation:

Windows:
Linux (Oracle, RHEL):
Linux (Debian):

"C:\Program Files\PostgreSQL\<new_version>\bin\pg_upgrade.exe" -b "C:\Program Files\PostgreSQL\<old_version>\bin" -B "C:\Program Files\PostgreSQL\<new_version>\bin" -d <old PGDATA directory> -D <new PGDATA directory> -j 4 -k -p <port of old PostgreSQL® server> -P <port of new PostgreSQL® server> -U postgres

/usr/pgsql-<new_version>/bin/pg_upgrade -b /usr/pgsql-<old_version>/bin -B /usr/pgsql-<new_version>/bin -d <old PGDATA directory> -D <new PGDATA directory> -j 4 -k -p <port of old PostgreSQL®> -P <port of new PostgreSQL®> -U postgres  -j 4 -k -p <port of old PostgreSQL®> -P <port of new PostgreSQL®> -U postgres

/usr/lib/postgresql/<new_version>/bin/pg_upgrade -b /usr/lib/postgresql/<old_version>/bin -B /usr/lib/postgresql/<new_version>/bin -d <old PGDATA directory> -D <new PGDATA directory> --old-options=”-D /etc/postgresql/16/main” -j 4 -k -p <port of old PostgreSQL®> -P <port of new PostgreSQL®> -U postgres

The -j option configures how many parallel jobs are used – adjust this value depending on the hardware used. The -k option instructs pg_upgrade to create hard-links instead of copying the database files. This feature makes the upgrade process very quick but works only if new and old directories are on the same filesystem.

Change the
```
 pg_hba.conf 
```
file in the new PGDATA directory of PostgreSQL® according to your needs (e.g. copy from old PGDATA directory ). Remember to revert changes applied in step 5.
Start the new PostgreSQL® server:
- If you want to start it as a service on Windows, open Services and make sure that the argument for the -D switch of the postgresql-x64-<new version> is pointing to the correct PGDATA directory <new PGDATA directory> and start the service.
- On Linux use the systemctl command depending on your distribution:
  - Oracle, RHEL
  - Debian
  systemctl start postgresql-<version>
  systemctl start postgresql@<old_version>-main.service
- If you start it manually, use:
```
 pg_ctl:
```
  - Windows
  - Linux (Oracle, RHEL):
  - Linux (Debian):
  "C:\Program Files\PostgreSQL\<new_version>\bin\pg_ctl.exe" start -D <new PGDATA directory> -w -U postgres
  /usr/pgsql-<new_version>/bin/pg_ctl -D <new PGDATA directory> start
  /usr/lib/postgresql/<new_version>/bin/pg_ctl -D <new PGDATA directory> start
Removing old data directory:
The pg_upgrade command creates a delete_old_cluster.bat or delete_old_cluster.sh script that must be used to remove the old database directory after successful upgrade.
Update the database schema by following the appropriate instructions here: PostgreSQL® Database Maintenance - DBAdmin-Managed Database.
After starting the server, finalize the upgrade by calling SELECT analyze_database();. This will analyze the tables in order, starting with the most recently created ones first. This will quickly improve the performance of queries that use only the most recent data.
- Windows
- Linux (Oracle, RHEL):
- Linux (Debian):
```
"C:\Program Files\PostgreSQL\<new_version>\bin\psql.exe" -U postgres -d winccoa -c " SELECT analyze_database();"
```
```
/usr/pgsql-<new_version>/bin/pg_ctl -U postgres -d winccoa -c "analyze;"
```
```
/usr/lib/postgresql/<new_version>/bin/pg_ctl -U postgres -d winccoa -c "SELECT analyze_database();"
```