[Postgres] Kill connections
Using Postgres for a Rails app I have to deploy on Heroku, I got this error:
Errors running test:units! #<ActiveRecord::StatementInvalid: PG::Error: ERROR: database "christmas_project" is being accessed by other users
DETAIL: There are 2 other sessions using the database.
: DROP DATABASE IF EXISTS "christmas_project">So the solution is to kill the connections and I found this, which works only for older versions:
SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )
AND datname = current_database( );For Postgres version 9.2.1, use :
SELECT pg_terminate_backend( pid )
FROM pg_stat_activity
WHERE pid <> pg_backend_pid( )
AND datname = current_database( );Written by François Benaiteau
Related protips
5 Responses
This is also useful for rake/thor tasks that are changing the database e.g. loading a dump
@tadas_t the thing I don't get why it keeps on happening though while running tests. I have tried this monkey patch https://gist.github.com/4073741 but does not seem to work. any idea?
How do you run the tests? They shouldn't be modifying the db scheme, just the records. If they are, for any reason, maybe several processes are using the same database i.e. dev and test environments use the same db, cucumber and rspec use the same db etc.
@tadas_t with test::unit, it crashes sometime when running rake, apparently it drops the table for some reason
I think this is incredibly crude. Don't do that on a production environment because you're just pulling the rug from under another process this way.
Question to OP: why didn't you find out what processes where still connected to the database in the first place because usually if you expect that there are no connections at that time there exists a bug.
