Diagnosing application connection leaks in PostgreSQL

Question

I am investigating a connection leak in PostgreSQL and would like to gather some diagnostics.

In particular, I would like to associate some information with each connection I make and then be able to query it directly.

If I was designing this kind of thing in MS-SQL I would change my connection factory to execute an upsert against a diagnostics table after each connection is made, containing a @@spid, backtrace and other diag information.

Then to diagnose what is happening I could query sysprocesses joining to my diagnostics table on spid. This would give me clean application backtraces associated with each connection.

How can I achieve something similar on PostgreSQL?

Answer 1

PostgreSQL forks a new process to handle each connection. You can get the PID of this process easily:

SELECT pg_backend_pid();

This corresponds to the process ID visible in standard administration tools (top, ps, etc.). You can terminate connections using standard tools too (kill) or, with the appropriate permissions, by saying SELECT pg_terminate_backend(pid).

A list of current sessions is also accessible in the database:

SELECT * FROM pg_stat_activity;

One last note: PIDs are guaranteed to be unique as of the time the query is run, but will be re-used by the operating system eventually. You can ensure uniqueness by pairing the PID with the backend_start column of pg_stat_activity. For that matter, you might as well lump that in with your logging:

INSERT INTO log_table (pid, backend_start, message, backtrace)
SELECT procpid, backend_start, 'my message', 'my backtrace'
FROM pg_stat_activity
WHERE procpid=pg_backend_pid();