How can I kill all my postgresql connections?
I'm trying a rake db:drop but I get:
ERROR: database "database_name" is being accessed by other users
DETAIL: There are 1 other session(s) using the database.
I've tried shutting down the processes I see from a ps -ef | grep postgres but this doesn't work either:
kill: kill 2358 failed: operation not permitted
You can use pg_terminate_backend() to kill a connection. You have to be superuser to use this function. This works on all operating systems the same.
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 = 'database_name'
;
Before executing this query, you have to REVOKE the CONNECT privileges to avoid new connections:
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
If you're using Postgres 8.4-9.1 use procpid instead of pid
SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
procpid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'database_name'
;
OSX, Postgres 9.2 (installed with homebrew)
$ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ pg_ctl restart -D /usr/local/var/postgres
$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
If your datadir is elsewhere you can find out where it is by examining the output of ps aux | grep postgres
This seems to be working for PostgreSQL 9.1:
#{Rails.root}/lib/tasks/databases.rake
# monkey patch ActiveRecord to avoid There are n other session(s) using the database.
def drop_database(config)
case config['adapter']
when /mysql/
ActiveRecord::Base.establish_connection(config)
ActiveRecord::Base.connection.drop_database config['database']
when /sqlite/
require 'pathname'
path = Pathname.new(config['database'])
file = path.absolute? ? path.to_s : File.join(Rails.root, path)
FileUtils.rm(file)
when /postgresql/
ActiveRecord::Base.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))
ActiveRecord::Base.connection.select_all("select * from pg_stat_activity order by procpid;").each do |x|
if config['database'] == x['datname'] && x['current_query'] =~ /<IDLE>/
ActiveRecord::Base.connection.execute("select pg_terminate_backend(#{x['procpid']})")
end
end
ActiveRecord::Base.connection.drop_database config['database']
end
end
Lifted from gists found here and here.
Here's a modified version that works for both PostgreSQL 9.1 and 9.2.
I use the following rake task to override the Rails drop_database method.
lib/database.rake
require 'active_record/connection_adapters/postgresql_adapter'
module ActiveRecord
module ConnectionAdapters
class PostgreSQLAdapter < AbstractAdapter
def drop_database(name)
raise "Nah, I won't drop the production database" if Rails.env.production?
execute <<-SQL
UPDATE pg_catalog.pg_database
SET datallowconn=false WHERE datname='#{name}'
SQL
execute <<-SQL
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '#{name}';
SQL
execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
end
end
end
end
Edit: This is for Postgresql 9.2+
I had this issue and the problem was that Navicat was connected to my local Postgres db. Once I disconnected Navicat the problem disappeared.
EDIT:
Also, as an absolute last resort you can back up your data then run this command:
sudo kill -15 `ps -u postgres -o pid`
... which will kill everything that the postgres user is accessing. Avoid doing this on a production machine but you shouldn't have a problem with a development environment. It is vital that you ensure every postgres process has really terminated before attempting to restart PostgreSQL after this.
EDIT 2:
Due to this unix.SE post I've changed from kill -9 to kill -15.
There is no need to drop it. Just delete and recreate the public schema. In most cases this have exactly the same effect.
namespace :db do
desc 'Clear the database'
task :clear_db => :environment do |t,args|
ActiveRecord::Base.establish_connection
ActiveRecord::Base.connection.tables.each do |table|
next if table == 'schema_migrations'
ActiveRecord::Base.connection.execute("TRUNCATE #{table}")
end
end
desc 'Delete all tables (but not the database)'
task :drop_schema => :environment do |t,args|
ActiveRecord::Base.establish_connection
ActiveRecord::Base.connection.execute("DROP SCHEMA public CASCADE")
ActiveRecord::Base.connection.execute("CREATE SCHEMA public")
ActiveRecord::Base.connection.execute("GRANT ALL ON SCHEMA public TO postgres")
ActiveRecord::Base.connection.execute("GRANT ALL ON SCHEMA public TO public")
ActiveRecord::Base.connection.execute("COMMENT ON SCHEMA public IS 'standard public schema'")
end
desc 'Recreate the database and seed'
task :redo_db => :environment do |t,args|
# Executes the dependencies, but only once
Rake::Task["db:drop_schema"].invoke
Rake::Task["db:migrate"].invoke
Rake::Task["db:migrate:status"].invoke
Rake::Task["db:structure:dump"].invoke
Rake::Task["db:seed"].invoke
end
end
Remote scenario. But if you're trying to run tests in a rails app, and you get something like
"ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR: database "myapp_test" is being accessed by other users DETAIL: There is 1 other session using the database."
Make sure you close pgAdmin or any other postgres GUI tools before running tests.
Case :
Fail to execute the query :
DROP TABLE dbo.t_tabelname
Solution :
a. Display query Status Activity as follow :
SELECT * FROM pg_stat_activity ;
b. Find row where 'Query' column has contains :
'DROP TABLE dbo.t_tabelname'
c. In the same row, get value of 'PID' Column
example : 16409
d. Execute these scripts :
SELECT
pg_terminate_backend(25263)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
25263 <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'database_name'
;
