skip to content

In PostgreSQL, how do you check which server version you are connected to, and why might version() still report 18.4 after 18.6 packages were installed?

level: middleimportance: should knowfreq 46%

answer

  1. a string for people, an integer for code
  2. major times 10000 plus minor
  3. the client is not the server
  4. running processes keep the old program

basics

~20 s

Use SELECT version() or SHOW server_version to read it, and server_version_num (180006 for 18.6) in scripts. If version() still shows 18.4 after the 18.6 packages went in, the server has not been restarted and is still running the old code.

solid answer

~40 s

`SELECT version();` returns a descriptive string such as `PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc ..., 64-bit`, and `SHOW server_version;` returns just the version, sometimes with a packager's suffix. In code use the integer `server_version_num` (`SHOW server_version_num` or `current_setting('server_version_num')::int`): major times 10000 plus minor, so 18.6 is `180006` and `>= 170000` is a safe feature test. `psql --version` and `pg_config --version` describe the client and the installed build, not the running server, and the `PG_VERSION` file in the data directory holds only the major, `18`. If `version()` still reports 18.4 after installing 18.6, the server was not restarted: on Linux the postmaster and every backend it forks keep executing the program they started with, and `pg_postmaster_start_time()` older than the install confirms it.

code

sql · 5 lines
sql
SELECT version();
SHOW server_version;
SHOW server_version_num;
SELECT current_setting('server_version_num')::int >= 180000 AS is_pg18_or_later;
SELECT pg_postmaster_start_time();

go deeper

for a junior

Know version(), SHOW server_version and server_version_num, and that client tools report their own version, not the server's.

for a middle

Explain the integer encoding, major times 10000 plus minor, why scripts should compare it rather than text, and why an installed package is not a running server.

for a senior

Use the integer and pg_postmaster_start_time() to prove a patch round finished, and tell a server never patched apart from one patched on disk but not restarted.

for a principal

Make the running version a monitored fact, collected from every server, rather than something inferred from configuration management or package inventories.

## Ask the server, not the machine The version that matters is the one the **running server process** reports, because that is the code executing your queries. PostgreSQL exposes it in several forms: | Method | Example on 18.6 | Use it for | |---|---|---| | `SELECT version();` | `PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc ..., 64-bit` | A person reading a ticket: version plus platform and compiler | | `SHOW server_version;` | `18.6`, possibly with a packager's suffix | A quick look; the server also sends this string to every client when it connects | | `SHOW server_version_num;` | `180006` | Scripts, migrations and feature checks | | `current_setting('server_version_num')::int` | `180006` | The same value inside a SQL expression | `server_version` and `server_version_num` are **preset parameters**: they are fixed when the server is built, cannot be set in `postgresql.conf`, and always describe the program that is actually running. `version()` returns a string compiled into the same binary. Client libraries have an equivalent: libpq's `PQserverVersion()` returns the same integer. ## The integer form and why code should use it `server_version_num` is **major times 10000 plus minor**: 18.6 is `180006`, 17.11 is `170011`. For the old three-part versions two digits were used per part, so 9.6.2 is `90602`. The integer sorts correctly; the text does not: - Compared as strings, `'9.6'` is greater than `'18.0'`. - A packager's suffix after the number, or a pre-release string such as `19beta4`, breaks naive parsing. - The documentation of `version()` itself tells developers to use `server_version_num` (available since 8.2) or `PQserverVersion` instead of parsing the text. So a feature check is one comparison, and splitting the integer is arithmetic: ```sql SELECT current_setting('server_version_num')::int >= 170000 AS has_pg17_features; SELECT n / 10000 AS major, n % 10000 AS minor FROM (SELECT current_setting('server_version_num')::int AS n) AS v; ``` psql exposes the same pair as its variables `SERVER_VERSION_NAME` and `SERVER_VERSION_NUM`, which suits `\if` blocks in deployment scripts. ## Things that look like the server version but are not - **`psql --version`** prints the version of the psql client binary on your machine. It can be newer or older than the server you connect to. - **`pg_config --version`** prints the version of the installed build: what the packages on disk are, not necessarily what is running. - **The `PG_VERSION` file** in the data directory contains only the major number, `18`. It records which major the cluster's files belong to and does not change on a minor update, so it tells you which major's binaries can open that directory, not which minor is running. - **A configuration-management report or package inventory** says what was meant to be installed. It is evidence about intent, not about the process answering queries. - **The psql banner** is a useful hint: when the client and server versions differ, psql greets you with `psql (18.6, server 18.4)`, and it prints a warning when the server's major is newer than its own. ## Installed is not running On Linux, installing new packages replaces files on disk. The **postmaster** that is already running, and every backend it forks for new connections, keep executing the program image they started with. So right after the 18.6 packages are installed on an 18.4 server: 1. `pg_config --version` and `psql --version` already say 18.6. 2. `SELECT version()` still says 18.4, because the server process is still the 18.4 program. 3. `SELECT pg_postmaster_start_time();` returns a time earlier than the package installation, which confirms the server has not been restarted. None of 18.6's fixes is in effect until the restart, security fixes included. Some packagings restart the service as part of the upgrade and others leave it to you, so check rather than assume. `pg_reload_conf()` does not help: a reload makes the server re-read its configuration files, and the program stays the same. ## Closing a patch round with the integer When several servers are patched, the closing check is the integer from each one, for example `psql -Atc 'SHOW server_version_num'` against every host, compared with the target `180006`. A server that still returns `180004` was either never patched or patched on disk and never restarted; `pg_config --version` on that host tells the two apart, and `pg_postmaster_start_time()` shows when the running program was started. Recording the integer rather than the text also makes the fleet sortable: the lowest number is the server most behind.

  • Why is comparing PostgreSQL's server_version strings in a script fragile?
    Text compares character by character, so `'9.6'` sorts above `'18.0'`. The string can also carry a packager's suffix or a pre-release tag such as `19beta4`. `server_version_num` is an integer, major times 10000 plus minor, so `current_setting('server_version_num')::int >= 170000` is correct for every release since 10, and the manual recommends it over parsing `version()`.
  • What does psql's startup banner tell you about client and server versions?
    When the psql binary and the server differ, psql prints both, for example `psql (18.6, server 18.4)`; when they match it prints only one version. If the server's major is newer than psql's, it adds a warning that some psql features might not work. It is a quick hint that a server was patched on disk but not restarted, or that your client is older.

  • psql --version tells you which version the server is running.
  • The PG_VERSION file in the data directory shows the full version, such as 18.6.
  • Installing the new packages upgrades the running server straight away.
  • pg_reload_conf() makes the server start using newly installed binaries.
  • Parsing the version() text is the right way to detect features in application code.