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

Материал из WiKi - UserSide
Нет описания правки
Нет описания правки
Строка 1: Строка 1:
[[HOWTO:_PostgreSQL_version_upgrade|en]] | [[HOWTO:_Обновление_версии_PostgreSQL|ru]]
[[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/16/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 database cluster ([https://www.postgresql.org/docs/16/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.


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


2. 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()"


3. 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()"


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


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


6. 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
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main
11  main    5433 online postgres /var/lib/postgresql/11/main


7. 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()"


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


9. 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
 
10. Start the procedure to create a new cluster based on the old cluster (cluster version upgrade):
sudo pg_upgradecluster -m upgrade 9.6 main
 
11. Start the service:
sudo systemctl start postgresql
 
12. Look at how your clusters look:
pg_lsclusters
Now you should see a table like this one, with version 9.6 in the down state:
Ver Cluster Port Status Owner    Data directory
9.6 main    5433 down  postgres /var/lib/postgresql/9.6/main
11  main    5432 online postgres /var/lib/postgresql/11/main
 
13. Check the versions:
sudo -u postgres psql -d userside -A -c "SELECT VERSION()"
sudo -u postgres psql -d userside -A -c "SELECT PostGIS_full_version()"
 
14. Run all the services you stopped in step 1 and check that USERSIDE and modules are working. Check that everything is working correctly.
 
15. After you have ensured that everything works correctly, you can remove the temporary backups as well as the old packages:
sudo pg_dropcluster 9.6 main --stop
sudo apt-get purge postgresql-9.6 postgresql-9.6-postgis-2.4 postgresql-9.6-postgis-2.5 -y
sudo apt autoremove -y
sudo rm -rf /tmp/pg9.6-* /tmp/dump_9.6_before_update.dump
 
PostgreSQL upgrade completed.
 
Video guide: https://youtu.be/c9ULVSz8yQM

Версия от 16:09, 22 января 2024

en | ru

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

  1. Stop USERSIDE.
  2. Upgrade the current version of postgresql within the major version (e.g., from 11.5 to 11.22).
  3. 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.
  4. Install a new version of postgresql without uninstalling the old one (e.g. 16.1).
  5. Make a backup copy of the database and settings
  6. Delete the database cluster created automatically by the script when installing the new version
  7. Upgrade the database cluster of the old version to the cluster of the new version
  8. Check USERSIDE operation
  9. 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!

Источник — https://wiki.userside.eu/index.php?title=HOWTO:_PostgreSQL_version_upgrade&oldid=17184