How to Check PostgreSQL version

Last updated on August 9th, 2024 at 07:20 am

PostgreSQL regularly releases newer versions with more features, bug fixes, performance improvements. Often you may need to find PostgreSQL version to determine if a specific command or feature is supported in it. This also tells you if your version is too old and needs to be updated. In this article, we will look at the different ways to check PostgreSQL version.

How to Check PostgreSQL version

Here are the steps to check PostgreSQL version. It is important to remember that since PostgreSQL client and server are different entities, they will have different versions. You can find PostgreSQL version for both database server and client, via Shell command as well as SQL queries. We will look at each of these approaches one by one.

1. Find PostgreSQL Server Version using postgres command

You can get PostgreSQL version for Server from terminal/command line by simply issuing postgres command with -V option.

$ postgres -V
or
$ postgres --version
Postgres (PostgreSQL) 10.3.10

The above commands if the path to postgres binary is automatically added to your system’s PATH environment variable, at the time of installation. But sometimes this does not happen and you can ‘postgres’ command not found error. In such cases, if postgres command is not directly supported, find the path of its binary using locate command

$ locate bin/postgres
/usr/lib/postgresql/10.3/bin/postgres

Now use the full path with -V option to get its version.

$ /usr/lib/postgresql/10.3/bin/postgres -V
Postgres (PostgreSQL) 10.3.10

Also read : How to Change User to Superuser in PostgreSQL

2. Check PostgreSQL client version using psql

Similarly, you can determine the PostgreSQL client version using psql command with -V option.

$ psql -V
psql (PostgreSQL) 10.3.10

Again, if psql command is not supported or the path to its binary is not added to PATH environment variable, use locate command to find out the path of its binary.

$ locate bin/psql
/usr/bin/psql

Now run the full path with -V option

$ /usr/bin/psql -V
psql (PostgreSQL) 10.3.10

By the way, when you log into PostgreSQL shell, it automatically displays the client version above the prompt.

Also Read : How to Limit Access to Database in PostgreSQL

3. Check PostgreSQL Server Version Using SQL queries

You can also find PostgreSQL version using SQL queries. Here are two SQL queries to get PostgreSQL version. One uses version() function while the other uses server_version system variable. First, log into PostgreSQL server. Then run any of the following SQL queries.

-# SELECT version();
PostgreSQL 10.3.10, compiled by Visual C++ build 1800, 64-bit

OR

-# SHOW server_version;
10.3.10

Also read : How to Set up remote connection in PostgreSQL

4. Check PostgreSQL Client Version Using SQL queries

Similarly, you can also view PostgreSQL client version using psql query as shown below.

=# \! psql -V 
psql (PostgreSQL) 10.3.10

Conclusion

In this article, we have provided four ways to get PostgreSQL version, for server as well as client. It is very important for every database administrator to keep track of their PostgreSQL server version, especially that of their server to avail latest commands, features, bug fixes, security updates and performance improvements. Otherwise, you may not be able to run some of the latest commands. Also, malicious attackers may exploit security vulnerabilities in it. So determine PostgreSQL version of your system and proceed accordingly.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.