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:

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

2. Stop Patroni on all PostrgeSQL cluster nodes::

sudo systemctl stop patroni.service

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

sudo systemctl stop postgresql@10 postgresql@15

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

pg_lsclusters

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

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

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

3. Get the name of the PostgreSQL 10 cluster:

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

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

5. Start Patroni on the Leader node:

sudo systemctl start patroni.service

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

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

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

2. Ensure that PostgreSQL 15 is running correctly and the data is accessible.
3. 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

Was this helpful?

Thanks for your feedback!

Please specify why:

Recommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

How we can improve it?

YesNo

Please specify whyRecommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

Found a typo? Highlight the text, press ctrl + enter and notify us