Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Rake task to terminate idle postgresql connections that prevent a database to be dropped
# http://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection
namespace :db do
desc "Fix 'database is being accessed by other users'"
task :terminate => :environment do
ActiveRecord::Base.connection.execute <<-SQL
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = '#{ActiveRecord::Base.connection.current_database}';
SQL
end
end
Rake::Task["db:drop"].enhance ["db:terminate"]
@mfilej

This comment has been minimized.

Copy link
Owner Author

mfilej commented Jul 7, 2013

Fix for an issue where postgresql doesn't allow a database to be dropped (e.g. “database is being accessed by other users” or “there are N other session(s) using the database”).

Solution from: http://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection

Drop into db/tasks. It will be run automatically before db:drop. Run manually with rake db:terminate.

@plindelauf

This comment has been minimized.

Copy link

plindelauf commented Jul 15, 2013

Looks promising, since I'm looking for a solution for a similar problem. Unfortunately, my problem occurs in the test environment, and when I try to run the rake db:terminate, it runs in the development environment. Is there an easy way to make this task work in a test enviroment as well?

@haslo

This comment has been minimized.

Copy link

haslo commented Aug 6, 2013

@plindelauf I'm sure you've fixed your problem meanwhile, but otherwise, try rake db:terminate RAILS_ENV=test.

@l0c0luke

This comment has been minimized.

Copy link

l0c0luke commented Mar 21, 2015

a star is not really enough for this. thank you ever so much.

@ayb

This comment has been minimized.

Copy link

ayb commented Jul 18, 2016

+1 just found this and came in very handy

Thanks!!

@FsDevNinja

This comment has been minimized.

Copy link

FsDevNinja commented Jun 6, 2018

Awesome. Worked like a charm!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.