0

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?

2 Answers 2

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.

1
  • Daniel, you are a monster. Thank you very much.
    – sandre89
    Jun 17, 2022 at 13:02
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.