HOWTO: PostgreSQL version upgrade: различия между версиями

Текущая версия от 06:59, 24 января 2024

This section describes how to upgrade PostgreSQL between major versions (e.g., from version 11 to version 16).

Upgrading within a version (e.g., from version 16.1 to version 16.2) is automatic and requires no additional steps.

We recommend reading the official upgrade instructions at postgresql.org.

In the Debian distribution and those based on it, there is a package postgresql-common that is installed automatically when the PostgreSQL package is installed. Postgresql-common includes tools to simplify working with PostgreSQL database clusters. This includes updating database clusters.

A database cluster in PostgreSQL is a server instance together with all the maintained databases, not a distributed system as it may seem.

The following example will show how to upgrade a database cluster from version 11 to version 16 using the tools in the postgresql-common package.

Upgrade order

Stop USERSIDE.
Upgrade the current version of postgresql within the major version (e.g., from 11.5 to 11.22).
Upgrade the postgis extension to the latest version (e.g. from 2.5 to 3.3.4). Also be sure to update the extension in the database itself.
Install a new version of postgresql without uninstalling the old one (e.g. 16.1).
Make a backup copy of the database and settings
Delete the database cluster created automatically by the script when installing the new version
Upgrade the database cluster of the old version to the cluster of the new version
Check USERSIDE operation
Remove the old version database cluster, obsolete packages and spare backups

Allocate time for this operation. It can take quite a long time depending on the size of the database and the performance of the server. Also, make sure you have the free space you will need to upgrade the cluster (at least as much as the current cluster, but more is better).

View video tutorial on asciinema.

Step-by-step instructions

Stop USERSIDE

sudo supervisorctl stop all
sudo systemctl stop php8.1-fpm

The php version is for example. Use the one you are using.

Comment out the startup of all modules and userside in crontab.

Upgrade the current version of postgresql within the major version

Check the current versions. Upgrade the current PostgreSQL and PostGIS packages:

sudo -u postgres psql -d userside -A -t -c "SELECT VERSION()"
sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"

sudo apt update
sudo apt install --only-upgrade -y postgresql-11 postgresql-11-postgis-2.5

Instead of 11 and 2.5, specify the versions you actually have installed.

Upgrade the postgis extension to the latest version

Be sure to upgrade the extension in the database as well!

sudo apt install -y postgresql-11-postgis-3

sudo -u postgres psql -d userside -A -t -c "ALTER EXTENSION postgis UPDATE"
sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"

Install a new version of postgresql without uninstalling the old one

sudo apt install -y postgresql-16 postgresql-16-postgis-3

Make a backup of the database and settings

The number 11 in the filename is the PostgreSQL version number to which this backup belongs.

sudo -u postgres pg_dump --no-acl -Fp -Z 5 userside --file=/backup/userside_emergency_11.sql.gz
sudo -u postgres pg_dump --no-acl -Fc userside --file=/backup/userside_emergency_11.dump
sudo tar czf /backup/postgres_config_emergency_11.tgz -C /etc/postgresql/11/main .

The /backup directory must exist and must be writable by all users.

Remove the database cluster created automatically by the script when installing a new version

Using the pg_lsclusters utility from postgresql-common, see what your database clusters look like:

sudo pg_lsclusters

The output of the command will be approximately as follows:

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11.log
16  main    5433 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16.log

The 16/main cluster was created automatically by the postgresql installation script. It needs to be stopped and deleted.

sudo pg_dropcluster 16 main --stop

Running the pg_lsclusters command again should show that you have one 11/main cluster left.

Perform an upgrade of the old version database cluster to the new version database cluster

Stop the postgresql service

sudo systemctl stop postgresql

Start the 11/main database cluster upgrade process using the pg_upgradecluster utility from the postgresql-common package. This may take a lot of time and disc space.

sudo pg_upgradecluster -m upgrade 11 main

After the upgrade operation is complete, you will see the new state of the clusters (the old version should be in down mode):

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11.log
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16.log

Start the postgres service

sudo systemctl start postgresql

Verify the operation of USERSIDE

First run the commands displaying the versions to verify that the database is now running on the new version database cluster:

sudo -u postgres psql -d userside -A -t -c "SELECT VERSION()"
sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"

You should see version 16 of postgresql and version 3.3.4 of postgis. Maybe by the time you read these lines, the postgis version will be newer.

