如何杀死我所有的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
没有必要放弃。只需删除并重新创建公共模式。在大多数情况下,这有完全相同的效果。
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
我有这个问题,问题是Navicat连接到我本地的Postgres数据库。一旦我断开Navicat,问题就消失了。
编辑:
此外,作为最后的手段,你可以备份你的数据,然后运行这个命令:
sudo kill -15 `ps -u postgres -o pid`
... 这将杀死postgres用户正在访问的所有内容。避免在生产机器上这样做,但在开发环境中应该不会有问题。在尝试重启PostgreSQL之前,确保每个postgres进程都已经真正终止是至关重要的。
编辑2:
由于这个unix。SE post我从击杀-9改为击杀-15。
可以使用pg_terminate_backend()终止连接。你必须是超级用户才能使用这个功能。这在所有操作系统上都是一样的。
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'
;
在执行这个查询之前,你必须撤销CONNECT权限以避免新的连接:
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
如果你正在使用Postgres 8.4-9.1,请使用procpid而不是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'
;