How To Check PostgreSQL Version: Complete Guide For Admins And Developers

To quickly check your PostgreSQL version, execute the SQL query SELECT version(); in your query editor, or run the terminal command postgres -V directly on your database host. These methods immediately return the exact major, minor, and security release versions of your database server to ensure compatibility and system security.

Pre-Verification Environment Assessment & Access Prerequisites

Before executing any version check commands, it is essential to understand the architectural layout of your database environment. PostgreSQL operates on a client-server model. This means the version of the client tool you are using to connect to the database (such as psql or pgAdmin) can differ significantly from the version of the engine running on the database server itself. Running mismatched client-server environments can lead to unexpected syntax errors, failed backups, or administrative failures during migrations.

To successfully inspect your PostgreSQL environment, you must determine your entry point and secure the necessary privileges. Use the following structured checklist to prepare your environment:

• Access Credentials & Privileges: For command-line operating system access, you need access to the terminal with a user account that has permission to execute postgres or pg_config binaries (often the postgres system user). For SQL-based verification, you require database login credentials (username and password) with basic connection rights. Superuser privileges are not required to check versions, as version metadata is readable by all authenticated roles.
• System Tools & Shells: Ensure you have an active command line interface such as Bash, Zsh, or PowerShell, or a graphical administration tool like pgAdmin, DBeaver, or command line client utilities installed.
• Target Infrastructure Definition: Identify whether your database is running as a bare-metal local installation, a system service (via systemd), a containerized instance (Docker or Kubernetes), or a managed cloud database service such as Amazon RDS, Google Cloud SQL, or Azure Database for PostgreSQL.
• Estimated Duration: Under normal conditions, identifying your PostgreSQL version takes less than two minutes across any of the methods detailed below.

Comprehensive Verification Protocols Across Different Environments

Depending on your access level and infrastructure setup, choose one of the following step-by-step protocols to extract the exact PostgreSQL version.

Step 1: Querying the Database via SQL Commands

If you can establish a database connection through a client tool, a web dashboard, or an application script, executing a direct SQL query is the most reliable way to obtain the true running engine version of the PostgreSQL server.

First, open your SQL editor, query tool, or command-line psql prompt. Run the following command:

SELECT version();

