Введение
PostgreSQL — объектно-реляционная система управления базами данных с открытым исходным кодом. Есть несколько способов узнать версию PostgreSQL, установленную на сервере. Технические специалисты должны располагать такими сведениями, например, чтобы своевременно производить обновление программного обеспечения, понимать, насколько текущая версия совместима для интеграции с той или иной службой, и для выполнения иных административных задач. Будем считать, что PostgreSQL уже установлена на сервере и работает. Если на этапе установки и настройки возникли какие-либо сложности, у нас в блоге есть статья, в которой рассмотрены базовые функции по работе с СУБД. В нашем случае, в качестве операционной системы выбрана Ubuntu Linux 22.04 и версия PostgreSQL 14.5, установленная из репозитория.
Обозначение версий PostgreSQL
Разработчики придерживаются следующей схемы нумерации версий продукта: MAJOR.MINOR, где major — основная версия, которая снабжается новым функционалом, исправляет ошибки обновляет систему безопасности. Такой релиз выпускается примерно раз в год и поддерживается ближайшие 5 лет. Minor — дополнительная версия, выпускается не реже одного раза в три месяца и содержит в основном обновления системы безопасности.
Проверить версии PostgreSQL из командной строки
Для отображения версии PostgreSQL, нужно любым удобным способом подключиться к серверу и в терминале выполнить команду:
pg_config --version
Результат выполнения:
postgres (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)
Из вывода команды видно, что используется версия PostgreSQL 14.5.
Есть и другие варианты проверки, но с ними не всегда удается сделать все с ходу:
postgres --version
Или используя короткую версию параметра -V:
postgres -V
Обратите внимание, что в первом случае применяется длинная версия параметра —version, а во втором короткая -V, результат выполнения во всех трех случаях абсолютно одинаковый.
На этом этапе некоторые операционные системы могут сообщить об ошибке: Command ‘postgres’ not found, это не проблема, и связано с тем, что разработчики данного программного продукта по каким-либо причинам не размещают двоичный исполняемый файл postgres ни в одну из папок, прописанных в переменной окружения $PATH. В таком случае, найдем его самостоятельно:
sudo find / -type f -iwholename "*/bin/postgres"
Результат выполнения команды в нашем случае:
/usr/lib/postgresql/14/bin/postgres
Файл найден. Повторяем вышеописанные действия, используя абсолютный путь:
/usr/lib/postgresql/14/bin/postgres --version
Или:
/usr/lib/postgresql/14/bin/postgres -V
Результат выполнения обеих команд будет идентичный, что был описан выше.
Узнать версию сервера PostgreSQL, используя оболочку
Также есть возможность определить версию СУБД непосредственно из оболочки самого сервера. На практике такой подход применим при написании SQL-запросов. Переходим в интерактивный терминал PostgreSQL от имени пользователя postgres:
sudo -u postgres psql
Система попросит ввести свой пароль для использования функционала sudo. После ввода пароля должно появиться приглашение интерпретатора SQL-запросов в виде:
postgres=#
Для отображения версии установленного сервера вводим запрос:
SELECT version();
В ответ получим:
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)
Из вывода команды видно, что установлена версия 14.5, а также другие технические данные о сервере.
Если необходимо запросить версию и менее детализированный вывод, используем конструкцию:
SHOW server_version;
Тогда ответ от сервера будет выглядеть следующим образом:
server_version
-------------------------------------
14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)
(1 row)
Запущенный сервер сообщает номер версии — 14.5. Для выхода из SQL shell нужно ввести команду \q и нажать Enter.
Посмотреть версию утилиты PSQL
PSQL — утилита, служащая интерфейсом между пользователем и сервером, она принимает SQL-запросы, затем передает их PostgreSQL серверу и отображает результат выполнения. Данный инструмент предоставляет очень мощный функционал для автоматизации и написания скриптов под широкий спектр задач. Для получения информации о версии установленной утилиты, нужно выполнить команду:
psql -V
Или используя длинную версию параметра –version:
psql --version
Вывод в обоих случаях будет одинаковый:
psql (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)
Терминальная утилита PSQL имеет версию 14.5.
Заключение
В этой инструкции мы:
- разобрались в схеме управления версиями разработчиками продукта;
- научились смотреть версию PostgreSQL в командной строке и с помощью клиентской оболочки PSQL;
Стоит добавить, что данная инструкция охватывает лишь часть функционала по работе с PostgreSQL, за дополнительной информацией всегда можно обратиться к документации на официальном сайте.
Существует несколько способов узнать версию системы управления базами данных PostgreSQL в операционной системе Windows.
Узнать версию PostgreSQL при помощи графической оболочки
Чтобы узнать версию PostgreSQL при помощи графической оболочки, необходимо подключиться к нужной базе данных и ввести следующий запрос:
select version();
Результатом выполнения будет одна строка, в которой будет указана версия.
Узнать версию PostgreSQL при помощи SQL Shell (psql)
Находим утилиту SQL Shell (psql) и запускаем её.
Теперь необходимо подключиться к серверу PostgreSQL:
- Server [localhost] — оставляем пустым, нажимаем Enter;
- Database [postgres] — указываем имя базы данных my_db, нажимаем Enter;
- Port [5432] — оставляем пустым, нажимаем Enter;
- Username [postgres] — оставляем пустым, нажимаем Enter;
- Пароль пользователя postgres — вводим пароль, который был указан при установке.
После подключения к серверу, выполняем следующий запрос:
select version();
Результатом выполнения будет одна строка, в которой будет указана версия.
Метки: PostgreSQL.
Knowing the specific version of PostgreSQL is vital for maintaining compatibility and utilizing new features. In this article, we will explain several methods to check our PostgreSQL version using the command line, the SQL shell, and the psql client. Also, it addresses common errors such as the «Command ‘postgres’ not found«.
Checking PostgreSQL Version from the Command Line
When working with Postgres, the exact version we are using is essential for compatibility, performance tuning and taking advantage of the latest features and security improvements.
Checking the PostgreSQL version from the command line is one of the quickest methods available. Let’s go over two commands that can help us determine the server and client versions.
1. Using the PSQL Command (Client Version)
The PSQL command is the PostgreSQL interactive terminal that allows us to communicate with the database. The —version option displays the installed version of the psql client.
To check the version of the PostgreSQL client installed on your system, use the following command
psql --version
Output:
Explanation: This output indicates that the psql client version installed on my machine is 16.2, meaning i am using the client that interacts with a PostgreSQL 16.x server.
2. Using the postgres Command (Server Version)
The Postgres -V command directly checks the version of the PostgreSQL server. It’s useful for confirming the server version in case you are maintaining or configuring multiple versions of PostgreSQL.
To check the PostgreSQL server version, use the postgres command
postgres -V
Output:
Explanation:
The output shows that my PostgreSQL server version is 16.2. It’s important to note that the client and server versions should match or compatible to avoid communication errors.
Solve «Command ‘postgres’ not found» When Using postgres -V
Sometimes, when we try to run Postgres -V, we might encounter the «Command ‘postgres’ not found» error. This issue occurs either because PostgreSQL is not installed correctly or because the PATH environment variable is not configured. Here’s how we can fix this:
Step 1. Verify Installation: Run the following command to verify if PostgreSQL is installed on your system.
This command lists the installed packages and should return a result if PostgreSQL is installed. If nothing is returned, we need to install PostgreSQL.
sudo apt list --installed | grep postgresql
Step 2. Add PostgreSQL to PATH: If PostgreSQL is installed but not found, add the PostgreSQL binary folder to your PATH:
export PATH=$PATH:/usr/lib/postgresql/14/bin
Step 3. Reinstall PostgreSQL: If the issue persists, reinstall PostgreSQL using the following command
sudo apt install postgresql
These steps will help resolve the error and ensure that the Postgres -V command works properly to check your server version.
Check Postgres Version from SQL Shell
The SQL Shell (PSQL) is another way to determine the PostgreSQL version. This method provides detailed information, including platform and compiler details. This query returns detailed information about the PostgreSQL server, including the version number, platform, and compiler used to build PostgreSQL.
Step 1: Log in to the PostgreSQL shell
Step 2: Run the following SQL command, This query returns the PostgreSQL version along with additional details like the OS platform and compiler version used to build the database.
SELECT version();
Output:
Explanation:
The output shows that PostgreSQL version 16.2 is installed on a 64-bit system. Additionally, it provides details about the C++ compiler version used to build the database.
How to Check psql Client Version?
The psql client is a command-line interface that connects us to the PostgreSQL server. Checking the client version helps ensure that it’s compatible with the server.
This command is similar to the one mentioned earlier and returns the version of the PostgreSQL psql client installed on your system. Run the following command to check the PSQLclient version,
psql --version
Solve «Command ‘postgres’ not found» when Checking psql Client Version
If we are checking the psql client version and encounter the «Command ‘postgres’ not found» error, the solution is similar to what we discussed earlier. Following these steps will help us to resolve the «command not found» issue when checking the PSQL client version.
Step 1: Check if psql is Installed: Run the following command to verify if the PSQL client is installed
sudo apt list --installed | grep psql
Step 2: If it’s not listed: you can install it using
sudo apt install postgresql-client
Step 3: Add psql to PATH: If the client is installed but not accessible, add it to your system PATH using the following command
export PATH=$PATH:/usr/lib/postgresql/14/bin
Conclusion
In summary, quickly checking PostgreSQL versions via command line commands like psql --version and postgres -V is essential for effective database management. These methods enable users to confirm compatibility between client and server versions.
New versions of PostgreSQL are released by the PostgreSQL Global Development Group on a regular basis. The major releases of Postgres are usually scheduled yearly and focus on improving key features as well as performance and security. In contrast, minor releases are scheduled nearly every three months. The minor releases focus on resolving evolving security issues and fixing bugs.
Try the new PgManage (Open Source) and get rid of PgAdmin!
If you intend to implement new software, you should check if it is compatible with your Postgres version, whether the latest security patch is available, etc. In such cases, knowing which Postgres version is active on your system might be useful.
Contact us today for all your Postgres and Open Source consulting and support needs.
Quick Outline
This blog post will teach you how to check the current version of Postgres running on your system using the following content.
- How to Check/Get Postgres Version on Windows?
- How to Check/Get Postgres Version on macOS?
- How to Check/Get Postgres Version on Linux?
- Final Thoughts
So, let’s get started!
How to Check/Get Postgres Version on Windows?
To check the Postgres version on the Windows operating system different methods like CMD (Command Prompt), psql (SQL Shell), and pgAdmin are used. All these methods will be discussed in this section using appropriate screenshots:
Method 1: How to Check PostgreSQL Version Using the Command Prompt
Follow the below-given stepwise guidelines to check the currently installed PostgreSQL version on your system via the command prompt.
Step1: Access Bin Directory
Firstly, open the command prompt and run the following command to navigate to the Postgres bin folder:
cd C:\Program Files\PostgreSQL\14\bin
Hit the “Enter” button to access the desired directory/path:
The above snippet indicates the successful entry into the “bin” directory.
Step2: Check Postgres Version
Now execute the “psql -v” command to check the Postgres version:
psql -V
Alternatively, you can execute the “psql –version” command to find the Postgres version:
psql –version
The output shows that the “Postgres 14.4” version is running on your computer.
Method 2: How to Check the PostgreSQL Version Using SQL Shell
SQL Shell is a default Postgres terminal that helps us execute different SQL commands and meta-commands. Different commands like “SELECT VERSION()”, and “SHOW_VERSION” can be executed from SQL Shell to Check the Postgres version.
Launch the SQL Shell, fill in the login details, and run the below command to check which version of PostgreSQL is running on your machine:
SELECT VERSION();
Alternatively, you can utilize the following command to check the server version:
SHOW SERVER_VERSION;
The output proves that the specified command returns the PostgreSQL version.
Method 3: How to Check PostgreSQL Version Using pgAdmin
pgAdmin is a feature-rich GUI-based tool that is an open-source and freely available management tool for Postgres. Using pgAdmin, you can find the current Postgres version either manually or by executing SQL queries.
To check the PostgreSQL version via pgAdmin, users can follow the below-mentioned steps:
Step 1: Expand “Servers” Tree
Open the pgAdmin, specify the superuser password, and left-click on the “Servers” tree to expand it:
Step 2: Select Properties
Left-click on the “PostgreSQL” located under the “Servers” tree, and then click on the “Properties” tab:
Under the properties tab, you can check the currently installed PostgreSQL version on your system.
Note: You can also execute the “SELECT VERSION();” and “SHOW SERVER_VERSION;” commands in the pgAdmin’s query tool.
How to Check/Get Postgres Version on macOS?
Mac users can find the Postgres version either by using a terminal or pgAdmin. To find the Postgres version via pgAdmin, perform the same steps as we did for Windows. While to check the PostgreSQL version via the Mac Terminal, execute the command:
postgres -V
How to Check/Get Postgres Version on Linux?
To find the Postgres version on Linux, the “SQL Shell”, “pg_config” utility, the “dpkg” command, and the “apt-cache” command are used. All these methods are discussed with practical illustration in our dedicated guide on “Check Postgres Version on Linux”.
Final Thoughts
PostgreSQL is among the top databases that keep its users up-to-date by releasing new versions regularly. Postgres users can upgrade the currently used version to the latest one to avail the latest features. But before that, it’s recommended to check which Postgres version you are currently using.
To do that on the Windows system, users can run the “psql —version” command from the Command Prompt, the “SELECT VERSION()” command from SQL Shell, or use pgAdmin’s properties tab. Linux users can use the “SQL Shell”, “pg_config” utility, the “dpkg” command, or the “apt-cache” command to check the currently installed Postgres version. This blog post explained various approaches for checking the PostgreSQL version on Windows, MacOS, and Linux via practical demonstration.
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.
Get Your Free Linux training!
Join our free Linux training and discover the power of open-source technology. Enhance your skills and boost your career!
Start Learning Linux today — Free!
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.