Upgrading Percona Distribution for PostgreSQL from 16 to 17¶

This document describes the in-place upgrade of Percona Distribution for PostgreSQL using the pg_upgrade tool.

Important

When running a major upgrade on RHEL 8 and compatible derivatives, consider the following:

Percona Distribution for PostgreSQL 16.3, 15.7, 14.12, 13.15 and 12.18 include llvm packages 16.0.6, while its previous versions 16.2, 15.6, 14.11, 13.14, and 12.17 include llvm 12.0.1. Since llvm libraries differ and are not compatible, the direct major version upgrade from 15.6 to 16.3 may cause issues.

To ensure a smooth upgrade path, follow these steps:

Upgrade to the latest minor version within your current major version (e.g., from 15.6 to 15.7).
Then, perform the major upgrade to your desired version (e.g., from 15.7 to 16.3).

The in-place upgrade means installing a new version without removing the old version and keeping the data files on the server.

On Debian and Ubuntu using `apt`¶

Run all commands as root or via sudo:

Install Percona Distribution for PostgreSQL 17 packages.
- Install percona-release . If you have installed it before, update it to the latest version
- Enable Percona repository:
```
$ sudo percona-release setup ppg-17
```
- Install Percona Distribution for PostgreSQL 17 package:
```
$ sudo apt install percona-postgresql-17
```
Stop the postgresql service.
```
$ sudo systemctl stop postgresql.service
```
This stops both Percona Distribution for PostgreSQL 16 and 17.

Run the database upgrade.

Log in as the postgres user.

$ sudo su postgres

Change the current directory to the tmp directory where logs and some scripts will be recorded:

$ cd tmp/

Check the ability to upgrade Percona Distribution for PostgreSQL from 16 to 17:

$ /usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/16/main \
--new-datadir=/var/lib/postgresql/17/main  \
--old-bindir=/usr/lib/postgresql/16/bin  \
--new-bindir=/usr/lib/postgresql/17/bin  \
--old-options '-c config_file=/etc/postgresql/16/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/17/main/postgresql.conf' \
--check

The --check flag here instructs pg_upgrade to only check the upgrade without changing any data.

Sample output

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Upgrade the Percona Distribution for PostgreSQL

$ /usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/16/main \
--new-datadir=/var/lib/postgresql/17/main  \
--old-bindir=/usr/lib/postgresql/16/bin  \
--new-bindir=/usr/lib/postgresql/17/bin  \
--old-options '-c config_file=/etc/postgresql/16/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/17/main/postgresql.conf' \
--link

The --link flag creates hard links to the files on the old version cluster so you don’t need to copy data.

If you don’t wish to use the --link option, make sure that you have enough disk space to store 2 copies of files for both old version and new version clusters.

Go back to the regular user:

$ exit

The Percona Distribution for PostgreSQL 16 uses the 5432 port while the Percona Distribution for PostgreSQL 17 is set up to use the 5433 port by default. To start the Percona Distribution for PostgreSQL 17, swap ports in the configuration files of both versions.

$ sudo vim /etc/postgresql/17/main/postgresql.conf
$ port = 5433 # Change to 5432 here
$ sudo vim /etc/postgresql/16/main/postgresql.conf
$ port = 5432 # Change to 5433 here

Start the postgreqsl service.

$ sudo systemctl start postgresql.service

Check the postgresql version.
- Log in as a postgres user
```
$ sudo su postgres
```
- Check the database version
```
$ psql -c "SELECT version();"
```
After the upgrade, the Optimizer statistics are not transferred to the new cluster. Run the vaccumdb command to analyze the new cluster:
```
$ /usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages
```

Delete the old cluster’s data files:

$ ./delete_old_cluster.sh
$ sudo rm -rf /etc/postgresql/16/main
$ #Logout
$ exit

On Red Hat Enterprise Linux and CentOS using `yum`¶

Run all commands as root or via sudo:

Install Percona Distribution for PostgreSQL 17 packages
- Install percona-release
- Enable Percona repository:
```
$ sudo percona-release setup ppg-17
```
- Install Percona Distribution for PostgreSQL 17:
```
$ sudo yum install percona-postgresql17-server
```
Set up Percona Distribution for PostgreSQL 17 cluster
Log is as the postgres user
```
$ sudo su postgres
```

Set up locale settings

export LC_ALL="en_US.UTF-8"
export LC_CTYPE="en_US.UTF-8"

Initialize cluster with the new data directory

$ /usr/pgsql-17/bin/initdb -D /var/lib/pgsql/17/data

Stop the postgresql 16 service
```
$ systemctl stop postgresql-16
```

Run the database upgrade.

Log in as the postgres user

$ sudo su postgres

Check the ability to upgrade Percona Distribution for PostgreSQL from 16 to 17:

$ /usr/pgsql-17/bin/pg_upgrade \
--old-bindir /usr/pgsql-16/bin \
--new-bindir /usr/pgsql-17/bin  \
--old-datadir /var/lib/pgsql/16/data \
--new-datadir /var/lib/pgsql/17/data \
--check

The --check flag here instructs pg_upgrade to only check the upgrade without changing any data.

Sample output

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Upgrade the Percona Distribution for PostgreSQL

$ /usr/pgsql-17/bin/pg_upgrade \
--old-bindir /usr/pgsql-16/bin \
--new-bindir /usr/pgsql-17/bin  \
--old-datadir /var/lib/pgsql/16/data \
--new-datadir /var/lib/pgsql/17/data \
--link

The --link flag creates hard links to the files on the old version cluster so you don’t need to copy data. If you don’t wish to use the --link option, make sure that you have enough disk space to store 2 copies of files for both old version and new version clusters.

Start the postgresql 17 service.
```
$ systemctl start postgresql-17
```
Check postgresql status
```
$ systemctl status postgresql-17
```
After the upgrade, the Optimizer statistics are not transferred to the new cluster. Run the vaccumdb command to analyze the new cluster:
- Log in as the postgres user
```
$ sudo su postgres
```
- Run the script to analyze the new cluster:
```
$ /usr/pgsql-17/bin/vacuumdb --all --analyze-in-stages
```
Delete Percona Distribution for PostgreSQL 16 configuration files
```
$ ./delete_old_cluster.sh
```
Delete Percona Distribution old data files
```
$ rm -rf /var/lib/pgsql/16/data
```

Upgrading Percona Distribution for PostgreSQL from 16 to 17¶

On Debian and Ubuntu using apt¶

On Red Hat Enterprise Linux and CentOS using yum¶

On Debian and Ubuntu using `apt`¶

On Red Hat Enterprise Linux and CentOS using `yum`¶