Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to trace the source of postgres queries identified by a pid. Normally you can do this by finding the client_addr and client_port fields in the pg_stat_activity table.

However, some entries have client_port -1 or NULL. What does this mean and how can one go about finding the source of these queries?

I think NULL ports indicated that this is an internal postgres operation, and I suspected that -1 meant local connections - but I couldn't work out how local connections communicated with the postgres daemon.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

I've found a magic trick that appears to work. At least on one particular ubuntu linux machine.

Run lsof on the postgres backend and find a unix socket something like this:

postgres   6571 postgres    8u     unix 0xffff81061ab3a000              205980094 /var/run/postgresql/.s.PGSQL.5432

The value 205980094 is the inode. Though the client unix socket connecting to this socket is anonymous, and you can't convince lsof on linux to tell you the end point of unix sockets it is created at amount the same time as the backends socket. So appears to have an inode number which is usually adjacent to this number.

Running lsof on the whole system, restriction to unix sockets (using grep), and then sorting by this key (sort -k 7 -n) should hopefully tell the process responsible for the query.

share|improve this answer

Those are connections over local unix connections, yes. They'll be through a socket that's in /tmp on a source install or a place like /var/run/postgresql if you're using package based install.

It should be possible to find the client process using for example lsof.

share|improve this answer
    
It doesn't look like lsof has the ability to trace psql sockets for me: –  user53597 Sep 18 '10 at 17:17
    
lsof doesn't seem to be able to tell me which process is connecting to a unix socket. (Running lsof of the postgres backend shows a named socket - but there doesn't seem to be anything interesting. Connected to his socket. –  user53597 Sep 18 '10 at 17:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.