Skip to content

CentOS 7 default psql version is 9.2.24

The default psql (postgresql command line client) included in CentOS 7 distribution is 9.2.24 but other version are available as described at Linux downloads (Red Hat family) :

PostgreSQL is available on these platforms by default. However, each version of the platform normally "snapshots" a specific version of PostgreSQL that is then supported throughout the lifetime of this platform. Since this can often mean a different version than preferred, the PostgreSQL project provides a repository of packages of all supported versions for the most common distributions.

Which version of PostgreSQL you get will depend on the version of the distribution:

RHEL / CentOS / SL / OL 7 9.2 (also supplies package rh-postgresql10, rh-postgresql96, rh-postgresql95 and rh-postgresql94 via SCL)

Postgresql 9.2 has been deprecated more than 5 years ago

According to the PostgreSQL official versioning policy, version 9.2.24 was the final minor (9.2 EOL) published in November 9, 2017

There are no security patches and bug fixes of any sort since then. Keep using such a long time deprecated version pose a risk under many aspects.

Using different versions between PostgreSQL client and server

What is the common knowledge about using different versions between client and server is explained by some official Notes in the psql docs:

psql works best with servers of the same or an older major version. Backslash commands are particularly likely to fail if the server is of a newer version than psql itself. However, backslash commands of the \d family should work with servers of versions back to 9.2, though not necessarily with servers newer than psql itself. The general functionality of running SQL commands and displaying query results should also work with servers of a newer major version, but this cannot be guaranteed in all cases.

If you want to use psql to connect to several servers of different major versions, it is recommended that you use the newest version of psql. Alternatively, you can keep around a copy of psql from each major version and be sure to use the version that matches the respective server. But in practice, this additional complication should not be necessary.

DBOD users are free to use what they want but our recommendation is to use a psql (client) version which is the same or higher compared to the server.

Warning

Keep using older psql versions may be possible but it is prone to fail at any time, with unpredictable results/consequences, and thus is strongly discouraged and at your own risk!

This is because the client, being older, does not know of the newer features of the server (like for example the scram-sha-256 password encryption), and this can produce unpredictable results, although it may work if you are using features which have not changed.

What can be done to deal with an old psql version

  1. Manual installation of a newer version of the PostgreSQL Client package 1
  2. Use a newer version of psql from the LXPLUS Red Hat Enterprise Linux 8 or 9 clusters 2
  3. Make available a newer version of psql in the LXPLUS CentOS 7 cluster using SCL 3 (Red Hat Software Collections)
  4. Ensure a specific version of the client package is installed by puppet 4
  5. WORKAROUND (up to PG13) AT YOUR OWN RISK: use a recent psql from any host to change the passwords to scram-sha-256, run the upgrade, revert the passwords to md5, keep using the old psql as before

What we should do to fix and avoid future issues

Configure any host/image such that the default psql is linked to the latest psql version available for the platform/distribution, instead of the oldest version, and whenever possible, keep adding new versions when they are published.

  1. Manual installation of PostgreSQL Client
    sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-client
     ↩

  2. Use a newer version of PostgreSQL Client from the lxplus8 or lxplus9 clusters
    [localhost ~]$ ssh lxplus8.cern.ch
[@lxplus807 ~]$ psql --version
psql (PostgreSQL) 15.3

[localhost ~]$ ssh lxplus9.cern.ch
[@lxplus913 ~]$ psql --version
psql (PostgreSQL) 15.3
     ↩

  3. Make available a newer version of PostgreSQL Client in LXPLUS CentOS 7 cluster using SCL
    [@lxplus796 ~]$ psql --version
psql (PostgreSQL) 9.2.24

[@lxplus796 ~]$ scl enable rh-postgresql13 bash

[@lxplus796 ~]$ psql --version
psql (PostgreSQL) 13.7
     ↩

  4. It seems there is a puppet repo which may be used to make sure the desired version of PostgreSQL client is installed. DBOD Team is working at developing a reusable recipe to share with all users. ↩