How to Check PostgreSQL Version Without Shell Access

You can determine your PostgreSQL version using several SQL queries available through client tools like Navicat and phpPgAdmin, or by checking the connection properties in these interfaces. The most reliable method is to execute SELECT version(); or SHOW version; in your query tool, which will return detailed version information including the build date and compatibility level. Additionally, you can check the PostgreSQL system catalog tables like pg_catalog.pg_settings for version-related parameters.

Contents

• Using SQL Queries
• Checking in Navicat
• Checking in phpPgAdmin
• Alternative Methods
• Corporate Environment Considerations
• Troubleshooting Tips

Using SQL Queries

The most reliable way to determine your PostgreSQL version is through SQL queries that you can execute in any client tool. Here are the most effective methods:

Primary Version Query

Execute this query in your SQL editor:

SELECT version();

This query returns detailed version information in a single string, including:

• PostgreSQL version number (e.g., 14.7, 15.2)
• Build date and time
• Compiler information
• Platform details
• Optional version suffixes (e.g., “devel”, “beta”, “rc1”)

Alternative SQL Methods

If you need just the version number in a more structured format:

-- Get version as a single field
SELECT current_setting('server_version');

-- Get version components separately
SELECT 
    current_setting('server_version_num') as version_num,
    current_setting('server_version') as version_string,
    current_setting('server_version_name') as version_codename;

System Catalog Query

You can also query the system catalog tables:

SELECT * FROM pg_catalog.pg_settings 
WHERE name IN ('server_version', 'server_version_num', 'server_version_name');

Note: These queries work in all PostgreSQL versions from 8.1 onwards and require only standard privileges that most users have in corporate environments.

Checking in Navicat

Navicat provides multiple ways to view PostgreSQL version information through its graphical interface:

Connection Properties Method

1. Connect to your database using Navicat
2. Right-click on the connection in the connection tree
3. Select Properties or Connection Properties
4. Look for the Version field which typically displays the PostgreSQL version

Server Information Panel

1. Connect to your database
2. Double-click on the database name in the connection tree
3. The Server Information panel usually shows the PostgreSQL version
4. If not visible, check the Dashboard or Server Status tab

Query Result Method

1. Open the Query Editor in Navicat
2. Execute SELECT version(); or SELECT current_setting('server_version');
3. The results will show in the grid below the query editor

Connection Dialog Details

1. Before connecting, you can sometimes see version information in the connection dialog
2. Test the connection - Navicat often displays the server version in the test results

Checking in phpPgAdmin

phpPgAdmin, being a web-based tool, offers several convenient ways to check PostgreSQL version:

Main Dashboard

1. Log in to phpPgAdmin
2. Click on your database server in the left navigation
3. The main dashboard typically displays the PostgreSQL version at the top or in the server information panel

Server Status Page

1. Navigate to your database server
2. Click on the “Status” tab
3. Look for PostgreSQL version information in the server status details

SQL Query Method

1. Click on the “SQL” tab in phpPgAdmin
2. Execute any of the version queries mentioned earlier:
   sql

   SELECT version();
   SELECT current_setting('server_version');
   

3. View the results in the query output area

phpPgAdmin Footer Information

Some installations of phpPgAdmin display the PostgreSQL version in the footer or header area of the interface, particularly when connected to the server.

Alternative Methods

Using pgAdmin

If you have access to pgAdmin (even the web version), you can:

1. Connect to your server
2. Look at the connection tree - the version is often displayed next to the server name
3. Check the Dashboard for server information

Application Connection Strings

Your application’s connection logs or configuration might contain version information:

• Application error logs when connecting
• Connection pool logs if using connection pooling
• ORM debug output when applications connect

Database Metadata Tables

Query system tables that might contain version information:

-- Check for version-related extensions
SELECT * FROM pg_extension WHERE extname LIKE '%version%';

-- Check for version-related settings
SELECT name, setting FROM pg_settings 
WHERE name LIKE '%version%' OR name LIKE '%compatibility%';

Client Library Version

Sometimes the client library version can give clues:

-- PostgreSQL client library version
SELECT version();

Corporate Environment Considerations

In corporate settings, you may encounter several challenges when trying to determine PostgreSQL version:

Limited Permissions

Many corporate databases restrict user permissions:

• Standard users may not have access to all system tables
• Read-only access is common for non-privileged accounts
• Query monitoring may be in place, so use simple queries

Multiple Environments

Corporate environments often have:

• Development, staging, and production servers with different versions
• Database clusters with mixed versions
• Containerized or virtualized environments

Compliance and Security

Be aware that:

• Version queries may be logged or monitored
• Some queries might be blocked by security policies
• Information disclosure concerns might exist in sensitive environments

Documentation and Support

In corporate settings:

• Check internal documentation first
• Contact database administrators if standard methods fail
• Use approved channels for version information requests

Troubleshooting Tips

Query Returns Empty Results

If version queries don’t return data:

1. Check permissions - you might need basic SELECT privileges
2. Try simpler queries like SELECT 1; first to test connectivity
3. Contact your DBA if you suspect permission issues

Version Information is Incomplete

If you get partial version information:

1. Try different queries from the methods above
2. Check for database-specific configurations that might hide version details
3. Look for environment-specific version suffixes or custom builds

Connection Issues

If you can’t connect to determine version:

1. Check connection parameters in your client tools
2. Verify network access to the database server
3. Contact your network team if firewall rules are blocking access

Corporate Restrictions

If you face corporate restrictions:

1. Use the simplest possible queries to minimize visibility
2. Check with your compliance officer about information disclosure policies
3. Request version information through proper channels if needed

Conclusion

Determining your PostgreSQL version without shell access in a corporate environment is achievable through several reliable methods. The most effective approach is using SQL queries like SELECT version(); or SELECT current_setting('server_version'); which work in all standard client tools including Navicat and phpPgAdmin. For users who prefer graphical interfaces, both Navicat and phpPgAdmin display version information in their connection properties and dashboard areas. When working in corporate settings, always consider permission restrictions, information security policies, and the potential need to involve database administrators if standard methods don’t work. By using these methods, you can confidently identify your PostgreSQL version even in environments with limited access.

Sources

1. PostgreSQL Official Documentation - System Information Functions
2. Navicat User Guide - Connection Properties
3. phpPgAdmin Documentation - Server Status