7

I got this error from an Play 2.0.3 java application. How could I restart Heroku Postgres Dev DB? I could not find any instructions to restart the DB on Heroku help center. 

app[web.1]: Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
i.am.michiel
  • 10,281
  • 7
  • 50
  • 86
angelokh
  • 9,426
  • 9
  • 69
  • 139
  • possible duplicate of [Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections"](http://stackoverflow.com/questions/11847144/heroku-psql-fatal-remaining-connection-slots-are-reserved-for-non-replication) – Craig Ringer Aug 14 '12 at 12:01

3 Answers3

15

The error mesage you have there isn't a reason to restart the database; it isn't a database problem. Your application is holding too many connections, probably because you forgot to set up its connection pool. That isn't a DB server problem and you can fix it without restarting the DB server.

If you stop your Play application or reconfigure its connection pool the problem will go away.

Another option is to put your Heroku instance in maintenance mode then take it out again.

Since heroku doesn't allow you to connect as a superuser (for good reasons) you can't use that reserved superuser slot to connect and manage connections like you would with normal PostgreSQL.

See also:

Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections"

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

If you're a non-heroku user who found this:

With normal PostgreSQL you can disconnect your client from the server end end using a PostgreSQL connection to your server. See how it says there's a slot reserved for "superuser connections" ? Connect to Pg as a superuser (postgres user by default) using PgAdmin-III or psql.

Once you're connected you can see other clients with:

SELECT * FROM pg_stat_activity;

If you want to terminate every connection except your own you can run:

SELECT procpid, pg_terminate_backend(procpid) 
FROM pg_stat_activity WHERE procpid <> pg_backend_pid();

Add AND datname = current_database and/or AND usename = <target-user-name> as appropriate.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Possibly relevant link: http://wiki.postgresql.org/wiki/Number_Of_Database_Connections – kgrittn Aug 15 '12 at 12:09
  • 1
    I was actually able to use the SQL statement above to terminate connections on a Heroku PostgreSQL instance. I'm not sure about the superuser permissions, but this was on a shared database. Using the `heroku pg:psql` command I was able to run the first SQL statement and see all connections, but because it was a shared DB that query had certain columns blocked for all but our DB connections. Then I was able to run the second SQL statement slightly modified: `SELECT procpid, pg_terminate_backend(procpid) FROM pg_stat_activity WHERE procpid <> pg_backend_pid() AND usename = '';` – Liron Yahdav Mar 12 '13 at 19:40
7

I think I should have just added this in reply to the previous answer, but I couldn't figure out how to do that, so...

As an update to Liron Yahdav's comment in the accepted answer's thread: the "non-heroku users who found this" solution worked for me on a Heroku PostgreSQL dev database, but with a slight modification to the query Liron provided. Here is my modified query: SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND usename='<your_username>';

It seems that procpid has changed to pid.

ctlacko
  • 712
  • 6
  • 13
6

There is no way to restart the whole database. Though, heroku offers a simple way to stop all connections which solves the problem in the majority of cases:

heroku pg:killall

ruhanbidart
  • 4,564
  • 1
  • 26
  • 13