October 15, 2023
Summary: Every 3 months, the PostgreSQL community releases minor version updates that are available for all the supported PostgreSQL versions. Let’s say that we are using PostgreSQL 13.1 but a new minor version, PostgreSQL 13.2, has been released. In this recipe, we shall see how a minor version upgrade can be performed when using PostgreSQL 13.
Table of Contents
Major versions upgrade usually change the internal format of system catalogs and data files. In order to perform major upgrades, a dump/reload of the database or use of the pg_upgrade program is required.
Updating a PostgreSQL minor version does not normally require a dump and restore; you can stop the database server, install the updated binaries, and restart the server. However while updating will always contain some level of risk, PostgreSQL minor releases fix only frequently-encountered bugs, security issues, and data corruption problems to reduce the risk associated with updating.
Getting ready
In order to perform a minor version PostgreSQL 13 update, we should have a database server with an already-running PostgreSQL 13 cluster. To use yum or apt, we should have the yum or apt repository configured.
How to do it…
We will do this using the following steps:
1. Shut down the PostgreSQL cluster:
$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data stop
2. Get all the Postgres packages installed in the existing PostgreSQL server:
$ rpm -qa | grep postgres
postgresql13-13.1-3PGDG.rhel7.x86_64
postgresql13-server-13.1-3PGDG.rhel7.x86_64
postgresql13-libs-13.1-3PGDG.rhel7.x86_64
postgresql13-contrib-13.1-3PGDG.rhel7.x86_64
3. Install the latest minor version using yum or apt for all the packages installed.
For CentOS/Red Hat, use the following:
$ sudo yum install postgresql13-server postgresql13-contrib
For Ubuntu/Debian, use the following:
$ sudo apt-get update postgresql13-server postgresql13-contrib
4. Start the PostgreSQL cluster:
$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data start
5. Validate the minor version update:
$ /usr/pgsql-13/bin/psql -c "select version()"
This results in the following output:
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
How it works…
Updating a PostgreSQL minor version is a very simple procedure. The first step is to shut down the PostgreSQL cluster running on the database servers, as seen in step 1. Once the database is down, we need to look at the packages that are installed and use yum or apt to get a list of the latest PostgreSQL packages, as seen in step 2. After installing the latest packages, we can start the Postgres cluster and notice that the version has been updated successfully, as seen in steps 3 and 4. The application can restart the connectivity to the databases once step 4 is completed successfully.