Upgrading Percona Distribution for PostgreSQL from 12 to 13¶
This document describes the in-place upgrade of Percona Distribution for PostgreSQL using the pg_upgrade
tool.
The in-place upgrade means installing a new version without removing the old version and keeping the data files on the server.
Similar to installing, we recommend you to upgrade Percona Distribution for PostgreSQL from Percona repositories.
Important
A major upgrade is a risky process because of many changes between versions and issues that might occur during or after the upgrade. Therefore, make sure to back up your data first. The backup tools are out of scope of this document. Use the backup tool of your choice.
The general in-place upgrade flow for Percona Distribution for PostgreSQL is the following:
-
Install Percona Distribution for PostgreSQL 13 packages.
-
Stop the PostgreSQL service.
-
Check the upgrade without modifying the data.
-
Upgrade Percona Distribution for PostgreSQL.
-
Start PostgreSQL service.
-
Execute the analyze_new_cluster.sh script to generate statistics so the system is usable.
-
Delete old packages and configuration files.
The exact steps may differ depending on the package manager of your operating system.
On Debian and Ubuntu using apt
¶
Important
Run all commands as root or via sudo.
-
Install Percona Distribution for PostgreSQL 13 packages.
- Enable Percona repository using the percona-release utility:
$ sudo percona-release setup ppg-13
- Install Percona Distribution for PostgreSQL 13 package:
$ sudo apt install percona-postgresql-13
- Install the components:
$ sudo apt install percona-postgresql-13-repack \ percona-postgresql-13-pgaudit \ percona-pgbackrest \ percona-patroni \ percona-pgbadger \ percona-pgaudit13-set-user \ percona-pgbadger \ percona-postgresql-13-wal2json \ percona-pg-stat-monitor13 \ percona-postgresql-contrib
Seealso
Percona Documentation:
-
Stop the
postgresql
service.$ sudo systemctl stop postgresql.service
This stops both Percona Distribution for PostgreSQL 12 and 13.
-
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 12 to 13:
$ /usr/lib/postgresql/13/bin/pg_upgrade --old-datadir=/var/lib/postgresql/12/main \ --new-datadir=/var/lib/postgresql/13/main \ --old-bindir=/usr/lib/postgresql/12/bin \ --new-bindir=/usr/lib/postgresql/13/bin \ --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \ --check
The
--check
flag here instructspg_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/13/bin/pg_upgrade --old-datadir=/var/lib/postgresql/12/main \ --new-datadir=/var/lib/postgresql/13/main \ --old-bindir=/usr/lib/postgresql/12/bin \ --new-bindir=/usr/lib/postgresql/13/bin \ --old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/13/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 12 uses the
5432
port while the Percona Distribution for PostgreSQL 13 is set up to use the5433
port by default. To start the Percona Distribution for PostgreSQL 13, swap ports in the configuration files of both versions.
$ sudo vim /etc/postgresql/13/main/postgresql.conf $ port = 5433 # Change to 5432 here $ sudo vim /etc/postgresql/12/main/postgresql.conf $ port = 5432 # Change to 5433 here
- Log in as the
-
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();"
-
Run the
analyze_new_cluster.sh
script$ tmp/analyze_new_cluster.sh $ #Logout $ exit
-
Delete Percona Distribution for PostgreSQL 12 packages and configuration files
- Remove packages
$ sudo apt remove percona-postgresql-12* percona-pgbackrest percona-patroni percona-pg-stat-monitor12 percona-pgaudit12-set-user percona-pgbadger percona-pgbouncer percona-postgresql-12-wal2json
- Remove old files
$ rm -rf /etc/postgresql/12/main
On Red Hat Enterprise Linux and CentOS using yum
¶
Important
Run all commands as root or via sudo.
-
Install Percona Distribution for PostgreSQL 13 packages
- Enable Percona repository using the percona-release utility:
$ sudo percona-release setup ppg-13
- Install Percona Distribution for PostgreSQL 13:
$ sudo yum install percona-postgresql13-server
- Install components:
$ sudo yum install percona-pgaudit \ percona-pgbackrest \ percona-pg_repack13 \ percona-patroni \ percona-pg-stat-monitor13 \ percona-pgbadger \ percona-pgaudit13_set_user \ percona-pgbadger \ percona-wal2json13 \ percona-postgresql13-contrib
Seealso
Percona Documentation:
-
Set up Percona Distribution for PostgreSQL 13 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-13/bin/initdb -D /var/lib/pgsql/13/data
-
Stop the
postgresql
12 service$ sudo systemctl stop postgresql-12
-
Run the database upgrade.
- Log in as the
postgres
user
$ sudo su postgres
- Check the ability to upgrade Percona Distribution for PostgreSQL from 12 to 13:
$ /usr/pgsql-13/bin/pg_upgrade \ --old-bindir /usr/pgsql-12/bin \ --new-bindir /usr/pgsql-13/bin \ --old-datadir /var/lib/pgsql/12/data \ --new-datadir /var/lib/pgsql/13/data \ --link --check
The
--check
flag here instructspg_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-13/bin/pg_upgrade \ --old-bindir /usr/pgsql-12/bin \ --new-bindir /usr/pgsql-13/bin \ --old-datadir /var/lib/pgsql/12/data \ --new-datadir /var/lib/pgsql/13/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. - Log in as the
-
Start the
postgresql
13 service.$ systemctl start postgresql-13
-
Check postgresql status
$ systemctl status postgresql-13
-
Run the
analyze_new_cluster.sh
script- Log in as the postgres user
$ sudo su postgres
- Run the script
$ ./analyze_new_cluster.sh
-
Delete Percona Distribution for PostgreSQL 12 configuration files
$ ./delete_old_cluster.sh
-
Delete Percona Distribution for PostgreSQL 12 packages
- Remove packages
$ sudo yum -y remove percona-postgresql12*
- Remove old files
$ rm -rf /var/lib/pgsql/12/data