How to upgrade Postgres minor version from 14.x to 14.4 in Ubuntu 20.04 LTS

All we need is an easy explanation of the problem, so here it is.

In 2022-06-16 Postgres released an out-of-cycle version 14.4, which fixes possible corruption on indexes created with the concurrently option in versions 14.0 to 14.3.

Needless to say, this is a very important upgrade that should be done as soon as possible.

Since I’m a web dev that also happen to run his own servers for my side projects, I’d appreciate validation for the steps upgrading from 14.2 to 14.4.

  1. Since this is a minor upgrade, there’s no need to dump and reimport; simply "replacing the binaries" is enough. Does this mean I can anticipate a downtime of less < 5 minutes after stopping the server? (just the time for the new version install?)

  2. My server already has the http://apt.postgresql.org/pub/repos/apt/ apt repository added. After a sudo apt update, how can I make sure that a sudo apt install postgresql will be installing the 14.4 version before I run it? (I don’t want it trying to upgrade to 14.3 or some other stale version)

  3. After stopping the server with a sudo systemctl stop postgresql, is a simple sudo apt install postgresql enough for this upgrade?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Q: Does this mean I can anticipate a downtime of less < 5 minutes after stopping the server? (just the time for the new version install?)

Yes, but you don’t need to stop the server. The upgrade procedure will do it.

Q: After a sudo apt update, how can I make sure that a sudo apt install postgresql will be installing the 14.4 version before I run it?

After apt update has fetched the most recent versions, the candidate version to install can be seen with apt policy.
For instance on Ubuntu 20.04 before the upgrade:

$ apt policy postgresql-14
postgresql-14:
  Installed: 14.3-1.pgdg20.04+1
  Candidate: 14.4-1.pgdg20.04+1
  Version table:
     14.4-1.pgdg20.04+1 500
        500 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages
 *** 14.3-1.pgdg20.04+1 100
        100 /var/lib/dpkg/status

After stopping the server with a sudo systemctl stop postgresql, is a simple sudo apt install postgresql enough for this upgrade?

The server package is postgresql-14, not postgresql, which is a "metapackage". Just doing apt install postgresql will normally not upgrade postgresql-14.

Here’s the output when upgrading postgresql-14 to 14.4:

$ sudo apt install postgresql-14
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be upgraded:
  postgresql-14
1 upgraded, 0 newly installed, 0 to remove and 10 not upgraded.
Need to get 15.8 MB of archives.
After this operation, 12.3 kB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-14 amd64 14.4-1.pgdg20.04+1 [15.8 MB]
Fetched 15.8 MB in 5s (2946 kB/s)        
Preconfiguring packages ...
(Reading database ... 349578 files and directories currently installed.)
Preparing to unpack .../postgresql-14_14.4-1.pgdg20.04+1_amd64.deb ...
Unpacking postgresql-14 (14.4-1.pgdg20.04+1) over (14.3-1.pgdg20.04+1) ...
Setting up postgresql-14 (14.4-1.pgdg20.04+1) ...
Processing triggers for postgresql-common (241.pgdg20.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
  fr
Removing obsolete dictionary files:

You can check with sudo tail /var/log/postgresql/postgresql-14-main.log that the server was stopped and restarted with the new version. The output will typically look like this:

2022-06-17 11:36:58.794 CEST [26346] LOG:  background worker "logical replication launcher" (PID 26353) exited with exit code 1
2022-06-17 11:36:58.810 CEST [26348] LOG:  shutting down
2022-06-17 11:36:59.364 CEST [26346] LOG:  database system is shut down
2022-06-17 11:37:02.173 CEST [15840] LOG:  starting PostgreSQL 14.4 (Ubuntu 14.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
2022-06-17 11:37:02.173 CEST [15840] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-06-17 11:37:02.173 CEST [15840] LOG:  listening on IPv6 address "::", port 5432
2022-06-17 11:37:02.210 CEST [15840] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-06-17 11:37:02.304 CEST [15842] LOG:  database system was shut down at 2022-06-17 11:36:59 CEST
2022-06-17 11:37:02.367 CEST [15840] LOG:  database system is ready to accept connections

Comparing the shutdown and startup times in the above output, in that case it was only a few seconds.

Method 2

You can look at your installed PostgreSQL packages like this

dpkg -l 'postgres*'

You can upgrade a package with

apt-get update
apt-get upgrade packagename

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply