1461

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.

Improve this question

23 Answers 23

Reset to default
2249

Run this query from PostgreSQL:

SELECT version();
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

No result in my case in terminal on Ubuntu
@Timo, this is a query to be run through PostgreSQL. This could be done through pgAdmin, or any other mechanism for running a query. Were you trying to run it from an Ubuntu shell? (this won't work)
This can also be ran from the command line with psql -c 'SELECT version();'
You can running directly from the bash specifying the postgres db as follow: psql postgres -c 'SELECT version();'
@Frank H. Using: sudo -u postgres psql postgres -c 'SELECT version()' | grep PostgreSQL should get you past "role 'username' does not exist".
|
715

I believe this is what you are looking for,

Server version:

pg_config --version

Client version:

psql --version
Improve this answer

6 Comments

Thanks! This works for when shell access is available. Unfortunately in my case I don't have that access; I've updated the question.
Sometimes the client version is what you want to know anyway.
As Frank notes, this can be deceiving. psql will connect to whatever postmaster/postgres database process is running and the database engine may not be the same version as the psql command.
pg_config --version could be misleading, e.g. if you upgrade an Ubuntu server and don't run pg_upgradecluster, pg_config will show the new version instead of the one you're still using.
just this works for me: pg_config --version The command: psql --versionnot works, complains for this: dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.7.dylib Referenced from: /usr/local/bin/psql Reason: image not found
|
255

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
Improve this answer

4 Comments

Thank you !, the SHOW server_version; is very handy in scripts to avoid having to parse in the long string of SELECT version();.
Thanks a lot. People don't realize that for issuing SQL commands you have to know at least one role to connect to the database. But with postgres -V you don't have to know to connect to the database to know its version.
One line in CLI assuming superuser access: psql postgres -c "SHOW server_version" -t -A. -t removes headers, -A removes alignment whitespace.
Thank you for SHOW server_version_num in particular, which is handy for inequalities.
82

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)
Improve this answer

Comments

53

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.

Improve this answer

Comments

37

Execute command 

psql -V

Where

V must be in capital.

Improve this answer

3 Comments

That is the psql (client) version, not the version of the Postgres server.
To reviewers: if the answer is wrong but is an answer (like the comment implies), don't recommend deletion: downvote! See, for example, "You're doing it wrong: A plea for sanity in the Low Quality Posts queue" and "When an answer answers the wrong question, is it Not An Answer?". This is an answer. You may not agree with it, but it is an attempt to answer the question.
This is the best answer. Many of the answers above return the version of the client not the server.
20

in shell psql.exe , execute

\! psql -V
Improve this answer

1 Comment

This will give him the version of the postgre client. I thin that OP is asking for the sql server version.
20
  1. Using command line Server:
postgres -V

Client:

psql -V
  1. Login to postgres then:
postgres=# select version();

Or from cli:

psql -c "SELECT version();"
  1. Use VERSION special variable Login as postgres user:
sudo su - postgres

Then:

psql -c "\echo :VERSION"

Check out this guide here for full explaination

Improve this answer

Comments

15

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

Version 3.5:

pgadmin4 show postgres version. Servers > server_name > Properties > Version

Version 4.1, 4.5:

enter image description here

Improve this answer

3 Comments

Don't see it. Using pgadmin 4 version 4.1.
@faintsignal added screenshot for pgadmin4 v4.1, that versioning jump happened pretty quick.
Oh, my mistake. I thought you were right-clicking the server, which leads to a different "Properties" dialog. Thanks!
13

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

Improve this answer

1 Comment

Note that this will only tell you the client version, which could quite possibly be different to the server. See @simhumileco's answer for the canonical way.
11

use VERSION special variable

$psql -c "\echo :VERSION"
Improve this answer

1 Comment

The \ commands are supplied by the psql client. That's not a SQL statement you can put into other clients. Question was about Navicat etc, which do not support this syntax.
10

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

Improve this answer

Comments

10

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
Improve this answer

Comments

10

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

See image bellow

Improve this answer

Comments

8

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
Improve this answer

Comments

6 
~$ psql --version
psql (PostgreSQL) 16.2 (Ubuntu 16.2-1.pgdg22.04+1)

I ran this command on ubuntu CLI and found this version

Improve this answer

Comments

5

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.

Improve this answer

2 Comments

As a similar idea, I ran $ yum list to see that certain Postgresql packages were installed.
this works well. Could you please state how to upgrade from 9.6 to 10. on ubuntu 16.04
5

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: PGAdmin 4.16 showing DB version 10.10

Improve this answer

Comments

4

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.

Improve this answer

Comments

3

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.

Improve this answer

1 Comment

psql --version returns the version of the psql client, not the version of the Postgres server
2

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

Select version::char(100)

or 

Select version::varchar(100)
Improve this answer

1 Comment

select version()::varchar(100); worked for me, but was the same as version()
0

i go to the postgres-directory and type in cmd => psql --version

Improve this answer

Comments

-1 

**Using any of below commands we can get PostgreSQL Server version:
<br>
Below commands needs to be run from psql tool:<br>**
show server_version;<br>
select version();<br>
<br>
**Below commands needs to run from Linux shell:<br>**

cat PG_VERSION -- This is there in data directory(show data_directory;)<br>
pg_config --version;<br>

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.