This query returns a single string containing the full PostgreSQL version, the compiler used, the target operating system architecture, and the build date. For example, the output will look like: PostgreSQL 15.4 (Ubuntu 15.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc, 64-bit.

If you need a cleaner, standardized format that only displays the version number without compilation metadata, execute this alternative command:

SHOW server_version;

This returns a brief string like: 15.4.

For automated provisioning scripts, database migrations, or application code that needs to make logic decisions based on the database version, you can query a structured integer format using:

SHOW server_version_num;

This returns a clean, six-digit integer, such as 150004. In this format, the first two digits represent the major version (15), the next two represent the minor release (00), and the final two indicate the patch version (04). This structured integer format avoids complex regular expression parsing in your code.

Pro-Tip: Up to version 9.6, PostgreSQL utilized a three-part version numbering system (e.g., 9.6.22). Since version 10, the numbering system has been simplified to a two-part format (e.g., 15.4), where the first number represents the major release and the second represents the minor patch.

Step 2: Running Terminal Commands on the Host Server

If you have SSH access to the server hosting the database, or if you are running PostgreSQL locally on your workstation, you can check the version directly via the operating system command line.

Open your terminal emulator and type the following command:

postgres --version

Alternatively, you can use the shorthand flag:

postgres -V

This command queries the main postgres server executable and returns its version directly to the standard output, showing a clean string like: postgres (PostgreSQL) 16.1.

If your terminal returns an error stating that the command was not found, it usually means the PostgreSQL binaries are not included in your operating system's PATH variable. You must specify the absolute path to the binary.

On typical Debian or Ubuntu installations, run:

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

On Red Hat Enterprise Linux, Fedora, or Rocky Linux, run:

/usr/pgsql-16/bin/postgres -V

On macOS installations managed by Homebrew, check the version by targeting:

/opt/homebrew/opt/postgresql@16/bin/postgres -V

Make sure to replace the version number in these directory paths with the corresponding version installed on your file system.

If you are a developer looking to compile extensions or check compile-time configurations, use the PostgreSQL configuration tool utility by running:

pg_config --version

This utility outputs the active PostgreSQL version and is highly reliable because it is hard-linked to the development library headers installed on the server.

Warning: Be cautious when running psql -V or psql --version. This command queries the psql interactive terminal client version, not the server. If your workstation has an older psql client but connects to a newer remote database server, this command will output the wrong version number for your data engine.

Step 3: Extracting Version Data in Docker Containers

For containerized microservice architectures, you do not need to log into the container's interactive shell manually to verify the database version. Instead, you can pass the execution command directly from your host system's command line.

If your container is actively running, execute the following command:

docker exec -it postgres -V

Replace the placeholder text with your actual container name or container ID. The output will immediately print the internal PostgreSQL version of the container binary to your terminal screen.

If you prefer to verify the version by executing an SQL statement inside the container environment, run:

docker exec -it psql -U postgres -c "SELECT version();"

This command connects to the database inside the container as the default postgres user and runs the version query, cleanly displaying the structured compiler information.

Step 4: Utilizing Graphical User Interfaces (GUIs)

For database administrators who prefer visual dashboards to command lines, checking the database version is simple.

In pgAdmin, navigate to the Browser panel on the left side of the screen. Expand the Servers group and select your active server connection. Once highlighted, click on the Properties tab in the main tab pane on the right. Scroll down to find the Server type or Version row, which displays the exact engine version running on the host.

In DBeaver, establish a connection to your target database. Double-click the connection name in the Database Navigator panel to expand it. Right-click on the connection name and select Connection Properties. Navigate to the General section, or simply look at the Properties panel at the bottom of the navigation pane while the connection is highlighted. The target database version will be displayed next to the database driver details.

Technical Parameters of PostgreSQL Version Retrieval Methods

Choosing the correct method for checking your version depends on your access privileges and the specific requirements of your workflow. The table below compares the primary version check methods, their output formats, and their best use cases.

Common Connection Errors & Version Query Failures

While checking your PostgreSQL version is normally a straightforward task, various environmental conflicts, configuration locks, or permission blocks can cause these commands to fail. Below are real-world failure scenarios and how to resolve them.

Scenario 1: 'postgres' command not found or not recognized

• Root Cause: The system's PATH environment variable does not include the directory where the PostgreSQL binaries are installed. This often happens on clean Linux installations or custom macOS setups.
• Actionable Fix: Locate your installation path manually or use your operating system's search utilities to find the postgres executable. On Linux, run the command find /usr -name "postgres" to locate the absolute path. Once located, call the binary directly using its full path, or append the directory permanently to your PATH profile. You can do this by adding export PATH=$PATH:/usr/lib/postgresql/16/bin to your user home directory's profile configuration file (such as .bashrc or .zshrc) and reloading the shell.

Scenario 2: Connection Refused / Is the server running locally?

• Root Cause: You tried to run psql or a SQL version check command, but the PostgreSQL database server daemon is not running, or it is listening on a port other than the default port 5432.
• Actionable Fix: Verify if the system service is active. On systemd-managed Linux distributions, execute systemctl status postgresql to check the status. If the service is stopped, start it by running sudo systemctl start postgresql. If the service is running but connections are still refused, inspect the postgresql.conf configuration file to confirm the port parameter, and test the connection by specifying the explicit port using the command psql -p -U postgres.

Scenario 3: Peer Authentication Failed / Access Denied

• Root Cause: Your local operating system user does not match the database role authorized to connect to the database via local UNIX domain sockets, or the pg_hba.conf security file limits connection permissions for your user.
• Actionable Fix: Switch to the default administrative OS account by executing sudo -i -u postgres. Once running under this account, execute your commands or connect to the database shell by typing psql. If you need to log in as a specific user, bypass peer authentication by using host loopback parameters with the command psql -h 127.0.0.1 -U postgres.

Scenario 4: Command returns client version instead of server version

• Root Cause: Running psql -V only returns the version of the interactive command-line interface tool installed on your machine. If you are connecting to a remote cloud server, your local psql tool version can be completely different from the database engine version running in the cloud.
• Actionable Fix: You must establish a connection to the database first, and then run an internal SQL query. Execute psql -h -U -d to connect. Once successfully logged in and presented with the active database prompt, run the command SELECT version(); to query the server itself.

Frequently Asked Questions

What is the difference between psql version and postgres version?

The psql version refers to the command-line terminal client software used to interact with PostgreSQL databases. The postgres version refers to the database engine software itself, which processes queries and manages storage on the host server. These two versions can be different, so you should always check the database engine version directly using SQL queries to ensure compatibility.

How do I check the PostgreSQL version in Ubuntu or Linux?

On Ubuntu or Debian systems, you can check the PostgreSQL version by running the terminal command pg_config --version or by querying the package manager with the command dpkg -l | grep postgresql. Alternatively, you can switch to the administrative user by running sudo -u postgres psql -c "SELECT version();" in your system terminal.

Can I check the PostgreSQL version without database connection credentials?

Yes, you can check the version of the installed software binaries without database access by running the command postgres -V or pg_config --version on the host server's command line. However, this only tells you the version of the files installed on the disk, and does not guarantee that a database server is currently running or using that specific version.

How do I find the major version of PostgreSQL programmatically?

To find the major version of PostgreSQL in a script or application, run the SQL query SHOW server_version_num;. This returns a clean six-digit integer (such as 150004). You can extract the major version by dividing this integer by 10000, which cleanly isolates the major release number (such as 15) without requiring complex string parsing.

How do I check my PostgreSQL version in AWS RDS or cloud environments?

Because cloud platforms block direct SSH command-line access to the host server, you cannot run binary commands like postgres -V. Instead, you must connect to your managed cloud instance using an external database client (like psql or pgAdmin) and run the SQL query SELECT version(); or SHOW server_version; to retrieve the running version.

Optimize and Secure Your PostgreSQL Infrastructure

Keeping your database system secure and running at peak performance requires routine updates and regular configuration reviews. Take the next step in optimizing your database environments by automating your system updates, scheduling periodic version checks, and aligning your development systems with your production environments.