How To Check Postgres Version Using SQL Queries And Terminal Commands
Knowing how to check your PostgreSQL version is essential for maintaining database compatibility, troubleshooting errors, and planning security patches. Database administrators and developers can quickly identify their running server or client release using standard SQL commands, system functions, or direct command-line utilities.
Pre-Operation & Planning Checklist
Verifying your database version requires minimal system resources, but understanding your access level and environment scope is vital for executing the correct diagnostic commands without triggering permission errors.
- Essential tools and interfaces: Command-line terminal access, an interactive PostgreSQL terminal session (psql), or a database management GUI like pgAdmin or DBeaver.
- Mandatory prerequisite knowledge: Basic familiarity with SQL syntax, terminal navigation, and your operating system's command prompt (Bash, Zsh, or PowerShell).
- Estimated duration and scope: Less than two minutes to run a single command across local development environments, containerized deployments, or enterprise production servers.
Step-by-Step PostgreSQL Version Inspection Workflow
Step 1: Execute the Version SQL Query
The most reliable method to check the exact server version from within any SQL client is by querying the database system functions. Open your preferred database management tool or interactive terminal and execute the query selecting version open parentheses close parentheses. This built-in function returns a comprehensive string detailing the PostgreSQL release number, the underlying operating system architecture, and the compiler used during installation.
Pro-Tip: If you need to extract only the numeric version for programmatic use in migration scripts or deployment pipelines, query the server version num parameter instead of the full text string. Execute selecting current setting open parentheses single quote server version num single quote close parentheses to receive a clean integer representing the major and minor release tree.
Step 2: Query the System Catalog Tables
For relational database environments where you need to verify version parameters programmatically inside stored procedures or monitoring scripts, you can inspect the PostgreSQL system catalogs. Execute a query selecting the version parameter from the pg control settings view, or check the system administrative tables for detailed build metadata.
Warning: Direct access to system catalogs and administrative views like pg control settings typically requires superuser privileges or membership in the pg read all settings role. Standard users without these permissions will encounter permission denied errors and should rely on the standard version function outlined in step one.
Step 3: Use the Command-Line Terminal Utility
If you do not have an active database session open, you can check the installed PostgreSQL client and server versions directly from your operating system command line. Open your terminal and type psql space hyphen capital V to output the version of the interactive terminal application installed on your workstation. To check the version of the active database server daemon running on your host machine, type postgres space hyphen capital V or query the service manager depending on your Linux distribution or Windows service configuration.
Step 4: Inspect Containerized and Cloud Deployments
When working with PostgreSQL instances running inside Docker containers, Kubernetes pods, or managed cloud services, standard command-line access requires targeted execution wrappers. To check the version of a containerized PostgreSQL instance, run the docker exec command targeting your container ID followed by psql space hyphen U space your username space hyphen c space version semicolon. For managed cloud providers such as Amazon RDS or Google Cloud SQL, execute the version query through the web-based query editor provided in your cloud console.
PostgreSQL - How to show all columns with indexes? | TablePlus
PostgreSQL Diagnostic Methods Comparison
| Diagnostic Method | Target Component | Access Level Required | Output Format | Best Use Case |
|---|---|---|---|---|
| SELECT version(); | Database Server | Standard User | Detailed String | General inspection and documentation |
| SHOW server_version; | Database Server | Standard User | Clean String | Configuration checks and quick logging |
| psql -V | Client Utility | OS Terminal | Version Number | Verifying client tool compatibility |
| postgres -V | Server Binary | Root / Admin | Version Number | System administration and packaging checks |
Common Inspection Failures and Field Fixes
Root Cause: Executing the psql command yields a command not found error in the terminal.
- Actionable Fix: The PostgreSQL binaries are not added to your operating system PATH environment variable. Locate your installation directory, add the bin folder to your system PATH, or specify the absolute file path to the psql executable when running your commands.
Root Cause: Running the version query returns a permission denied error or fails to connect to the database cluster.
- Actionable Fix: Verify that the PostgreSQL service daemon is actively running on your machine and that you are supplying the correct username, host address, and port number parameters during connection initialization.
Root Cause: The reported client version does not match the server version, causing unexpected syntax errors in modern SQL queries.
- Actionable Fix: Mismatched versions occur when an older psql client connects to a newly upgraded remote database server. Update your local PostgreSQL client packages using your operating system package manager, Homebrew, or official enterprise repositories to match the server release.
Frequently Asked Questions
How do I check the PostgreSQL version using pgAdmin?
Open the pgAdmin application, expand your server tree in the browser panel, and click on your target database server instance. Navigate to the Dashboard tab in the main interface, where the server version, uptime, and basic cluster statistics are displayed prominently. Alternatively, you can open the Query Tool and run the standard version function query to view the detailed build string.
What is the difference between client and server PostgreSQL versions?
The client version refers to the utility tools and libraries, such as psql or pg dump, installed on your local machine used to communicate with databases. The server version refers to the actual PostgreSQL database management system engine running on the host server that processes data storage, indexing, and transactions. Maintaining alignment between client and server versions prevents deprecation warnings and syntax parsing failures.
Can I check the PostgreSQL version without logging into the database?
Yes, you can check the server version without logging into an active SQL session by executing command-line utilities in your terminal. Use postgres space hyphen capital V to check the database engine binary version, or inspect your operating system package manager records using dpkg, rpm, or brew depending on your installation environment.
How do I find the major vs minor PostgreSQL version numbers?
PostgreSQL follows a versioning format where the first number represents the major release containing significant feature updates, and the trailing numbers represent minor bugfix and security releases. For example, in version 15.4, the major version is 15 and the minor release patch level is 4. You can parse these distinct values using the server version num configuration parameter.
Why does my SQL query return an unknown function error for version?
An unknown function error indicates that your query syntax contains a typo or that you are accidentally connected to a completely different database management system, such as MySQL or SQLite, which uses alternative syntax for version checks. Verify your database connection string and ensure you are executing valid PostgreSQL dialect commands.
Optimize your database management workflows by verifying your system configurations and upgrading outdated instances today.