Now start the FPM service and go to USERSIDE through your browser.

sudo systemctl start php8.1-fpm

Since the other services are stopped, you will probably see errors related to them, but the USERSIDE interface should open without database connection errors. If everything is successful, then uncomment the previously commented lines in crontab and start the external services under supervisor control:

sudo supervisorctl start all
sudo supervisorctl status

Remove the old version database cluster, obsolete packages, and spare backups

If USERSIDE is running correctly, and there are no database communication errors, you can remove the old cluster and packages. You can also optionally delete the emergency backups or wait a few days to make sure they are not needed.

sudo pg_dropcluster 11 main --stop
sudo apt purge -y postgresql-11 postgresql-11-postgis-2.5 postgresql-11-postgis-3
sudo apt autoremove -y
sudo rm /backup/userside_emergency_11* /backup/postgres_config_emergency_11.tgz
sudo rm -rf /var/lib/postgresql/11/main

@@ Строка 1: / Строка 1: @@
 [[HOWTO:_PostgreSQL_version_upgrade|en]] | [[HOWTO:_Обновление_версии_PostgreSQL|ru]]
-This section describes how to upgrade PostgreSQL server to a new major version (e.g. from 9.x to 11.x). You can also upgrade to the maximum version available at the time of the upgrade - they are all backwards compatible.
+This section describes how to upgrade PostgreSQL ''between major versions'' (e.g., from version 11 to version 16).
-We recommend that the server upgrade procedure is performed by a qualified person using the official instructions: https://www.postgresql.org/docs/11/upgrading.html.
+Upgrading within a version (e.g., from version 16.1 to version 16.2) ''is automatic and requires no additional steps.''
-Updating within a major version (e.g. from 11.1 to 11.3) is done without any additional steps.
+'''We recommend reading the official upgrade instructions''' at [https://www.postgresql.org/docs/current/upgrading.html postgresql.org].
-There are several ways of upgrading. Here we will cover the method with a full dump before the upgrade and then a recovery from the dump after the upgrade. The procedure will also be explained for Debian and its derivatives (Ubuntu etc.). For other distributions and operating systems the procedure is almost exactly the same, but other tools specific to a particular distribution or operating system are used.
+In the Debian distribution and those based on it, there is a package [https://packages.debian.org/sid/postgresql-common postgresql-common] that is installed automatically when the PostgreSQL package is installed. Postgresql-common includes tools to simplify working with PostgreSQL database clusters. This includes updating database clusters.
-It is assumed that PostgreSQL was installed following these instructions and PostgreSQL repositories were added to the system, providing access to different versions of PostgreSQL and extensions, and that the installation followed these instructions and installed the '''postgresql-9.6''' package rather than the ''postgresql metapackage''. If you have a metapackage installed, the upgrade should happen automatically when you upgrade the metapackage, but it is not certain that it will go through correctly. We recommend using specific packages (specifying the version) instead of metapackages.
+A [https://www.postgresql.org/docs/current/glossary.html database cluster] in PostgreSQL is a server instance together with all the maintained databases, not a distributed system as it may seem.
-If the upgrade process fails to successfully execute any command, please do not continue with the remaining items until the problem has been resolved. Unfortunately, we are not experts in all problems of PostgreSQL and the other services, so we will not always be able to give a qualified answer.
+The following example will show how to upgrade a database cluster from version 11 to version 16 using the tools in the '''postgresql-common''' package.
-. Stop all database-using systems from running. Stop php-fpm or apache2, and temporarily disable tasks in cron and stop usm_* modules from running.
+== Upgrade order ==
+# Stop USERSIDE.
+# Upgrade the current version of postgresql within the major version (e.g., from 11.5 to 11.22).
+# Upgrade the postgis extension to the latest version (e.g. from 2.5 to 3.3.4). Also be sure to update the extension in the database itself.
+# Install a new version of postgresql without uninstalling the old one (e.g. 16.1).
+# Make a backup copy of the database and settings
+# Delete the database cluster created automatically by the script when installing the new version
+# Upgrade the database cluster of the old version to the cluster of the new version
+# Check USERSIDE operation
+# Remove the old version database cluster, obsolete packages and spare backups
-. Check which versions of PostgreSQL and PostGIS you currently have installed:
+Allocate time for this operation. It can take quite a long time depending on the size of the database and the performance of the server. Also, make sure you have the free space you will need to upgrade the cluster (at least as much as the current cluster, but more is better).
- sudo -u postgres psql -d userside -A -c "SELECT VERSION()"
- sudo -u postgres psql -d userside -A -c "SELECT PostGIS_full_version()"
-. Upgrade the current versions, and if you have PostGIS 2.4 installed, install PostGIS 2.5 and upgrade this extension in the database, then make sure the extension in the database is upgraded:
+[https://asciinema.org/a/do2AE0z30mg5JLIuI9zhGTPoR View video tutorial on asciinema].
- sudo apt-get update
- sudo apt-get --only-upgrade install postgresql-9.6 postgresql-9.6-postgis-2.4 -y
- sudo apt-get install postgresql-9.6-postgis-2.5 -y
- sudo -u postgres psql -d userside -A -c "ALTER EXTENSION postgis UPDATE"
- sudo -u postgres psql -d postgres -A -c "ALTER EXTENSION postgis UPDATE"
- sudo -u postgres psql -d userside -A -c "SELECT PostGIS_full_version()"
- sudo -u postgres psql -d postgres -A -c "SELECT PostGIS_full_version()"
-. Install the new version of PostgreSQL and PostGIS without removing the old one(!).
+== Step-by-step instructions ==
- sudo apt-get install postgresql-11 postgresql-11-postgis-2.5 -y
-. Perform a backup of all server databases (in this case the latest version of the pg_dumpall utility will be used, but the database will still be used as before):
+=== Stop USERSIDE ===
- sudo -u postgres pg_dumpall > /tmp/dump_9.6_before_update.dump
+<pre>
+sudo supervisorctl stop all
+sudo systemctl stop php8.1-fpm
+</pre>
+The php version is for example. Use the one you are using.
-. Look at how your clusters look. You should now see two versions, both in an online state:
+Comment out the startup of all modules and userside in crontab.
- pg_lsclusters
-You should see a table:
- Ver Cluster Port Status Owner    Data directory
-.6 main    5432 online postgres /var/lib/postgresql/9.6/main
-  main    5433 online postgres /var/lib/postgresql/11/main
-. When installing PostgreSQL, the installer automatically creates a cluster with the configuration and databases so that you can start working with the database immediately. In case of an upgrade this is an unnecessary operation and the cluster for version 11 needs to be removed. We will create it based on the version 9.6 cluster:
+=== Upgrade the current version of postgresql within the major version ===
- sudo pg_dropcluster 11 main --stop
+Check the current versions. Upgrade the current PostgreSQL and PostGIS packages:
+<pre>
+sudo -u postgres psql -d userside -A -t -c "SELECT VERSION()"
+sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"
-. Stop the service
+sudo apt update
- sudo systemctl stop postgresql
+sudo apt install --only-upgrade -y postgresql-11 postgresql-11-postgis-2.5
+</pre>
+Instead of 11 and 2.5, specify the versions you actually have installed.
-. Make a backup of the current directories with database files and configuration files. Be careful, the database files may take up a lot of storage space, so it may be best to choose a different location for them. Later, if the upgrade is successful, these directories will have to be removed:
+=== Upgrade the postgis extension to the latest version ===
- sudo cp -r /var/lib/postgresql/9.6/main/ /tmp/pg9.6-lib
+Be sure to upgrade the extension in the database as well!
- sudo cp -r /etc/postgresql/9.6/main/ /tmp/pg9.6-etc
+<pre>
+sudo apt install -y postgresql-11-postgis-3
-. Start the procedure to create a new cluster based on the old cluster (cluster version upgrade):
+sudo -u postgres psql -d userside -A -t -c "ALTER EXTENSION postgis UPDATE"
- sudo pg_upgradecluster -m upgrade 9.6 main
+sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"
+</pre>
-. Start the service:
+=== Install a new version of postgresql without uninstalling the old one ===
- sudo systemctl start postgresql
+<pre>
+sudo apt install -y postgresql-16 postgresql-16-postgis-3
+</pre>
-. Look at how your clusters look:
+=== Make a backup of the database and settings ===
- pg_lsclusters
+The number 11 in the filename is the PostgreSQL version number to which this backup belongs.
-Now you should see a table like this one, with version 9.6 in the down state:
+<pre>
- Ver Cluster Port Status Owner    Data directory
+sudo -u postgres pg_dump --no-acl -Fp -Z 5 userside --file=/backup/userside_emergency_11.sql.gz
-.6 main    5433 down   postgres /var/lib/postgresql/9.6/main
+sudo -u postgres pg_dump --no-acl -Fc userside --file=/backup/userside_emergency_11.dump
-  main    5432 online postgres /var/lib/postgresql/11/main
+sudo tar czf /backup/postgres_config_emergency_11.tgz -C /etc/postgresql/11/main .
+</pre>
+The /backup directory must exist and must be writable by all users.
-. Check the versions:
+=== Remove the database cluster created automatically by the script when installing a new version ===
-  sudo -u postgres psql -d userside -A -c "SELECT VERSION()"
+Using the <code>pg_lsclusters</code> utility from postgresql-common, see what your database clusters look like:
-  sudo -u postgres psql -d userside -A -c "SELECT PostGIS_full_version()"
+<pre>
+sudo pg_lsclusters
+</pre>
+The output of the command will be approximately as follows:
+<pre>
+Ver Cluster Port Status Owner    Data directory              Log file
+main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11.log
+main    5433 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16.log
+</pre>
+The 16/main cluster was created automatically by the postgresql installation script. It needs to be stopped and deleted.
+<pre>
+sudo pg_dropcluster 16 main --stop
+</pre>
+Running the <code>pg_lsclusters</code> command again should show that you have one '''11/main''' cluster left.
-. Run all the services you stopped in step 1 and check that USERSIDE and modules are working. Check that everything is working correctly.
+=== Perform an upgrade of the old version database cluster to the new version database cluster ===
+Stop the postgresql service
+<pre>
+sudo systemctl stop postgresql
+</pre>
+Start the '''11/main''' database cluster upgrade process using the <code>pg_upgradecluster</code> utility from the postgresql-common package. This may take a lot of time and disc space.
+<pre>
+sudo pg_upgradecluster -m upgrade 11 main
+</pre>
+After the upgrade operation is complete, you will see the new state of the clusters (the old version should be in '''down''' mode):
+<pre>
+Ver Cluster Port Status Owner    Data directory              Log file
+  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11.log
+  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16.log
+</pre>
+Start the postgres service
+<pre>
+sudo systemctl start postgresql
+</pre>
-. After you have ensured that everything works correctly, you can remove the temporary backups as well as the old packages:
+=== Verify the operation of USERSIDE ===
- sudo pg_dropcluster 9.6 main --stop
+First run the commands displaying the versions to verify that the database is now running on the new version database cluster:
- sudo apt-get purge postgresql-9.6 postgresql-9.6-postgis-2.4 postgresql-9.6-postgis-2.5 -y
+<pre>
- sudo apt autoremove -y
+sudo -u postgres psql -d userside -A -t -c "SELECT VERSION()"
- sudo rm -rf /tmp/pg9.6-* /tmp/dump_9.6_before_update.dump
+sudo -u postgres psql -d userside -A -t -c "SELECT PostGIS_full_version()"
+</pre>
+You should see version 16 of postgresql and version 3.3.4 of postgis. Maybe by the time you read these lines, the postgis version will be newer.
-PostgreSQL upgrade completed.
+Now start the FPM service and go to USERSIDE through your browser.
+<pre>
+sudo systemctl start php8.1-fpm
+</pre>
+Since the other services are stopped, you will probably see errors related to them, but the USERSIDE interface should open without database connection errors.
+If everything is successful, then uncomment the previously commented lines in crontab and start the external services under supervisor control:
+<pre>
+sudo supervisorctl start all
+sudo supervisorctl status
+</pre>
-Video guide: https://youtu.be/c9ULVSz8yQM
+=== Remove the old version database cluster, obsolete packages, and spare backups ===
+If USERSIDE is running correctly, and there are no database communication errors, you can remove the old cluster and packages. You can also optionally delete the emergency backups or wait a few days to make sure they are not needed.
+<pre>
+sudo pg_dropcluster 11 main --stop
+sudo apt purge -y postgresql-11 postgresql-11-postgis-2.5 postgresql-11-postgis-3
+sudo apt autoremove -y
+sudo rm /backup/userside_emergency_11* /backup/postgres_config_emergency_11.tgz
+sudo rm -rf /var/lib/postgresql/11/main
+</pre>