How to get Client Machine Name/ IP Address connecting to PostgreSQL Server

Question

We are using PostgreSQL Server. We need a query to get all client machine names, IPAdresses connecting to that database.

Found below query. But it is returning only max connections, available connections. But we need client machine names.

select max_conn, used, res_for_super, max_conn - used - res_for_super res_for_normal 
from (
  select count(*) used from pg_stat_activity
) t1,
(select setting::int res_for_super 
 from pg_settings 
 where name=$$superuser_reserved_connections$$
) t2,
(select setting::int max_conn 
 from pg_settings 
 where name=$$max_connections$$) t3;

SELECT inet_client_addr() is not giving correct IP addresses.

Need help in this.

Solution

https://www.postgresql.org/docs/current/static/functions-info.html

inet_client_addr address of the remote connection

which is YOUR session IP.

https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

pg_stat_activity.client_addr and pg_stat_activity.client_hostname should help you

client_addr

IP address of the client connected to this backend. If this field is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum.

client_hostname

Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null for IP connections, and only when log_hostname is enabled.

also client IP can be behind the HAproxy or pgbouncer or smth like...

Comments

[Srilakshmi Chandolu]

Hi, Thanks for the reply. But client_addr is coming only for UNIX machines. But I need it for windows machines. How to enable log_hostname. Could you please share more details.

[Ismail Yavuz]

Which IP has how many clients: select count(client_addr) as c,client_addr from pg_stat_activity group by client_addr order by c desc