Which version of PostgreSQL am I running? – Dev

The best answers to the question “Which version of PostgreSQL am I running?” in the category Dev.

QUESTION:

I’m in a corporate environment (running Debian Linux) and didn’t install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don’t have shell access to the server running the database.

ANSWER:

I believe this is what you are looking for,

Server version:

pg_config --version

Client version:

psql --version

ANSWER:

Run this query from PostgreSQL:

SELECT version();

ANSWER:

If you’re using CLI and you’re a postgres user, then you can do this:

psql -c "SELECT version();"

Possible output:

                                                         version                                                         
-------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 (Debian 11.1-3.pgdg80+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u2) 4.9.2, 64-bit
(1 row)

ANSWER:

Using CLI:

Server version:

$ postgres -V  # Or --version.  Use "locate bin/postgres" if not found.
postgres (PostgreSQL) 9.6.1
$ postgres -V | awk '{print $NF}'  # Last column is version.
9.6.1
$ postgres -V | egrep -o '[0-9]{1,}\.[0-9]{1,}'  # Major.Minor version
9.6

If having more than one installation of PostgreSQL, or if getting the “postgres: command not found” error:

$ locate bin/postgres | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/pgsql-9.3/bin/postgres -V 
postgres (PostgreSQL) 9.3.5
/usr/pgsql-9.6/bin/postgres -V 
postgres (PostgreSQL) 9.6.1

If locate doesn’t help, try find:

$ sudo find / -wholename '*/bin/postgres' 2>&- | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/pgsql-9.6/bin/postgres -V 
postgres (PostgreSQL) 9.6.1

Although postmaster can also be used instead of postgres, using postgres is preferable because postmaster is a deprecated alias of postgres.

Client version:

As relevant, login as postgres.

$ psql -V  # Or --version
psql (PostgreSQL) 9.6.1

If having more than one installation of PostgreSQL:

$ locate bin/psql | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/bin/psql -V 
psql (PostgreSQL) 9.3.5
/usr/pgsql-9.2/bin/psql -V 
psql (PostgreSQL) 9.2.9
/usr/pgsql-9.3/bin/psql -V 
psql (PostgreSQL) 9.3.5

Using SQL:

Server version:

=> SELECT version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

=> SHOW server_version;
 server_version 
----------------
 9.2.9

=> SHOW server_version_num;
 server_version_num 
--------------------
 90209

If more curious, try => SHOW all;.

Client version:

For what it’s worth, a shell command can be executed within psql to show the client version of the psql executable in the path. Note that the running psql can potentially be different from the one in the path.

=> \! psql -V
psql (PostgreSQL) 9.2.9