Heroku Postgres: Too many connections. How do I kill these connections?

Solution 1:

Maybe have a look at what heroku pg:kill can do for you? https://devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall

Solution 2:

heroku pg:killall will kill all open connections, but that may be a blunt instrument for your needs. Interestingly, you can actually kill specific connections using heroku's dataclips.

To get a detailed list of connections, you can query via dataclips:

SELECT * FROM pg_stat_activity;

In some cases, you may want to kill all connections associated with an IP address (your laptop or in my case, a server that was now destroyed).

You can see how many connections belong to each client IP using:

SELECT client_addr, count(*) 
FROM pg_stat_activity 
WHERE client_addr is not null 
  AND client_addr <> (select client_addr from pg_stat_activity where pid=pg_backend_Tid()) 
GROUP BY client_addr; 

which will list the number of connections per IP excluding the IP that dataclips itself uses.

To actually kill the connections, you pass their "pid" to pg_terminate_backend(). In the simple case:

SELECT pg_terminate_backend(1234)

where 1234 is the offending PID you found in pg_stat_activity.

In my case, I wanted to kill all connections associated with a (now dead) server, so I used:

SELECT pg_terminate_backend(pid), host(client_addr) 
FROM pg_stat_activity 
WHERE host(client_addr) = 'IP HERE'