What version is the server?
PostgreSQL has internal version numbers for the data file format, database catalog layout, and crash recovery format. Each of these is checked as the server runs to ensure that the data doesn't become corrupt. PostgreSQL doesn't change these internal formats for a single minor release; they only change across major releases.
From a user's perspective, each release differs in terms of the way the server behaves. If you know your application well, then it should be possible to assess the differences simply by reading the release notes for each version. In many cases, a retest of the application is the safest thing to do.
If you experience any general problems related to setup and configuration with your database, then you'll need to double-check which version of the server you have. This will help you to report a fault or to consult the correct version of the manual.
How to do it…
We will find out the version by querying the database server directly:
- Connect to the database and issue the following command:
postgres=# SELECT version();
- You'll get a response that looks something like this:
PostgreSQL 14.0 (Debian 14.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
That's probably too much information all at once!
Another way of checking the version number in your programs is as follows:
postgres=# SHOW server_version;
The preceding shows the version in text form, so you may also want a numerical value that is easier to compare using a greater than symbol, in which case you execute this command instead:
postgres=# SHOW server_version_num;
Another alternative is via command-line utilities, such as this:
bash # psql --version psql (PostgreSQL) 14.0 (Debian 14.0-1.pgdg100+1)
However, be wary that this shows the client software version number, which may differ from the server software version number. This will usually be reported to you so that you're aware.
How it works…
The current PostgreSQL server version format is composed of two numbers; the first number indicates the major release, and the second one denotes subsequent maintenance releases for that major release. It is common to mention just the major release when discussing what features are supported, as they are unchanged on a maintenance release.
14.0 is the first release of PostgreSQL 14, and subsequent maintenance releases will be 14.1, 14.2, 14.3, and so on. In the preceding example, we see that 14.0 is the version of that PostgreSQL server.
For each major release, there is a separate version of the manual, since the feature set is not the same. If something doesn't work exactly the way you think it should, make sure you are consulting the correct version of the manual.
There's more…
Prior to release 10, PostgreSQL was using a three-part numbering series, meaning that the feature set and compatibility related to the first two numbers, while maintenance releases were denoted by the third number. For instance, version 9.6 contained more additional features and compatibility changes when compared to version 9.5; version 9.6.0 was the initial release of 9.6, and version 9.6.1 was a later maintenance release.
The release support policy for PostgreSQL is available at http://www.postgresql.org/support/versioning/. This article explains that each release will be supported for a period of 5 years. Since we release one major version per year, this means 5 major releases.
Support for all releases up to and including 9.6 ended in September 2021. So, by the time you're reading this book, only PostgreSQL 10 and higher versions will be supported. The earlier versions are still robust, although many performance and enterprise features are missing from those releases. The future end-of-support dates are as follows:
Figure 2.1 – A table showing PostgreSQL version release dates
