Discovering Your PostgreSQL Version

Discovering Your PostgreSQL Version

PostgreSQL is a powerful, open-source object-relational database system. Knowing the version of your PostgreSQL installation is crucial for ensuring compatibility, applying security patches, and utilizing new features. This tutorial outlines several methods to determine your PostgreSQL version, catering to various access levels and environments.

1. Using SQL Queries

The most direct way to determine the server version is through SQL queries executed within a PostgreSQL client (like psql, pgAdmin, or DBeaver).

SELECT version();

This query returns a detailed version string including the PostgreSQL version, the operating system, the compiler used, and whether it’s a 64-bit build.

For a more concise version number, you can use:

SHOW server_version;

This will output just the version number, for example 9.6.1.

If you need the version as a numerical value (e.g., for programmatic comparisons), use:

SELECT current_setting('server_version_num');
-- or
SHOW server_version_num;

This returns an integer representing the version, such as 90603 (representing version 9.6.3). This is especially useful when you need to compare versions within an application or script.

2. Using Command-Line Tools

If you have shell access to the server, several command-line tools can reveal the PostgreSQL version.

a) postgres --version or postgres -V:

This command shows the version of the postgres server executable. This is often the most reliable way to determine the server version.

postgres --version
# Example output: postgres (PostgreSQL) 14.7

b) psql --version or psql -V:

This displays the version of the psql client. Note that the client version might differ from the server version, particularly if the client and server are on different machines or have been updated independently.

psql --version
# Example output: psql (PostgreSQL) 14.2

c) pg_config --version:

pg_config is a utility that provides information about the installed PostgreSQL installation. This is particularly useful for build and linking processes.

pg_config --version
# Example output: 14.7

d) Locating Executables (if postgres or psql are not in your PATH):

If the postgres or psql command isn’t recognized, you might need to locate the executable and then run it with its full path.

• Using locate:

  locate bin/postgres | xargs -i xargs -t '{}' -V
  locate bin/psql | xargs -i xargs -t '{}' -V
  

• Using find (if locate fails):

  sudo find / -wholename '*/bin/postgres' 2>&- | xargs -i xargs -t '{}' -V
  sudo find / -wholename '*/bin/psql' 2>&- | xargs -i xargs -t '{}' -V
  

These commands search for the executables and then execute them with the -V (version) flag.

3. Important Considerations

• Client vs. Server: Remember that the psql client version might not always match the server version. For accurate server version information, always use SQL queries or the postgres --version command.
• Multiple Installations: If you have multiple PostgreSQL installations, the commands might return the version of the first installation found in your PATH. Use the full path to the executable or use SQL queries connected to the specific database instance to ensure you are getting the correct version.
• Restricted Access: In environments where you don’t have shell access (e.g., a managed database service), the SQL queries are your primary method for determining the version.