See PostgreSQL connections

Posted 2025-02-05

I spent the last couple of days tracking down a bug which came down to a database connection stalling out, dying, and not getting re-established.

In the process, I learned that the PostgreSQL table pg_stat_activity holds information on all its processes, including client connections. To see all connected clients:

SELECT pid,
backend_start AS connected_since,
usename AS user_name,
datname AS database_name,
client_addr AS client_address,
client_port,
state,
query as current_or_last_query
FROM pg_stat_activity
-- this backend = client connection
WHERE backend_type = 'client backend';

This table includes info on various types of processes, but 'client backend' ones are connections. See the docs for details.

Using a pid from this table:

• SELECT pg_terminate_backend(12345); will terminate the connection (causing the server OS to send a "TCP Reset" (RST) packet; the client usually sees an "unexpected disconnection" or a "connection reset by peer" error)
• SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'your_database' AND pid <> pg_backend_pid(); terminates all connections but yours
• SELECT pg_cancel_backend(12345); will leave the connection open but cancel its currently running query
• SELECT pg_wait_for_backend_termination(12345, 10); waits up to 10 seconds for the pid to terminate and returns true if it does and false if it doesn't

Ultimately I had to simulate a stalled connection (not an explicitly terminated one) to reproduce the bug; running toxiproxy in a Docker container, pointing my database connections there, and setting a timeout 0 toxic after the connection was established was the key to that.