How to Check the PostgreSQL Version

Introduction

Before we start talking about how to check PostgreSQL version, let's briefly understand-What is PostgreSQL ?

PostgreSQL, also known as Postgres, is a free and open-source general-purpose object-relational database management system.

When working with PostgreSQL, it is often important to know the version you are using. The version number provides valuable information about features, bug fixes, and compatibility.

This tutorial will outline several methods to check the PostgreSQL version to help you identify the version of your PostgreSQL installation. We will also address a few FAQs on how to check PostgreSQL version.

PostgreSQL Versioning

PostgreSQL's releases are versioned as follows:

MAJOR.MINOR

For instance, 12 is a major version while 1 is a minor version in PostgreSQL 12.1.

• MAJOR: Starting with PostgreSQL 10, each major release increases the MAJOR portion of the version by one, for example, 10, 11, or 12. Prior to PostgreSQL 10, major releases were denoted by a decimal number, such as 9.0 or 9.6.
• MINOR: The final component of the version number is the minor release number. For instance, minor versions 11.4 and 11.6 are included in PostgreSQL version 11, and 9.6.15 and 9.6.16 are included in PostgreSQL version 9.6.

Major PostgreSQL updates with new features are usually released once every year. Each major release is supported for a period of five years.

Using the Command Line

Use the postgres command with the --version or -V argument to find out which PostgreSQL version is running on your system:

postgres --version

The PostgreSQL version will be printed using the command:

Output

postgres (PostgreSQL) 10.6

The PostgreSQL server in this example is running version 10.6.

You will receive an error message that reads “postgres: command not found” if the postgres binary is not in the system's PATH.

Using either locate or find command, you can find the path to the binary:

sudo find /usr -wholename '*/bin/postgres'

sudo updatedb
locate bin/postgres

The output should resemble this:

Output

/usr/lib/postgresql/9.6/bin/postgres

Once you have located the binary, you can use it to determine the PostgreSQL server's version:

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

The following command will find a version of the PostgreSQL client utility, psql:

psql --version

The output will resemble this:

Output

postgres (PostgreSQL) 10.6

You can interact with the PostgreSQL server using the command-line utility psql.

Using the SQL Shell

You can also use a SQL statement to print the version of the PostgreSQL server by logging in to the server's SQL prompt.

You can use psql or a GUI client like pgAdmin to access the PostgreSQL shell:

sudo -u postgres psql

The PostgreSQL server version and build information are shown in the following statement:

SELECT version();

Output

                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
(1 row)

The following query should be used if you get only the PostgreSQL server version number:

SHOW server_version;

Output

 server_version 
----------------
 10.6
(1 row)

FAQs: How to Check the PostgreSQL Version

Conclusion

In this tutorial, we have covered different methods for determining the PostgreSQL server version that is installed on your system.

If you have any queries, feel free to leave a comment below, and we'll be happy to help.