Find the host name and port using PSQL commands

SELECT *
FROM pg_settings
WHERE name = 'port';

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

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

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.

select inet_server_addr( ), inet_server_port( );

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

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.

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.

From the terminal you can do:

\conninfo

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

\?

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".

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

select setting from pg_settings where name='port';

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

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';;

service postgresql status

returns: 10/main (port 5432): online

I'm running Ubuntu 18.04

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.

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();

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';

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

• host name
• port ID
• Username.

For Windows, inside below file, search for port

C:\Program Files\PostgreSQL\[version]\data\postgresql.conf

or if you are already have access to DB, try below command in POSTGRES:

SHOW config_file;

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.