How to Check Your PostgreSQL Version

Introduction

New versions of PostgreSQL are released at regular intervals. Major releases are scheduled yearly and focus on improving key features and fixing known bugs. Minor releases are available approximately every three months and aim to resolve ongoing security concerns.

You might want to check if you have the latest security patch, or if the new software you want to implement is compatible with your PostgreSQL version.

This tutorial shows you how to check your PostgreSQL version using a few short commands.

Prerequisites

• Access to a terminal window/command line
• PostgreSQL database server

Check PostgreSQL Version from Command Line

Access your terminal and enter the following command to check your PostgreSQL version:

postgres --version

The version number is displayed in your terminal window. Another way to check your PostgreSQL version is to use the -V option:

postgres -V

These two commands work with installations initiated from official repositories. They might not be applicable for installations originating from third-party sources. Instead, you might receive the “Command ‘postgres’ not found” message.

How to Solve the “Command ‘postgres’ not found” Error

To solve the “Command ‘postgres’ not found” issue, locate the PostgreSQL binary folder. Enter the following command to locate the correct postgres path:

locate bin/postgres

The path to your binary folder is now displayed in your terminal.

Type the full path and add the -V option to display the current PostgreSQL server version:

/usr/lib/postgresql/10/bin/postgres -V

In this example, the Postgres version number is 10.12.

The PostgreSQL Development Group uses a standard MAJOR.MINOR semantic versioning system. In our example, the first section (10) signifies the MAJOR release number. The second part (12), represents the MINOR release number for that major version.

Check Postgres Version from SQL Shell

The version number can also be retrieved directly from the PostgreSQL prompt. Access the PostgreSQL shell prompt by typing the following command:

sudo -u postgres psql

Type the following SQL statement within the prompt to check the current version:

SELECT version();

The resulting output provides the full version and system information for the PostgreSQL server.

You can also instruct PostgreSQL to show the value associated with the server_version parameter:

SHOW server_version;

The result displays the current value for server_version.

How to Check psql Client Version

Psql functions as a front-end terminal for PostgreSQL. It’s used to issue queries and display the provided results.

You can use the following command to determine the version of the psql client utility:

psql --version

You’ll notice that the commands used to check the psql client version match the commands used to determine PostgreSQL server version. The -V option works in this instance as well:

psql -V

The psql version is presented in the terminal.

The “Command not found” error can appear in this instance as well. If that is the case, enter the following command to locate the correct path to the psql utility:

locate bin/psql

The output provides the full path to the psql utility.

Use the resulting path and -V option to check the current psql version:

/usr/lib/postgresql/10/bin/psql -V

The resulting output shows you the current psql client version on your system.

Conclusion

The provided commands and SQL statements are the most effective way to determine the PostgreSQL version number. Use them to check the current version of your PostgreSQL database server or psql client utility.

Make sure that your systems are always up to date with the latest available version.