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
- Using the command line
- Using SQL
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
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
locate database so it can find the files:
$ sudo updatedb
Next let us locate our
$ 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 -V postgres (PostgreSQL) 14.0
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.
To determine the server version, postgres provides the
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
postgres=# show server_version; server_version ---------------- 14.0 (1 row)
SHOW command is used to display current run-time parameters, which are essentially just a table of
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.
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.
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
We managed to cover how to check the client and server versions of postgres in this guide.
Pingback: Which Version Of PostgreSQL Am I Running? - Programming Questions And Solutions Blog