Which version of PostgreSQL am I running?
Categories:
How to Determine Your PostgreSQL Version
Learn various methods to quickly identify the PostgreSQL version running on your system, from command-line tools to SQL queries.
Knowing the exact version of PostgreSQL you are running is crucial for compatibility, troubleshooting, and applying security patches. Whether you're a developer, a database administrator, or just curious, there are several straightforward ways to find this information. This article will guide you through the most common methods, covering both command-line utilities and SQL queries.
Method 1: Using the psql Command-Line Client
The psql command-line utility is the most common way to interact with PostgreSQL. It provides a simple command to display the server version directly. This method is ideal if you have psql installed and can connect to your PostgreSQL instance.
psql --version
Check psql client version
This command will show you the version of the psql client itself. While often the same as the server, it's not guaranteed. To get the server's version, you need to connect to the database and run a SQL query.
psql -c "SELECT version();"
Check PostgreSQL server version using psql
Alternatively, you can log into the psql prompt and then execute the SELECT version(); query.
psql
# \q to exit
Connect to psql prompt
SELECT version();
SQL query to get server version
Method 2: Using pg_config for Installation Details
The pg_config utility provides information about the installed version of PostgreSQL, including compilation options and library paths. This is particularly useful if you need to know details about how PostgreSQL was built or if you're developing extensions.
pg_config --version
Check PostgreSQL installation version
This command typically returns the major version number. For more detailed information, you can run pg_config without any arguments.
pg_config
Display full pg_config output
pg_config utility is usually found in the PostgreSQL installation's bin directory. Ensure this directory is in your system's PATH, or provide the full path to pg_config.Method 3: Checking the PostgreSQL Service Status
On Linux systems, you can often infer the PostgreSQL version by checking the status of the PostgreSQL service. The service name often includes the major version number.
sudo systemctl status postgresql
# Or for older systems:
sudo service postgresql status
Check PostgreSQL service status on Linux
The output will usually show the service name, which might look something like postgresql@14-main.service or postgresql-13.service, indicating the major version (e.g., 14 or 13).
flowchart TD
A[Start] --> B{Access to DB?}
B -- Yes --> C{psql client available?}
C -- Yes --> D["Run: psql -c \"SELECT version();\""]
C -- No --> E{pg_config available?}
E -- Yes --> F["Run: pg_config --version"]
E -- No --> G{Check service status?}
G -- Yes --> H["Run: sudo systemctl status postgresql"]
G -- No --> I[Consult documentation/logs]
B -- No --> I
D --> J[End]
F --> J
H --> J
I --> JDecision flow for finding PostgreSQL version
Method 4: Inspecting Data Directory and Log Files
If you cannot connect to the database or use command-line tools, you can sometimes find version information by inspecting the PostgreSQL data directory or log files. The data directory often contains a PG_VERSION file, and log files typically record the server version upon startup.
cat /var/lib/postgresql/data/PG_VERSION
# (Path may vary depending on installation)
Check PG_VERSION file in data directory
grep "PostgreSQL database server" /var/log/postgresql/postgresql-*.log
# (Log file path may vary)
Search log files for version information
PG_VERSION file only gives the major version. For minor versions and build details, connecting to the database with SELECT version(); is more reliable.