PostgreSQL Version via Command Line & SQL Shell

PostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system. In this article, we’ll explain how to find PostgreSQL version running on your system.

Knowing what version of the PostgreSQL server is installed and running on your system can be important in some situations. For example, if you are installing an application that requires a specific PostgreSQL version, you’ll need to find out the version of your server.

PostgreSQL Version

PostgreSQL releases are versioned using the following scheme:

MAJOR.MINOR

For example, in PostgreSQL 12.1, 12 is a major version, and 1 is a minor version.

• MAJOR – Starting with PostgreSQL 10, each new major release increases the MAJOR part of the version by one, e.g., 10, 11 or 12. Before PostgreSQL 10, major versions were represented with a decimal number e.g., 9.0 or 9.6.
• MINOR – Minor release number is the last part of the version number. For example, 11.4 and 11.6 are minor versions that are part of the PostgreSQL version 11, and 9.6.15 and 9.6.16 are part of the PostgreSQL version 9.6.

PostgreSQL major releases with new features are usually delivered once a year. Each major release is supported for 5 years.

You may be interested in learning about how to setup PyroCMS on Linux server.

Find PostgreSQL Version using Command Line

To find out what version of PostgreSQL is running on your system, invoke the postgres command with the --version or -V option:

postgres --version

The command will print the PostgreSQL version:

postgres (PostgreSQL) 10.6

In this example, the version of the PostgreSQL server is 10.6.

If the postgres binary is not in system’s PATH, you’ll get an error saying “postgres: command not found”. This usually happens when the PostgreSQL package is not installed from the distribution’s standard repositories.

You can find the path to the binary either with the locate or find command:

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

sudo updatedb
locate bin/postgres

The output should look something like this:

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

Once you find the path to the binary, you can use it to get the version of the PostgreSQL server:

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

The version of the PostgreSQL client utility, psql can be found using the following command:

psql --version

The output will look something like this:

postgres (PostgreSQL) 10.6

psql is an interactive command-line utility that allows you to interact with the PostgreSQL server.

Using SQL Shell

Another way to determine the PostgreSQL server version is to log in to the server SQL prompt and use an SQL statement to print out the version.

You can access the PostgreSQL shell using a GUI client like pgAdmin or with psql:

sudo -u postgres psql

The following statement displays the PostgreSQL server version along with the build information:

SELECT version();

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)

If you want to get only the PostgreSQL server version number use the following query:

SHOW server_version;

 server_version 
 10.6
 (1 row)

Conclusion

In this article, we have shown several different options about how to find the PostgreSQL version on server running on your system.