What version of postgres client or server am I running?

In this brief guide, we are going to explore how to check which version of Postgres is running either on your machine or in a postgres instance for both the client and the server versions.

Table of Contents

1. Using the command line
2. Using SQL
3. Use VERSION special variable

1. Using the command line

Postgres installations provide both client and server utilities that can be used to query the Postgres Installation.

To check the version, we use the respective binary but with the -V or --version flag.

Server version

To find the Postgres server version from the shell command line, use the postgres command with the -V or (--version) flag (for version):

$ postgres -V
postgres (PostgreSQL) 14.0

Often times, the Postgresql command is not added to the $PATH and is not normally accessible. In such a case, you will get the error:

$ postgres -V
-bash: postgres: command not found

If you know the exact path to the postgres executable, type it like /path/to/postgres -V but if not use the locate command. If you get the locate command not found like this:

# locate bin/postgres
-bash: locate: command not found

You need to install it. I am Using Rocky Linux 8 – Similar to Centos 8. So to check which package provides that:

$ sudo dnf whatprovides locate
Last metadata expiration check: 0:44:27 ago on Wed 06 Oct 2021 05:46:04 AM UTC.
mlocate-0.26-20.el8.x86_64 : An utility for finding files by name
Repo        : baseos
Matched from:
Filename    : /usr/bin/locate

Install locate in centos 8

$ sudo dnf install -y mlocate

Then Update locate database so it can find the files:

$ sudo updatedb

Next let us locate our bin/postgres.

$ locate bin/postgres
/usr/bin/postgresql-14-setup
/usr/pgsql-14/bin/postgres
/usr/pgsql-14/bin/postgresql-14-check-db-dir
/usr/pgsql-14/bin/postgresql-14-setup

In our case we are interested in the /usr/pgsql-14/bin/postgres command:

$ /usr/pgsql-14/bin/postgres -V
postgres (PostgreSQL) 14.0

Client version

Postgres provides a client tool pgsql that can be used to connect to postges.

To view the client version, pass the -V flag to the psql client utility command:

$ psql -V
psql (PostgreSQL) 14.0

If the psql command is not found, search it with :

$ locate bin/psql
/usr/bin/psql
/usr/pgsql-14/bin/psql

Then you can supply the full path:

$ /usr/pgsql-14/bin/psql -V
psql (PostgreSQL) 14.0

2. Using SQL

We can also use the SQL prompt via an SQL statement to query the Postgesql database.

Server version

To determine the server version, postgres provides the select version();

postgres=# select version();
                                                version

-----------------------------------------------------------------------------------------------------
---
 PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-b
it
(1 row)

The above command will show the full version information for the database.

You can also query from the command line:

psql -c "SELECT version();"

You can also query the specific server version, in the standard major.minor.patch format, by using the SHOW command:

postgres=# show server_version;
 server_version
----------------
 14.0
(1 row)

The SHOW command is used to display current run-time parameters, which are essentially just a table of name/setting pairs.

Client version

We can also issue a short SQL statement from a PostgreSQL prompt to query the client version of psql. However, it’s worth noting that this is purely for convenience, as we’re effectively just asking Postgres to issue a command on the shell prompt, but from within the PostgreSQL prompt itself.

This is performed by using the \! flag while connected to the client, followed by the statement we wish to issue:

postgres=# \! psql -V
psql (PostgreSQL) 14.0

Just as before when we were issuing this command directly from the shell prompt, psql -V may return your client version as above, or the path may not be found.

3. Use VERSION special variable

We can also use the VERSION special variable defined for the postgres user. For this to work we need to be logged in as a postgres user.
Login as postgres

sudo su - postgres

Then check the version

$ psql -c "\echo :VERSION"
PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

Conclusion

We managed to cover how to check the client and server versions of postgres in this guide.