如何杀死我所有的postgresql连接?
我试着耙db:下降，但我得到:
ERROR: database "database_name" is being accessed by other users
DETAIL: There are 1 other session(s) using the database.
我尝试过关闭我从ps -ef | grep postgres中看到的进程，但这也不起作用:
kill: kill 2358 failed: operation not permitted
当前回答
关于运行进程的所有信息:
SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND datname = 'my_database_name';
其他回答
关于运行进程的所有信息:
SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND datname = 'my_database_name';
只是想指出，如果一些其他后台进程正在使用数据库，哈里斯的答案可能无法工作，在我的情况下，它是延迟的工作，我做到了:
script/delayed_job stop
只有这样我才能删除/重置数据库。
更简单和更新的方法是:
使用ps -ef | grep postgres找到连接# 须藤杀死-9“#”的连接
注:可能有相同的PID。杀死一个人就会杀死所有人。
我使用下面的rake任务来覆盖Rails drop_database方法。
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
编辑:这是Postgresql 9.2+
没有必要放弃。只需删除并重新创建公共模式。在大多数情况下，这有完全相同的效果。
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
