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

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/