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

Question

I have an app running on Heroku. This app has an Postgres 9.2.4 (Dev) addon installed. To access my online database I use Navicat Postgres. Sometimes Navicat doesn't cleanly close connections it sets up with the Postgres database. The result is that after a while there are 20+ open connections to the Postgres database. My Postgres installs only allows 20 simultanious connections. So with the 20+ open connections my Postgress database is now unreachable (too many connections).

I know this is a problem of Navicat and I'm trying to solve this on that end. But if it happens (that there are too many connections), how can I solve this (e.g. close all connections).

I've tried all of the following things, without result.

• Closed Navicat & restarted my computer (OS X 10.9)
• Restarted my Heroku application (heroku restart)
• Tried to restart the online database, but I found out there is no option to do this
• Manually closed all connections from OS X to the IP of the Postgres server
• Restarted our router

I think it's obvious there are some 'dead' connections at the Postgres side. But how do I close them?

Answer 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

Answer 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'