如何杀死我所有的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';

其他回答

也许只是重启postgres => sudo服务postgresql重启

我使用下面的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+

OSX, Postgres 9.2(已安装自制程序)

$ 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

如果你的datadir在其他地方,你可以通过检查ps aux | grep postgres的输出来找到它

在PG管理中,您可以断开您的服务器(右键单击服务器)&所有会话将在重新启动时断开

关于运行进程的所有信息:

SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE pid <> pg_backend_pid()
AND datname = 'my_database_name';