HOWTO: PostgreSQL version upgrade

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

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.

Updating within a major version (e.g. from 11.1 to 11.3) is done without any additional steps.

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.

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.

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.

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.

2. Check which versions of PostgreSQL and PostGIS you currently have installed:

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:

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

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):

sudo -u postgres pg_dumpall > /tmp/dump_9.6_before_update.dump

6. Look at how your clusters look. You should now see two versions, both in an online state:

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:

sudo pg_dropcluster 11 main --stop

8. Stop the service

sudo systemctl stop postgresql

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:

sudo cp -r /var/lib/postgresql/9.6/main/ /tmp/pg9.6-lib
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