search string side menu

Updating PostgreSQL cluster 12 and lower to version 18

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

Install PostgreSQL 18 on all nodes of the cluster where version 12 or lower is installed:

sudo apt install postgresql-18

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 12 or lower and PostgreSQL 18 on all cluster nodes:

sudo systemctl stop postgresql@12 postgresql@18

  1. Ensure that both PostgreSQL 12 or lower and PostgreSQL 18 clusters are stopped:

pg_lsclusters

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

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

Where:

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

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

Where:

  • /usr/lib/postgresql/12/bin and /usr/lib/postgresql/18/bin are the paths to the binary files of PostgreSQL versions 12 and 18.
  • /var/lib/postgresql/12/main and /var/lib/postgresql/18/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 18:

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/18/main
bin_dir: /usr/lib/postgresql/18/bin
config_dir: /etc/postgresql/18/main
...

  1. Get the name of the PostgreSQL 12 or lower cluster:

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

  1. On the Leader node in Patroni, remove the PostgreSQL 12 or lower cluster:

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

Where postgres-cluster is the name of the PostgreSQL 12 or lower 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 18 data directory:

sudo rm -rf /var/lib/postgresql/18/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 18 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/18/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 18, 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