Which version of PostgreSQL am I running?

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 1

Run this query from PostgreSQL:

SELECT version();

Answer 2

I believe this is what you are looking for,

Server version:

pg_config --version

Client version:

psql --version

Answer 3

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

Answer 4

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 5

The accepted answer is great, but if you need to interact programmatically with PostgreSQL version maybe it's better to do:

SELECT current_setting('server_version_num'); -- Returns 90603 (9.6.3)
-- Or using SHOW command:
SHOW server_version_num; -- Returns 90603 too

It will return server version as an integer. This is how server version is tested in PostgreSQL source, e.g.:

/*
 * This is a C code from pg_dump source.
 * It will do something if PostgreSQL remote version (server) is lower than 9.1.0
 */
if (fout->remoteVersion < 90100)
    /*
     * Do something...
     */  

More info here and here.

Answer 6

Execute command

psql -V

Where

V must be in capital.

Answer 7

in shell psql.exe , execute

\! psql -V

Answer 8

Using pgadmin4 it can be seen by double clicking Servers > server_name_here > Properties tab > Version:

Version 3.5:

Version 4.1, 4.5:

Answer 9

A simple way is to check the version by typing psql --version in terminal

Answer 10

1. Using command line Server:

postgres -V

Client:

psql -V

2. Login to postgres then:

postgres=# select version();

Or from cli:

psql -c "SELECT version();"

3. Use VERSION special variable Login as postgres user:

sudo su - postgres

Then:

psql -c "\echo :VERSION"

Check out this guide here for full explaination

Answer 11

use VERSION special variable

$psql -c "\echo :VERSION"

Answer 12

In my case

$psql
postgres=# \g
postgres=# SELECT version();
                                                       version
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.21 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.6.real (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)

Hope it will help someone

Answer 13

The pg_config command will report the directory where the PostgreSQL programs are installed (--bindir), the location of C include files (--includedir) and object code libraries (--libdir), and the version of PostgreSQL (--version):

$ pg_config --version
PostgreSQL 9.3.6

Answer 14

Run this query from PostgreSQL: SELECT version();

Answer 15

If you are already using for tool(used DBeaver) to connect PostgreSQL, it will look like this :

Answer 16

Useful Queries to Chck PostgreSQL Database Version

bash-4.1$ psql
postgres=# SELECT version();
postgres=# SHOW server_version;

To Check PostgreSQL Client Version.

bash-4.1$ psql --version
psql (PostgreSQL) 12.1

Answer 17

If you have shell access to the server (the question mentions op does not have, but in case you have,) on a debian/ubuntu system

sudo apt-cache policy postgresql

which will output the installed version,

postgresql:
  Installed: 9.6+184ubuntu1.1
  Candidate: 9.6+184ubuntu1.1
  Version table:
 *** 9.6+184ubuntu1.1 500
        500 http://in.archive.ubuntu.com/ubuntu artful-updates/main amd64 Packages
        500 http://in.archive.ubuntu.com/ubuntu artful-updates/main i386 Packages
        500 http://security.ubuntu.com/ubuntu artful-security/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu artful-security/main i386 Packages
        100 /var/lib/dpkg/status
     9.6+184ubuntu1 500
        500 http://in.archive.ubuntu.com/ubuntu artful/main amd64 Packages
        500 http://in.archive.ubuntu.com/ubuntu artful/main i386 Packages

where the Installed: <version> is the installed postgres package version.

Answer 18

For the current version of PgAdmin: 4.16 at the time of writing.

1. Select the DB server whose version you need.
2. Click on the properties tab in the right pane.

See screenshot below:

Answer 19

This is quite an old question with many good answers. I found that from version 12 onwards, simply invoking the client tells me what I need to know, but I ran them on the server's shell. Examples below with output.

When I was on version 12:

$ sudo su postgres -c "psql"
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))

I read this as both the client and the server are at version 12.

After I upgraded Ubuntu from 20.04 to 21.04:

$ sudo su postgres -c "psql"
psql (13.4 (Ubuntu 13.4-0ubuntu0.21.04.1), server 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))

It's telling me clearly that the client is on version 13 but the server is still on 12, as I confirmed:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Notice, by the way, this misleading result, at this stage:

$ pg_config --version
PostgreSQL 13.4 (Ubuntu 13.4-0ubuntu0.21.04.1)

After I upgraded to version 14:

$ sudo su postgres -c "psql"
psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
Type "help" for help.

postgres=#

Again, I interpret it as both the client and the server being on version 14, confirmed once more:

$ pg_lsclusters
Ver Cluster Port Status                Owner    Data directory              Log file
12  main    5432 down,binaries_missing postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
14  main    5433 online                postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

This version is, by the way, the same obtained by running the SELECT version(); query.

Answer 20

Don’t know how reliable this is, but you can get two tokens of version fully automatically:

psql --version 2>&1 | tail -1 | awk '{print $3}' | sed 's/\./ /g' | awk '{print $1 "." $2}'

So you can build paths to binaries:

/usr/lib/postgresql/9.2/bin/postgres

Just replace 9.2 with this command.

Answer 21

If Select version() returns with Memo try using the command this way:

Select version::char(100) 

or

Select version::varchar(100)