Postgres Pro
Downloads
Home   >   mailing lists

Thread: identifying local connections

identifying local connections

From
Scott Ribe
Date:
Assume I have a local process which leaves a transaction open & idle for an extended period of time. Is there any way
toidentify the local process connected to a particular backend? 

pg_stat_activity is fine for TCP connections but does not provide useful identifying information for domain socket
connections.

I just upgraded to 9, and will implement set application_name in my various server daemons, but was wondering if
there'sa way to identify this process right now. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: identifying local connections

From
Tom Lane
Date:
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> Assume I have a local process which leaves a transaction open & idle for an extended period of time. Is there any way
toidentify the local process connected to a particular backend? 

netstat will probably work for this, depending on what platform you're on.

            regards, tom lane

Re: identifying local connections

From
Scott Ribe
Date:
On Nov 15, 2010, at 8:50 AM, Tom Lane wrote:

> netstat will probably work for this, depending on what platform you're on.

OS X. I can see the connections, but I don't see an option to display PIDs.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: identifying local connections

From
Steve Clark
Date:
On 11/15/2010 11:00 AM, Scott Ribe wrote: 
On Nov 15, 2010, at 8:50 AM, Tom Lane wrote:
 
netstat will probably work for this, depending on what platform you're on.   
OS X. I can see the connections, but I don't see an option to display PIDs.
netstat -an will do it on linux.

sockstat will do it on FreeBSD.

What's OS X ? ;-)

--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com

Re: identifying local connections

From
Tom Lane
Date:
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> On Nov 15, 2010, at 8:50 AM, Tom Lane wrote:
>> netstat will probably work for this, depending on what platform you're on.

> OS X. I can see the connections, but I don't see an option to display PIDs.

In that case see lsof --- you can match up the ends of the connection
using the hex value in the "device" column.

            regards, tom lane

Re: identifying local connections

From
Scott Ribe
Date:
On Nov 15, 2010, at 9:24 AM, Tom Lane wrote:

> In that case see lsof --- you can match up the ends of the connection
> using the hex value in the "device" column.
>
>             regards, tom lane
>

Yes, that works. Match "Address" from netstat to "DEVICE" in lsof.

Thanks.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: identifying local connections

From
Scott Ribe
Date:
On Nov 15, 2010, at 9:05 AM, Steve Clark wrote:

> netstat -an will do it on linux.
>
> sockstat will do it on FreeBSD.
>
> What's OS X ? ;-)

Apple must use very different option switches for their netstat, because I see no way to get PIDs (which seems like a
prettybig oversight to me), and -an would not make sense: 

-a      Include directory entries whose names begin with a dot (.).
-n      Display user and group IDs numerically, rather than converting to
             a user or group name in a long (-l) output.  This option turns on
             the -l option.

;-)

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice