database 杀死一个 postgresql 会话/连接

Kill a postgresql session/connection

提问by DanS

How can I kill all my postgresql connections?

我怎样才能杀死我所有的 postgresql 连接？

I'm trying a rake db:dropbut I get:

我正在尝试，rake db:drop但我得到：

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 postgresbut this doesn't work either:

我试过关闭我从 a 看到的进程，ps -ef | grep postgres但这也不起作用：

kill: kill 2358 failed: operation not permitted

回答by Frank Heikens

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.

您可以使用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'
    ;

Before executing this query, you have to REVOKEthe CONNECT privileges to avoid new connections:

在执行此查询之前，您必须撤销CONNECT 权限以避免新连接：

REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;

If you're using Postgres 8.4-9.1 use procpid instead of pid

如果您使用的是 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'
    ;

回答by Haris Krajina

Maybe just restart postgres=> sudo service postgresql restart

也许只是重新启动postgres=>sudo service postgresql restart

回答by Dorian

With all infos about the running process:

关于运行过程的所有信息：

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

回答by Juuso Ohtonen

MacOS, if postgresqlwas installed with brew:

MacOS，如果postgresql是用brew安装的：

brew services restart postgresql

Source: Kill a postgresql session/connection

来源：杀死一个 postgresql 会话/连接

回答by artemave

OSX, Postgres 9.2 (installed with homebrew)

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

If your datadir is elsewhere you can find out where it is by examining the output of ps aux | grep postgres

如果您的数据目录在其他地方，您可以通过检查输出来找出它的位置 ps aux | grep postgres

回答by Chris

This seems to be working for PostgreSQL 9.1:

这似乎适用于 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 hereand here.

从此处和此处找到的要点中提取。

Here's a modified versionthat works for both PostgreSQL 9.1 and 9.2.

这是一个适用于 PostgreSQL 9.1 和 9.2的修改版本。

回答by Chris Aitchison

I use the following rake task to override the Rails drop_databasemethod.

我使用以下 rake 任务来覆盖 Railsdrop_database方法。

lib/database.rake

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+

编辑：这是用于 Postgresql 9.2+

回答by Mr. Rene

Easier and more updated way is:

更简单和更新的方法是：

1. Use ps -ef | grep postgresto find the connection #
2. sudo kill -9 "#"of the connection

1. 使用ps -ef | grep postgres查找连接＃
2. sudo kill -9 "#"连接的

Note: There may be identical PID. Killing one kills all.

注意：可能有相同的PID。杀一个就是杀所有。

回答by Janki

SELECT 
pg_terminate_backend(pid) 
FROM 
pg_stat_activity 
WHERE
pid <> pg_backend_pid()
-- no need to kill connections to other databases
AND datname = current_database();
-- use current_database by opening right query tool

回答by Jamon Holmgren

I had this issue and the problem was that Navicat was connected to my local Postgres db. Once I disconnected Navicat the problem disappeared.

我遇到了这个问题，问题是 Navicat 连接到我本地的 Postgres 数据库。一旦我断开 Navicat，问题就消失了。

EDIT:

编辑：

Also, as an absolute last resortyou 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 everypostgresprocess has really terminated before attempting to restart PostgreSQL after this.

...这将杀死 postgres 用户正在访问的所有内容。避免在生产机器上执行此操作，但开发环境不应该有问题。在此之后尝试重新启动 PostgreSQL 之前，确保每个postgres进程都真正终止是至关重要的。

EDIT 2:

编辑2：

Due to this unix.SE postI've changed from kill -9to kill -15.

由于这个unix.SE后我已经从变kill -9到kill -15。