Find the host name and port using PSQL commands

Question

I have PSQL running, and am trying to get a perl application connecting to the database. Is there a command to find the current port and host that the database is running on?

score 322 · Answer 1 · edited Aug 12 '16 at 14:38

322

SELECT *
FROM pg_settings
WHERE name = 'port';

edited Aug 12 '16 at 14:38

Deepak Mahakale

22,834
10
68
88

answered Apr 08 '11 at 17:21

3

This doesn't work if you have multiple servers in the same cluster, unfortunately... – Vitor Baptista Mar 01 '13 at 22:57
15

This actually gives you the port number not the server host. – hd1 May 08 '13 at 18:12
3

@hd1: And? The question was (also) how to get the port number. And that is what the query returns. – May 08 '13 at 22:26
7

yes, but if you can't connect to the database, you can't execute that :P – jaydel May 05 '20 at 16:08

Khaino · Answer 2 · 2022-11-03T14:11:45.180

252

This command will give you postgres port number

 \conninfo

If Postgres is running on a Linux server, you can also use the following command

sudo netstat -plunt |grep postgres

OR (if it comes as postmaster)

sudo netstat -plunt |grep postmaster

and you will see something similar as this

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      140/postgres
tcp6       0      0 ::1:5432                :::*                    LISTEN      140/postgres

In this case, the port number is 5432 which is also the default port number

credit: link

edited Nov 03 '22 at 14:11

answered Jun 24 '16 at 10:36

Khaino

3,774
1
27
36

1

it is coming as postmaster for 5432. – codebased Feb 18 '17 at 00:27
14

this is the right answer. the chosen answer is true but not related to the question. – Luis Martins Apr 20 '18 at 05:52

score 75 · Accepted Answer · edited Apr 06 '15 at 21:11

75

The default PostgreSQL port is 5432. The host that the database is operating on should have been provided by your hosting provider; I'd guess it would be the same host as the web server if one wasn't specified. Typically this would be configured as localhost, assuming your web server and database server are on the same host.

edited Apr 06 '15 at 21:11

Eric Leschinski

146,994
96
417
335

answered Apr 08 '11 at 17:18

Brad Koch

19,267
19
110
137

1

I encountered this problem too, and trying 5433 port, and this is working – Jonny Manowar Aug 07 '18 at 12:10
85

This doesn't actually answer the question. – swiss_knight Apr 17 '20 at 05:53
2

