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

Why is it important to know the PostgreSQL version? 

Knowing the PostgreSQL version is crucial for multiple reasons. It helps determine the availability of specific features, compatibility with certain extensions or tools, and the need to upgrade or apply relevant patches.

How can I find the PostgreSQL version in the shell/command prompt? 

In the shell or command prompt, you can use the pg_config command, followed by the --version flag, to obtain the PostgreSQL version.

Are there any graphical tools for checking the PostgreSQL version? 

Yes, there are graphical tools available to check the PostgreSQL version. Tools like PgAdmin, DBeaver, and Navicat provide a user-friendly interface and include information about the current PostgreSQL version.

Can I determine the PostgreSQL version through the installation directory? 

Yes, you can check the version from the PostgreSQL installation directory. Navigate to the PostgreSQL directory and locate the README or CHANGES files. These files typically contain the version number and release notes.

How can I check the PostgreSQL version using an SQL query without accessing the database directly? 

If you have access to the pg_dump command, you can use it with the --version flag without connecting to the database.

Can I obtain the PostgreSQL version via the pgAdmin web interface? 

Yes, in the pgAdmin web interface, you can connect to your PostgreSQL server, select the server from the object browser, and locate the version information under the "Properties" or "Server Status" section.

Do I need to be logged in as a superuser to check the PostgreSQL version?

No, checking the PostgreSQL version does not require superuser privileges. You can determine the version as a regular user or with appropriate database access permissions.

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.