How to Check Your PostgreSQL Version
June 13, 2023
Knowing which version of PostgreSQL you are running is vital for feature availability and informed decision-making. This is especially crucial when using cloud-managed services that resemble PostgreSQL but do not exactly run the same software since managed services must do some things differently. In the case of PostgreSQL-compatible services, it is essential to consider both the service’s version and the version of compatible Postgres features.
How to Determine Which Version of PostgreSQL You Are Running
Show server_version
The most common way to check your version PostgresSQL is to look at the server_version parameter. I’m saying that this is “the most common” because that’s what is displayed by psql when you connect.
postgres=# \c
psql (13.7, server 15.1 (Debian 15.1-1.pgdg110+1))
postgres=# show server_version;
server_version
--------------------------------
15.1 (Debian 15.1-1.pgdg110+1)
(1 row)
postgres=#As you can see, I know that I’m connected to the PostgreSQL server version 15.1 with some additional information about the build. This is PostgreSQL, compiled by the PostgreSQL Global Development Group (PGDG).
Starting with PostgreSQL 10, the server_version follows the format major.minor. Previously, it had two-part major number before, like 9.6.24.
If I run the same query on YugabyteDB, which reuses a fork of PostgreSQL for its SQL processing layer but uses an innovative distributed storage layer that is completely different, I get:
yugabyte=# \c psql (13.7, server 11.2-YB-2.17.3.0-b0) yugabyte=# show server_version yugabyte-# ; server_version --------------------- 11.2-YB-2.17.3.0-b0 (1 row)
Here it is obvious that I am not on PostgreSQL, which should show only the major.minor like 11.2. But on a PostgreSQL-compatible database, like YugabyteDB, the version banner starts with the compatibility version. As you can see, this version of the Yugabyte database supports most of the PostgreSQL 11.2 features. YugabyteDB adds -YB- to identify itself and then adds its own version and build number.
Not all PostgreSQL-compatible databases are clever enough to identify themselves as not being the real PostgreSQL. For example, Amazon Aurora with PostgreSQL compatibility shows only the PostgreSQL compatibility version.
postgres=> show server_version; server_version ---------------- 14.6 (1 row)
To know that you are connected to Amazon Aurora and not PostgreSQL (and get its version), you have to query an additional function that exists only on this fork of PostgreSQL:
postgres=> select * from aurora_version(); aurora_version ---------------- 14.6.2 (1 row)
Parsing the server_version String
The server_version parameter is a string that must be parsed to get significant values. For example here is how I get each number for a YugabyteDB version:
yugabyte=# with p as (select '([0-9]+)[.]([0-9]+)(-(YB)-([0-9]+)[.]([0-9]+)[.]([0-9]+)[.]([0-9]+)[-](.*))?'as re
, current_setting('server_version') sv
) select
regexp_replace(sv,re,'\1') pg_major
, regexp_replace(sv,re,'\2') pg_minor
, regexp_replace(sv,re,'\5') yb_major
, regexp_replace(sv,re,'\6') yb_minor
, regexp_replace(sv,re,'\7') yb_update
, regexp_replace(sv,re,'\8') yb_build
from p;
pg_major | pg_minor | yb_major | yb_minor | yb_update | yb_build
----------+----------+----------+----------+-----------+----------
11 | 2 | 2 | 17 | 3 | b0
(1 row)This is specific to each fork and not easy to use to get the PostgreSQL version in general.
Show server_version_num
When it comes to PostgreSQL minor and major versions, better use the numeric version of it rather than parsing the text string. This is also available from the parameter
server_version_num postgres=# show server_version_num; server_version_num -------------------- 150001 (1 row) postgres=#
It was 2 digits per version part, like 90624 for 9.6.24 and is now the major multiplied by 10000 plus the minor, like 150001 for 15.1 and in all cases makes it easy to compare versions with simple integer arithmetic. This also works for PostgreSQL-compatible databases.
psql Variables
When connected with psql (or ysqlsh in the YugabyteDB) you don’t have to query the previous settings or functions since it is done automatically when you connect. The variables SERVER_VERSION_NAME and SERVER_VERSION_NUM are set accordingly:
yugabyte=# \echo :SERVER_VERSION_NUM :SERVER_VERSION_NAME
110002 11.2-YB-2.17.3.0-b0
yugabyte=# select :SERVER_VERSION_NUM;
?column?
----------
110002
(1 row)
yugabyte=# select :'SERVER_VERSION_NAME';
?column?
---------------------
11.2-YB-2.17.3.0-b0
(1 row)Those are different from :VERSION , :VERSION_NAME , :VERSION_NUM which are the versions of the psql client application itself.
For example, in psql you can run a version specific script with:
yugabyte=# \i script:SERVER_VERSION_NUM.sql script110002.sql: No such file or directory
I used a non-existing file in this example, to show the file name with variable expansion.
Note that a simple \set shows all variables and the version related ones are at the end.
Information Schema and Others
You can find the versions in other places, like in information_schema:
postgres=# select implementation_info_name, character_value
from information_schema.sql_implementation_info
where implementation_info_name like 'DBMS%';
implementation_info_name | character_value
--------------------------+-----------------
DBMS NAME | PostgreSQL
DBMS VERSION | 15.01.0000)
(2 rows)I would recommend not relying on this method, especially for PostgreSQL-compatible databases
Knowing PostgreSQL Versions for Compatibility and Security Audits
When looking up the PostgreSQL versioning scheme and release dates in Postgres documentation, it is important to look at the “Final Release” column. For example, PostgreSQL 11 ends in November 2023. This means that after this date there will be no security fixes, which is not a good idea for production.
However, this works differently for PostgreSQL-compatible databases that use the PostgreSQL code but not the binaries. The PostgreSQL version listed for these databases is only for feature and behavior compatibility. Security fixes may continue. For example, Greenplum or Amazon Redshift are based on old forks, but the vulnerabilities are still fixed by the product support even if they don’t come from the PostgreSQL community.
Another example is YugabyteDB which is currently compatible with PostgreSQL 11.2 and will follow the newer versions soon. Whether the compatibility with PostgreSQL 15 is generally available or not in November 2023, all vulnerabilities will continue to be fixed.
In short, this means that the PostgreSQL compatibility version is the right one to check for features, but security audits should look at the database version itself.