@s.k Read the context. The OP didn't know the host and port for their postgres database. They can connect with a basic `psql` command, so that implies `localhost:5432`. Unless someone got clever with env vars/aliases/etc. I don't love my answer a decade later, but [solving the x](http://xyproblem.info/) instead of y is perfectly fine and in this case has helped hundreds of people. – Brad Koch Apr 17 '20 at 12:43
1

I agree with @s.k I'm on a remote server where the database is hosted so of course can connect locally there via localhost, but I wish for an external application on a different server to access the database where of course the database doesn't exist locally, and hence knowing the answer to the original question would be useful for me – user4779 Mar 11 '21 at 05:09
I have the solution. But thinking how can I push this in the context. – ash Mar 14 '22 at 14:11

score 60 · Answer 4 · answered Sep 17 '12 at 07:05

60

select inet_server_addr(); gives you the ip address of the server.

answered Sep 17 '12 at 07:05

Kaarel

3,958
1
18
16

4

regrettably this does not give the "live" hostname if you're connecting via ssh proxy - I keep getting "127.0.0.1" – Andrew Wolfe Jan 05 '15 at 18:28
18

I did not get anything: ```$postgres=# select inet_server_addr(); inet_server_addr ------------------ (1 row)``` – trex Apr 14 '17 at 05:37
1

Did not give the host name – Kumar Vaibhav Jul 09 '18 at 16:13

score 26 · Answer 5 · answered Jun 02 '16 at 19:34

26

select inet_server_addr( ), inet_server_port( );

answered Jun 02 '16 at 19:34

gerardw

5,822
46
39

3

How is this any different from Kaarel Kitsemets's [answer](http://stackoverflow.com/a/12454501/369450) and Andromida's [answer](http://stackoverflow.com/a/22986571/369450)? – Uyghur Lives Matter Jun 02 '16 at 19:53
1

It answers the question that was asked as a single copy and paste solution. – gerardw Jun 03 '16 at 11:41

score 25 · Answer 6 · answered Jun 05 '14 at 15:43

25

This is non-sql method. Instructions are given on the image itself. Select the server that you want to find the info about and then follow the steps.

enter image description here

answered Jun 05 '14 at 15:43

bonCodigo

14,268
1
48
91

score 22 · Answer 7 · answered Apr 10 '14 at 11:20

22

select inet_server_port(); gives you the port of the server.

answered Apr 10 '14 at 11:20

Andromida

1,095
1
11
28

3

On bluehost this query returns a blank field when in reality the port was 5432. This query does not always return the port number. – Eric Leschinski Apr 06 '15 at 21:14

score 18 · Answer 8 · answered Jan 31 '21 at 07:14

18

From the terminal you can simply do a "postgres list clusters":

pg_lsclusters

It will return Postgres version number, cluster names, ports, status, owner, and the location of your data directories and log file.

answered Jan 31 '21 at 07:14

mrwonderfulness

361
2
7

score 17 · Answer 9 · answered Apr 06 '15 at 21:11

17

The postgresql port is defined in your postgresql.conf file.

For me in Ubuntu 14.04 it is: /etc/postgresql/9.3/main/postgresql.conf

Inside there is a line:

port = 5432

Changing the number there requires restart of postgresql for it to take effect.

answered Apr 06 '15 at 21:11

Eric Leschinski

146,994
96
417
335

This is a way better answer than all of the above. Additionally, it is possible to show the current path by using `sudo -u postgres psql -c 'SHOW config_file'` and also the path is nowadays 14 of course. – TheLexoPlexx Feb 03 '23 at 15:10

Vinayak Mishra · Answer 10 · 2019-11-07T06:51:33.573

16

From the terminal you can do:

\conninfo

I would suggest reading a documentation on their exhaustive list of all commands using:

\?

edited Nov 07 '19 at 06:51

answered Nov 07 '19 at 06:39

Vinayak Mishra

341
4
11

Intra · Answer 11 · 2019-07-26T15:30:58.893

12

You can use the command in psql \conninfo you will get You are connected to database "your_database" as user "user_name" on host "host_name" at port "port_number".

edited Jul 26 '19 at 15:30

answered Jul 26 '19 at 12:35

Intra

177
1
2
7

score 7 · Answer 12 · answered Aug 26 '21 at 21:02

7

To find the port number you can run this command (assuming you are on localhost)

select setting from pg_settings where name='port';

answered Aug 26 '21 at 21:02

A-Sharabiani

17,750
17
113
128

score 4 · Answer 13 · answered Mar 28 '18 at 10:52

4

SELECT CURRENT_USER usr, :'HOST' host, inet_server_port() port;

This uses psql's built in HOST variable, documented here

And postgres System Information Functions, documented here

answered Mar 28 '18 at 10:52

professorcolm

125
5

2

If you're connecting locally, `HOST` will be the directory in which your UNIX domain socket resides, eg `/tmp`. – bishop Sep 10 '19 at 17:29

score 1 · Answer 14 · answered Apr 29 '19 at 16:55

1

service postgresql status

returns: 10/main (port 5432): online

I'm running Ubuntu 18.04

answered Apr 29 '19 at 16:55

Marcos Galigarcia

219
3
7

score 1 · Answer 15 · answered Aug 17 '22 at 15:48

1

An addition to the @a_horse_with_no_name answer. To get the hostname:

SELECT boot_val,reset_val FROM pg_settings WHERE name='listen_addresses';;

answered Aug 17 '22 at 15:48

t7e

322
1
3
9

score 0 · Answer 16 · answered Nov 08 '17 at 07:43

0

go to the "Terminal" and just type

service postgres status

In the results you can get the port details

In my case it's running on port "5432" (default).
I'm using CentOS 7.Hope this helps.

answered Nov 08 '17 at 07:43

Rahal Kanishka

720
13
27

score 0 · Answer 17 · answered Jun 29 '22 at 00:42

Because you said you (yourself) have postgresql running, I'll assume:

you're on Linux,
have at least one account with superuser privileges and/or can access the postgres role, and
(just for fun) you need to access both values within a single transaction from within the database itself

/* SQL CODE */

CREATE TEMP TABLE tmp ( hostname text, port bigint ) ON COMMIT DROP;

COPY tmp FROM PROGRAM $pgm$ printf "$HOSTNAME\t$(i=1 && until [[ "$(psql -U postgres -p $i -qt -c "SELECT 'true'" 2>/dev/null | sed -e '$d' | xargs | tr -d "\n")" == "true" ]]; do i=$(($i+1)) && if [ $i == "65535" ]; then break ; fi ; done && echo $i)"$pgm$ ( format 'text', delimiter '\t' );

SELECT host, port FROM tmp;

will give you both, executing the $pgm$-delimited code as a shell script and returning the values to the server-side COPY API's stdin. Unfortunately, this method needs a table target to invoke the server-side shell.

If you need to be able to call without a temp table, i.e. as a function invocation, try implementing the above shell in the plsh language.

score 0 · Answer 18 · answered Aug 05 '22 at 03:52

I think PostgreSQL didn't provide an in-built function to get the hostname of server so we might need to write an extension to get the information from server.

I found there is a PostgreSQL extension pg-hostname which can get the hostname from server.

When we have installed the extension we can enable that and query information by inet_server_port & hostname function.

CREATE EXTENSION hostname;

SELECT hostname(),inet_server_port();

score 0 · Answer 19 · answered Oct 31 '22 at 17:33

0

I use such a solution. No plugins required. Temporary tables are not needed. Only for unix.

select pg_read_file('/etc/hostname') as hostname, setting as port from pg_settings where name='port';

answered Oct 31 '22 at 17:33

Arty

579
1
8
17

score 0 · Answer 20 · answered Nov 24 '22 at 01:45

0

Right click your SQL server. Mine is PostgresSQL 13 and select properties -> connection. This has:

host name
port ID
Username.

answered Nov 24 '22 at 01:45

JQTs

142
2
11

Find the host name and port using PSQL commands

20 Answers20

Linked