57

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?

Jasper Schulte
  • 7,181
  • 7
  • 23
  • 19

6 Answers6

45

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

John Beynon
  • 37,398
  • 8
  • 88
  • 97
  • Good call! That looks more useful than what I posted. – bma Nov 06 '13 at 15:46
  • 7
    This was the solution. Thank you. There is a bit of irony here. When I gave the command `heroku pg` it told met there were 21 connections open (20 being the max) so when I gave the command `heroku pg:killall DATABASE_NAME` it gave me the error that I could not connect to give the command because all the connections were taken. So I had te wait for one of the connection to timeout. :( – Jasper Schulte Nov 08 '13 at 00:36
  • 1
    If you run into @Zjaaspoer's issue, restart your Heroku app and it may free up a connection long enough for you to run the kill command. – Jamon Holmgren Jan 30 '14 at 21:12
24

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'
mgojohn
  • 881
  • 9
  • 15
12

1). First login into Heroku with your correct id (in case you have multiple accounts) using heroku login.
2). Then, run heroku apps to get a list of your apps and copy the name of the one which is having the PostgreSQL db installed.
3). Finally, run heroku pg:killall --app appname to get all the connections terminated.

Neeraj Sewani
  • 3,952
  • 6
  • 38
  • 55
9

From the Heroku documentation (emphasis is mine):

FATAL: too many connections for role

FATAL: too many connections for role "[role name]" This occurs on Starter Tier (dev and basic) plans, which have a max connection limit of 20 per user. To resolve this error, close some connections to your database by stopping background workers, reducing the number of dynos, or restarting your application in case it has created connection leaks over time. A discussion on handling connections in a Rails application can be found here.

Because Heroku does not provide superuser access your options are rather limited to the above.

Robert H
  • 11,520
  • 18
  • 68
  • 110
  • Sorry if I was not clear about this. The leaked connections seemed to originate from a local program (Navicat). I closed the progrem, restarted my laptop, restarted the router, manually killed all the processes with open connections. So I assume there's nothing more I can do to reduce the number of connections from my end. I restarted my app on Heroku serveral times also. – Jasper Schulte Nov 06 '13 at 15:25
  • That's it unfortunately. If it's a persistent problem upgrading to a paid plan may be worthwhile – Robert H Nov 06 '13 at 15:27
7

Restart server 

heroku restart --app <app_name>

It will close all connection and restart.

Ebin Joy
  • 2,690
  • 5
  • 26
  • 39
2

As the superuser (eg. "postgres"), you can kill every session but your current one with a query like this:

select pg_cancel_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid();

If they do not go away, you might have to use a stronger "kill", but certainly test with pg_cancel_backend() first.

select pg_terminate_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid();
bma
  • 9,424
  • 2
  • 33
  • 22
  • According to the Heroku documentation [link](https://devcenter.heroku.com/articles/heroku-postgresql) you can't do anything as a superuser: `Heroku Postgres users are granted all non-superuser permissions on their database.` – Jasper Schulte Nov 06 '13 at 15:22
  • You might be able to issue the same commands as the db owner, you could try that to see if it works for you. Otherwise, I can't think of anything else other than bouncing the db which you may or may not be able to do in Heroku. – bma Nov 06 '13 at 15:40