Restart Heroku Postgres Dev DB

Question

I got this error from the Play 2.0.3 java application. How can I restart Heroku Postgres Dev DB? I could not find any instructions for reloading the database in the Heroku help center.

app[web.1]: Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections 

Answer 1

You have an error reloading the database; this is not a database problem. There are too many connections in your application, possibly because you forgot to configure the connection pool. This is not a database server problem, and you can fix it without restarting the database server.

If you stop the Play application or reconfigure its connection pool, the problem will disappear.

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

Since heroku does not allow you to connect as a superuser (for good reasons), you cannot use this reserved superuser slot to connect and manage connections, as with regular PostgreSQL.

See also:

Heroku "psql: FATAL: the remaining connection slots are reserved for superuser connections without replication

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

If you are not the hero who found this:

With regular PostgreSQL, you can disconnect your client from the back of the server using the PostgreSQL connection to your server. See how it says about the slot reserved for "superuser connections"? Connect to Pg as superuser (default postgres user) using PgAdmin-III or psql .

After connecting, you can see other clients using

 SELECT * FROM pg_stat_activity; 

If you want to complete each 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> , if necessary.

Answer 2

I think I should have simply added this in response to the previous answer, but I could not figure out how to do this, therefore ...

As an update to Liron Yahdav's comment in the accepted stream of answers: the “non-heroic users who found this” solution worked for me in the Heroku PostgreSQL dev database, but with a slight modification to the Liron query. 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.

Answer 3

There is no way to restart the entire database. However, heroku offers an easy way to break all connections, which solves the problem in most cases:

heroku pg:killall

Answer 4

Well, actually there is a command to run when you want to reset the postgre database on heroku:

heroku pg:reset HEROKU_POSTGRESQL_MAGENTA -a app_name

Make sure you replace HEROKU_POSTGRESQL_MAGENTA with your database name, it could be, for example, HEROKU_POSTGRESQL_BLACK or something else that you can check in the heroku application control panel.