PostgreSQL® Version Upgrade from Version 13 to Version 16

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

Install PostgreSQL® 16
Setup the PGDATA directory for PostgreSQL® 16:
1. Decide which directory you want to use as your PGDATA directory for PostgreSQL® 16.
2. Delete the contents of the selected directory.
3. Make sure that the permissions for this directory are set correctly:
  1. Compare the permissions of the PostgreSQL® 16 data directory with those of the PostgreSQL® 13 installation.
  2. If you want to run PG as a Windows service, make sure to give the user NETWORK SERVICE Full control.
4. Initialize the chosen PGDATA directory using the
```
initdb
```
  from the PostgreSQL® 16 installation:
  Windows:
```
"C:\Program Files\PostgreSQL\16\bin\initdb.exe" -D <new PGDATA directory for PostgreSQL® 16> -U postgres
```
  Linux:
```
/usr/pgsql-16/bin/initdb -D <new PGDATA directory for PostgreSQL® 16> -U postgres
```
Set all local addresses to trusted in the C:\Program Files\PostgreSQL\16\data\pg_hba.confpg_hba.conf file in the old PGDATA directory of PostgreSQL® 13.
Shutdown the PostgreSQL® 13 database:
1. If you run PostgreSQL® 13 as Windows service, then just stop the service postgresql-x64-13.
2. Else use pg_ctl from the PostgreSQL® 13 installation:
  Windows:
```
"C:\Program Files\PostgreSQL\13\bin\pg_ctl.exe" stop -D <old PGDATA directory for PostgreSQL® 13> -U postgres
```
  Linux:
```
 /usr/pgsql-13/bin/pg_ctl -D <old PGDATA directory for PostgreSQL® 13> stop
```

Upgrade your PostgreSQL® 13 database to PostgreSQL® 16 using

 pg_upgrade

from the PostgreSQL® 16 installation:

Windows:

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

Linux:

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

Change the
```
 pg_hba.conf 
```
file in the new PGDATA directory of PostgreSQL® 16 according to your needs (e.g. copy from old PGDATA directory of PostgreSQL® 13).
Start the new PostgreSQL® 16 server:
1. 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-16 is pointing to the correct PGDATA directory <new PGDATA directory for PostgreSQL® 16> and start the service.
2. If you start it manually, use:
```
 pg_ctl:
```
  Windows:
```
"C:\Program Files\PostgreSQL\16\bin\pg_ctl.exe" start -D <new PGDATA directory for PostgreSQL® 16> -w -U postgres
```
  Linux:
```
/usr/pgsql-16/bin/pg_ctl -D <new PGDATA directory for PostgreSQL® 16> start
```

After starting the server, finalize the upgrade by calling following command:

Windows:

"C:\Program Files\PostgreSQL\16\bin\psql.exe" -U postgres -d winccoa -c "analyze;"

Linux:

/usr/pgsql-16/bin/pg_ctl -U postgres -d winccoa -c "analyze;"