Restart Heroku Postgres Dev DB

Asked 14/8, 2012 at 9:6 Answered 25/2, 2018 at 5:16

Solved postgresql heroku playframework-2.0

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

Alloy answered 14/8, 2012 at 9:6 Comment(1)

possible duplicate of Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections" – Paragraphia 14/8, 2012 at 12:1

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.

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.

Paragraphia answered 14/8, 2012 at 11:55 Comment(2)

Possibly relevant link: wiki.postgresql.org/wiki/Number_Of_Database_Connections – Gally 15/8, 2012 at 12:9

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 = '<your_username>';

– Whitebook 12/3, 2013 at 19:40

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.

Function answered 22/5, 2013 at 16:58 Comment(0)

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

Invigorate answered 25/2, 2018 at 5:16 Comment(1)

This solved my issue. No SQL queries other than SELECT were working b4 this. – Gusta 21/11, 2019 at 0:39

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

Recommended topics

Hot tags