Re: is there anyway to get the backends IP address from the PID?

[Michael Fuhr <mike@xxxxxxxx>]

On Fri, Jan 14, 2005 at 02:29:47PM -0600, Tony Caduto wrote:

> Does anyone know if there is a way to get the backends IP address from 
> the PID?

Do you mean the IP address of the backend (the server) or the address
of the client that's using that backend?

PostgreSQL 8.0 will have inet_client_addr() and inet_server_addr()
functions to get the client and backend IP addresses for the current
session, but I'm not aware of a way to get another session's IP
addresses via an SQL query.

> I am using the view pg_stat_activity and it would be nice if it would 
> also display the IP address along with the PID.

That probably wouldn't be hard to add -- consider submitting a patch
or suggesting it to the developers.

> I can see the IP address when I do a ps -ef but it would be nice to be 
> able to get it via a sql command.

A workaround would be to write a function that runs ps, netstat,
lsof, etc., and extracts the IP address from the command's output.
Here's a set-returning plperlu example that works with PostgreSQL
8.0.0rc5 on FreeBSD 4.11:

CREATE TYPE pid_ip AS (
    pid     integer,
    ipaddr  inet
);

CREATE FUNCTION backend_client_ips() RETURNS SETOF pid_ip AS $$
my $psprog = "/bin/ps";
my $rows;

if (open(my $ps, "-|", $psprog, $pid)) {
    while (<$ps>) {
	if (/^\s*(\d+).*postmaster: \S+ \S+ (\d+\.\d+\.\d+\.\d+)/) {
	    push @$rows, {pid => $1, ipaddr => $2};
	}
    }
    close $ps;
} else {
    elog ERROR, "$psprog: $!";
}

return $rows;
$$ LANGUAGE plperlu VOLATILE;


SELECT * FROM backend_client_ips();
  pid  |  ipaddr   
-------+-----------
 78563 | 10.1.0.1
 78566 | 127.0.0.1
 78573 | 10.1.0.2
(3 rows)

SELECT i.ipaddr, a.*
FROM pg_stat_activity AS a
LEFT OUTER JOIN backend_client_ips() AS i ON i.pid = a.procpid;
  ipaddr   | datid | datname | procpid | usesysid | usename | current_query |          query_start          
-----------+-------+---------+---------+----------+---------+---------------+-------------------------------
           | 26492 | test    |   78575 |      100 | mfuhr   | <IDLE>        | 2005-01-15 08:23:51.816278-07
 10.1.0.2  | 26492 | test    |   78573 |      100 | mfuhr   | <IDLE>        | 2005-01-15 08:23:34.224116-07
 10.1.0.1  | 26492 | test    |   78563 |      100 | mfuhr   | <IDLE>        | 2005-01-15 08:23:39.294674-07
 127.0.0.1 | 26492 | test    |   78566 |      100 | mfuhr   | <IDLE>        | 2005-01-15 08:23:14.276227-07
(4 rows)

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx