As a popular and powerful open-source relational database management system, PostgreSQL is widely used in many applications. Knowing the version of PostgreSQL that you are working with is crucial for compatibility checking, troubleshooting, and ensuring that your application is utilizing the correct features and optimizations.
In this post, we will explore different methods to retrieve the PostgreSQL version information. We will cover SQL queries that can be executed directly in the PostgreSQL database, as well as other approaches using command-line tools, client libraries etc.
So, let’s dive in and explore the different approaches to obtain the version information of PostgreSQL, enabling you to effectively manage and work with this powerful database management system!
Table of Contents
understanding Postgresql version format
The current PostgreSQL server version format is composed of two numbers; the first number indicates the major release, and the second one denotes subsequent maintenance releases for that major release. It is common to mention just the major release when discussing what features are supported, as they are unchanged on a maintenance release.
14.0 is the first release of PostgreSQL 14, and subsequent maintenance releases will be 14.1, 14.2, 14.3, and so on. In the preceding example, we see that 14.0 is the version of that PostgreSQL server.
For each major release, there is a separate version of the manual, since the feature set is not the same. If something doesn’t work exactly the way you think it should, make sure you are consulting the correct version of the manual.
Here’s a table showing some of the major releases of PostgreSQL, along with their version numbers and corresponding release dates:
| Version | Release Date |
|---|---|
| 1.0 | 1989-07-08 |
| 4.2 | 1994-07-01 |
| 6.3 | 1998-10-12 |
| 7.1 | 2001-04-04 |
| 8.0 | 2005-01-19 |
| 8.4 | 2009-07-01 |
| 9.0 | 2010-09-20 |
| 9.1 | 2011-09-12 |
| 9.2 | 2012-09-10 |
| 9.3 | 2013-09-09 |
| 9.4 | 2014-12-18 |
| 9.5 | 2016-01-07 |
| 9.6 | 2016-09-29 |
| 10 | 2017-10-05 |
| 11 | 2018-10-18 |
| 12 | 2019-10-03 |
| 13 | 2020-09-24 |
| 14 | 2021-09-30 |
Please note that this table includes only some of the major releases of PostgreSQL and their approximate release dates. For a complete and up-to-date list of PostgreSQL releases and their release dates, it’s recommended to refer to the official PostgreSQL documentation or the PostgreSQL community website.
Get Postgresql version with select sql query
The SQL query SELECT version(); is used to retrieve the version information of the PostgreSQL database server that you are connected to. The result of this query will provide you with details about the specific version of PostgreSQL that is currently installed and running.
Step 1: Open a terminal window or command prompt.
Step 2: Connect to your PostgreSQL database server using the psql command with appropriate connection details. For example, if your PostgreSQL server is running locally on the default port with the default database name and you are using the default PostgreSQL user, you can use the following command:
psql -U postgres -h localhost -p 5432 -d postgres
This assumes that you are connecting to the postgres database with the postgres user, and the PostgreSQL server is running on localhost (i.e., the same machine where you are executing the psql command) on port 5432 (the default port used by PostgreSQL).
Step 3: Once connected to the PostgreSQL server, you can issue the following SQL query to retrieve the version information:
SELECT version();
Step 4: Press Enter to execute the query. The result will display the version information of the PostgreSQL server, such as:
version
————————————————————————————————-
PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.0, 64-bit
(1 row)
The version information will be displayed as part of the query result, showing the specific version of PostgreSQL that is currently installed and running on your server.
This indicates that the version of PostgreSQL running is 13.5, and it was compiled using gcc (GCC) version 8.3.0 on a 64-bit Linux system.
The version information returned include details such as the major and minor version numbers, the platform or architecture it was compiled for, the compiler used, and other relevant information about the PostgreSQL installation.
Step 5: You can then exit the psql session by typing \q and pressing Enter.
By following these steps, you can easily retrieve the version information of PostgreSQL using the psql command.
Get Postgresql version with show command
Another way to retrieve the version information of PostgreSQL in your programs is by using the SHOW command with the server_version parameter. This is an SQL command that can be executed directly in the PostgreSQL database to obtain the version number.
For example, if you execute the following query in a PostgreSQL database:
SHOW server_version;
You may receive a result similar to the following:
server_version
----------------
13.5
(1 row)
This indicates that the version of PostgreSQL running is 13.5.
The SHOW command in PostgreSQL is used to retrieve various configuration settings, system parameters, and other server-related information. By specifying server_version as the parameter, you can obtain the version number of the PostgreSQL database server.
This method can be particularly useful when querying PostgreSQL from within your application code, as it allows you to dynamically retrieve the version information during runtime and use it in your application logic as needed.
Check Postgresql version with postgres command
You can also obtain the PostgreSQL version using “postgres -V” or “postgres –version” without the quotes.
Here’s an example of how you can use the correct command in a terminal or command prompt:
postgres -V
or
postgres --version
After executing the command, you should see the version number of PostgreSQL printed in the output, such as “PostgreSQL 13.3” or “PostgreSQL 14.1”, depending on the version installed on your system.
If you can not find the postgres command, you can refer to the following steps.
To determine the location where the PostgreSQL command is installed on your system, you can use the which command (on Linux and macOS) or the where command (on Windows). Here’s how you can do it:
On Linux and macOS:
Open a terminal window.
Type the following command and press Enter:
which postgres
This will display the path where the postgres command is installed, such as /usr/bin/postgres or /usr/local/bin/postgres.
Type the full path and add the -V option to display the current PostgreSQL server version:
/usr/bin/postgres -V
Get Postgresql version with psql command
You can indirectly obtain the version information by running the psql command with the –version option.
Here’s an example of how you can use the psql command with the –version option to retrieve the version information:
psql --version
This will display the version of the psql client tool, which may be different from the version of PostgreSQL server. The output will be something like:
psql (PostgreSQL) 13.5
In this example, the version of the psql client tool is shown as 13.5. Please note that this version number corresponds to the version of psql and not necessarily the version of the PostgreSQL server.
To get the version of the PostgreSQL server, you can connect to the server using the psql command and then run the SELECT version(); query as mentioned in the previous response.
Get Postgresql version with rpm command
On some Linux distributions, you can use the rpm command to check the version of PostgreSQL that is installed on your system. Here’s how you can do it:
Step 1: Open a terminal window.
Step 2: Run the following command, replacing postgresql with the appropriate package name for your distribution:
For CentOS/RHEL:
rpm -qi postgresql
For Ubuntu/Debian:
dpkg -s postgresql
Step 3: Press Enter to execute the command.
Step 4: Look for the line that shows the version information of PostgreSQL. The output may look like:
Name : postgresql
Version : 13.5
Release : 1pgdg<distro-version>
In this example, the version of PostgreSQL installed on the system is shown as 13.5. The exact format and location of the version information may vary depending on the Linux distribution you are using.
Please note that the rpm or dpkg commands provide information about the installed package version and not necessarily the version of the PostgreSQL server that is currently running. To obtain the version of the PostgreSQL server that is running, you can use the methods mentioned in the previous responses, such as connecting to the PostgreSQL server with psql and running the SELECT version(); query.