How To Check PostgreSQL Version

One of the first questions I ask when stepping into a new project is: “What version of PostgreSQL are we running?”. Whether you’re a developer or a DBA, knowing how to check your PostgreSQL version is the fundamental first step in database management. In this article, I’ll show you exactly how to find that information using every tool.

How To Check PostgreSQL Version

Method 1: The SQL Query (For Active Sessions)

If you are already logged into a database tool like psql, DBeaver, or pgAdmin, the fastest way to get your version is a simple SQL query.

Using the version() Function

This is the “gold standard” query. It returns a detailed string containing the version, the operating system, and the compiler used.

SQL

SELECT version();

Using SHOW server_version

If you just want the clean version number without the extra system “noise,” this is your best approach:

SQL

SHOW server_version;

Using server_version_num

For those of you writing automation scripts, you’ll want the numeric version. This returns an integer (e.g., 180002 for version 18.2), making it much easier to handle “greater than” or “less than” logic in your code.

SQL

SHOW server_version_num;

Command	Best For	Example Output
SELECT version();	Detailed system info	PostgreSQL 18.2 on x86_64-pc-linux-gnu...
SHOW server_version;	Human-readable version	18.2
SHOW server_version_num;	Scripting/Automation	180002

Method 2: The Command Line (Before You Connect)

If you’re a system administrator, you might be working directly in the Linux terminal or Windows Command Prompt. You can check the version without even logging into the database.

Checking the Client Version (psql)

The psql utility is the front-end tool. Keep in mind that the client version and the server version can be different!

Bash

psql --version
# OR
psql -V

Checking the Server Version (postgres)

If you have access to the server binaries, you can query the engine itself:

Bash

postgres --version
# OR
postgres -V

Expert Note: If you get a "command not found" error, it usually means your PATH environment variable isn’t set up correctly. This is common in standard Linux installations in AWS EC2 instances. You may need to use the full path, such as /usr/lib/postgresql/18/bin/postgres -V.

Method 3: Checking via GUI Tools (pgAdmin & DBeaver)

If you like to use a GUI approach.

In pgAdmin 4:

1. Open pgAdmin and connect to your server.
2. Select your server in the Browser tree on the left.
3. Click the Properties tab in the main dashboard.
4. Look for the Version field.

In DBeaver:

1. Right-click your connection in the Database Navigator.
2. Select Edit Connection.
3. Click on Test Connection. The resulting pop-up will show the server version details.

Understanding PostgreSQL Versioning Syntax

Starting with version 10, PostgreSQL moved to a simplified Major.Minor format. This was a huge win for clarity.

• Major Version (e.g., 18): Released once a year (usually in September). This brings new features and sometimes breaking changes.
• Minor Version (e.g., .2): Released quarterly. These are strictly for bug fixes and security patches. They do not change the internal data format.

Professional Best Practices for Version Management

• Stay Current on Minors: Always apply minor updates within 30 days of release. They are low-risk and keep you secure.
• Plan for Majors: Treat a major version upgrade (e.g., 17 to 18) as a project. Test your application in a staging environment first.
• Check EOL: PostgreSQL versions are supported for 5 years. If you are still running version 12 in 2026, you are officially “out of support” and at high risk.

Summary of Commands

Context	Command
Inside SQL Shell	SELECT version();
Inside SQL Shell	SHOW server_version;
Linux/macOS Terminal	psql --version
Windows CMD	postgres -V
Automation	SHOW server_version_num;

Conclusion

Knowing how to check your PostgreSQL version is about ensuring your environment is compatible, secure, and performing at its peak. Whether you prefer the command line or a GUI, make it a habit to verify your version regularly.

You may also like the following articles:

• PostgreSQL Latest Version
• What Is Postgresql Used For
• Why Use PostgreSQL
• PostgreSQL datatype text vs varchar