On Jul 10, 2009, at 3:34 PM, Scott Marlowe wrote:
Assuming that tracking down the process that's connected might help, you can use pg_stat_activity to find the port that the client is connecting from, then on the client machine, use lsof to hunt down the process that is connecting via that port. For instance, I connect from my laptop with two connections. One I do a begin; in and in the other I look it up like so: select * from pg_stat_activity where current_query ilike '%idle%trans%' and current_query not ilike 'select%'; datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port-------+----------+---------+----------+---------- +-----------------------+---------+------------------------------- +-------------------------------+------------------------------- +--------------+-------------11511 | postgres | 24893 | 16413 | smarlowe | <IDLE> in transaction | f | 2009-07-10 16:20:15.056385-06 | 2009-07-10 16:20:15.056385-06 | 2009-07-10 15:27:48.944738-06 | 192.168.0.74 | 48727 The client port is 48727. Now, on my laptop I can do: sudo lsof |grep 48727 and I have this line in there: psql 27964 smarlowe 3u IPv4 1114765 TCP steamboat:48727->192.168.0.247:postgresql (ESTABLISHED)
Just a little tidbit for that: you can have lsof tell you what's got that port open directly, no need for grep:
lsof -i tcp:48727 that way you keep the column headers in the output. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
