Identifying processes responsible for queries with client_port==-1 in pg_stat_activity

Question

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.

Answer 1

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.

Answer 2

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.