search string side menu

BRIX On-Premises > Prepare infrastructure > Databases > Discontinued PostgreSQL support / Updating PostgreSQL cluster 10 to 15

Updating PostgreSQL cluster 10 to 15

When updating PostgreSQL to a major version, the pg_upgrade utility is used. This utility allows you to update the data stored in PostgreSQL data files to a newer version of PostgreSQL.

Updating a PostgreSQL cluster consists of six steps:

  1. Database backup.
  2. Installation of PostgreSQL 15.
  3. Updating data in the database directory.
  4. Starting the Patroni cluster.
  5. Post-installation check.
  6. Cleaning up old data.

Step 1. Database backup

Before carrying out any actions related to the update, create a backup of the PostgreSQL data and configuration. Read more about how to do this in Backup and recover databases.

начало внимание

If there are third-party databases on the PostgreSQL server, backups of these databases should also be created.

конец внимание

Backup of the PostgreSQL database without using the elma365-backupper utility:

You can create an elma365 database backup using pg_dump :

sudo -u postgres pg_dump elma365 -Fc > path/to/elma365.bak 

Step 2. Installation of PostgreSQL 15

Install PostgreSQL 15 on all nodes of the cluster where version 10 is installed:

sudo apt install postgresql-15

Step 3. Updating data in the database directory

  1. In the PostrgeSQL cluster, identify the Leader node::

patronictl -c /etc/patroni/config.yml list

  1. Stop Patroni on all PostrgeSQL cluster nodes::

sudo systemctl stop patroni.service

  1. Stop PostgreSQL 10 and PostgreSQL 15 on all cluster nodes:

sudo systemctl stop postgresql@10 postgresql@15

  1. Ensure that both PostgreSQL 10 and PostgreSQL 15 clusters are stopped:

pg_lsclusters

  1. On the Leader node, perform a compatibility check of database configurations:

sudo -iu postgres /usr/lib/postgresql/15/bin/pg_upgrade \
-o "-c config_file=/etc/postgresql/10/main/postgresql.conf"\
-O "-c config_file=/etc/postgresql/15/main/postgresql.conf" \
--old-datadir=/var/lib/postgresql/10/main/ \
--new-datadir=/var/lib/postgresql/15/main/ \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/15/bin --check

where:

  • /usr/lib/postgresql/10/bin and /usr/lib/postgresql/15/bin are the paths to the binary files of PostgreSQL versions 10 and 15;
  • /var/lib/postgresql/10/main and /var/lib/postgresql/15/main are the paths to the data directories.
  1. On the Leader node, transfer data from PostgreSQL 10 to PostgreSQL 15:

sudo -iu postgres /usr/lib/postgresql/15/bin/pg_upgrade \
-o "-c config_file=/etc/postgresql/10/main/postgresql.conf" \
-O "-c config_file=/etc/postgresql/15/main/postgresql.conf" \
--old-datadir=/var/lib/postgresql/10/main/ \
--new-datadir=/var/lib/postgresql/15/main/ \
--old-bindir=/usr/lib/postgresql/10/bin \
--new-bindir=/usr/lib/postgresql/15/bin

where:

  • /usr/lib/postgresql/10/bin and /usr/lib/postgresql/15/bin are the paths to the binary files of PostgreSQL versions 10 and 15;
  • /var/lib/postgresql/10/main and /var/lib/postgresql/15/main are the paths to the data directories.

Step 4. Starting the Patroni cluster

  1. Open the Patroni configuration file on the path to PostgreSQL 15:

sudo nano /etc/patroni/config.yml

  1. Replace parameters data_dir, bin_dir and config_dir on each PostgreSQL cluster node, for example:

...
data_dir: /var/lib/postgresql/15/main
bin_dir: /usr/lib/postgresql/15/bin
config_dir: /etc/postgresql/15/main
...

  1. Get the name of the PostgreSQL 10 cluster:

sudo cat /etc/patroni/config.yml | grep "scope" | awk '{ print $2 }'

  1. On the Leader node in Patroni, remove the PostgreSQL 10 cluster:

patronictl -c /etc/patroni/config.yml remove postgres-cluster

where:

  • postgres-cluster is the name of the PostgreSQL 10 cluster obtained in the previous step.
  1. Start Patroni on the Leader node:

sudo systemctl start patroni.service

  1. On Replica nodes, delete the PostgreSQL 15 data directory:

sudo rm -rf /var/lib/postgresql/15/main

  1. On Replica nodes, start Patroni:

sudo systemctl start patroni.service

Step 5. Post-installation check

  1. Check the status of the cluster:

patronictl -c /etc/patroni/config.yml list

  1. Ensure that PostgreSQL 15 is running correctly and the data is accessible.
  2. For database optimization, execute the command on the Leader node:

sudo -u postgres /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

Step 6. Cleaning up old data

начало внимание

Before starting the cleaning process, ensure you have a backup of the data and configuration.

конец внимание

If necessary, after successful updating and checking PostgreSQL 15, you can clean up old data:

sudo -u postgres ./delete_old_cluster.sh

upgrading-postgresql-standalone-10-to-15.html upgrading-postgresql-container-10-to-15.html