Reputation: 7600

Find the host name and port using PSQL commands

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?

Upvotes: 261

Answers (20)

JQTs

Reputation: 310

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

host name
port ID
Username.

Upvotes: 0

Khaino

Reputation: 4149

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

Upvotes: 284

Arty

Reputation: 590

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

Upvotes: 0

t7e

Reputation: 509

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

Upvotes: 1

D-Shih

Reputation: 46219

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

Upvotes: 0

JJ Ward

Reputation: 133

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.

Upvotes: 0

A-Sharabiani

Reputation: 19329

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

select setting from pg_settings where name='port';

Upvotes: 9

mrwonderfulness

Reputation: 411

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.

Upvotes: 22

Vinayak Mishra

Reputation: 351

From the terminal you can do:

\conninfo

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

Upvotes: 16

Intra

Reputation: 177

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

Upvotes: 12

Marcos Galigarcia

Reputation: 237

service postgresql status

returns: 10/main (port 5432): online

I'm running Ubuntu 18.04

Upvotes: 0

professorcolm

Reputation: 145

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

Upvotes: 6

Rahal Danthanarayana

Reputation: 748

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.

Upvotes: -1

user330315

Reputation:

SELECT *
FROM pg_settings
WHERE name = 'port';

Upvotes: 341

gerardw

Reputation: 6330

select inet_server_addr( ), inet_server_port( );

Upvotes: 27

Brad Koch

Reputation: 20267

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.

Upvotes: 82