Ruby-on-rails Rails + Postgres drop 错误：其他用户正在访问数据库

Rails + Postgres drop error: database is being accessed by other users

提问by fjuan

I have a rails application running over Postgres.

我有一个运行在 Postgres 上的 Rails 应用程序。

I have two servers: one for testing and the other for production.

我有两台服务器：一台用于测试，另一台用于生产。

Very often I need to clone the production DB on the test server.

我经常需要在测试服务器上克隆生产数据库。

The command I'm runnig via Vlad is:

我通过 Vlad 运行的命令是：

rake RAILS_ENV='test_server' db:drop db:create

The problem I'm having is that I receive the following error:

我遇到的问题是我收到以下错误：

ActiveRecord::StatementInvalid: PGError: ERROR: database <database_name> is being accessed by other users DROP DATABASE IF EXISTS <database_name>

This happens if someone has accessed the application via web recently (postgres keeps a "session" opened)

如果有人最近通过网络访问了应用程序（postgres 保持“会话”打开），就会发生这种情况

Is there any way that I can terminate the sessions on the postgres DB?

有什么办法可以终止 postgres 数据库上的会话吗？

Thank you.

谢谢你。

Edit

编辑

I can delete the database using phppgadmin's interface but not with the rake task.

我可以使用 phppgadmin 的界面删除数据库，但不能使用 rake 任务。

How can I replicate phppgadmin's drop with a rake task?

如何使用 rake 任务复制 phppgadmin 的 drop？

采纳答案by encoded

If you kill the running postgresql connections for your application, you can then run db:drop just fine. So how to kill those connections? I use the following rake task:

如果您终止了正在运行的应用程序的 postgresql 连接，那么您可以运行 db:drop 就好了。那么如何杀死这些连接呢？我使用以下 rake 任务：

# lib/tasks/kill_postgres_connections.rake
task :kill_postgres_connections => :environment do
  db_name = "#{File.basename(Rails.root)}_#{Rails.env}"
  sh = <<EOF
ps xa \
  | grep postgres: \
  | grep #{db_name} \
  | grep -v grep \
  | awk '{print }' \
  | xargs kill
EOF
  puts `#{sh}`
end

task "db:drop" => :kill_postgres_connections

Killing the connections out from under rails will sometimes cause it to barf the next time you try to load a page, but reloading it again re-establishes the connection.

有时，当您尝试加载页面时，从 Rails 下杀死连接有时会导致它呕吐，但再次重新加载它会重新建立连接。

回答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 "# of the connection

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

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

回答by Jamon Holmgren

Here's a quick way to kill all the connections to your postgres database.

这是终止与 postgres 数据库的所有连接的快速方法。

sudo kill -9 `ps -u postgres -o pid` 

Warning: this will kill any running processes that the postgresuser has open, so make sure you want to do this first.

警告：这将杀死postgres用户打开的所有正在运行的进程，因此请确保您要先执行此操作。

回答by Matt Scilipoti

When we used the "kill processes" method from above, the db:drop was failing (if :kill_postgres_connections was prerequisite). I believe it was because the connection which that rake command was using was being killed. Instead, we are using a sql command to drop the connection. This works as a prerequisite for db:drop, avoids the risk of killing processes via a rather complex command, and it should work on any OS (gentoo required different syntax for kill).

当我们使用上面的“终止进程”方法时，db:drop 失败（如果 :kill_postgres_connections 是先决条件）。我相信这是因为 rake 命令使用的连接被杀死了。相反，我们使用 sql 命令来断开连接。这是 db:drop 的先决条件，避免了通过相当复杂的命令杀死进程的风险，并且它应该适用于任何操作系统（gentoo 需要不同的 语法kill）。

cmd = %(psql -c "SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE procpid <> pg_backend_pid();" -d '#{db_name}')

Here is a rake task that reads the database name from database.yml and runs an improved (IMHO) command. It also adds db:kill_postgres_connections as a prerequisite to db:drop. It includes a warning that yells after you upgrade rails, indicating that this patch may no longer be needed.

这是一个 rake 任务，它从 database.yml 读取数据库名称并运行改进的（恕我直言）命令。它还添加了 db:kill_postgres_connections 作为 db:drop 的先决条件。它包含一个警告，在您升级 rails 后会大喊大叫，表明可能不再需要此补丁。

see: https://gist.github.com/4455341, references included

请参阅：https: //gist.github.com/4455341，包括参考资料

回答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

回答by Frank Heikens

Let your application close the connection when it's done. PostgreSQL doesn't keep connections open , it's the application keeping the connection.

让您的应用程序在完成后关闭连接。PostgreSQL 不保持连接打开，它是保持连接的应用程序。

回答by Amol Udage

Please check if your rails console or server is running in another tab and then

请检查您的 rails 控制台或服务器是否在另一个选项卡中运行，然后

stop the rails server and console.

停止 Rails 服务器和控制台。

then run

然后运行

 rake db:drop

回答by Joshua D. Drake

Rails is likely connecting to the database to drop it but when you log in via phppgadmin it is logging in via the template1 or postgres database, thus you are not affected by it.

Rails 可能会连接到数据库以删除它，但是当您通过 phppgadmin 登录时，它是通过 template1 或 postgres 数据库登录的，因此您不受它的影响。

回答by Dan

I wrote a gem called pgresetthat will automatically kill connections to the database in question when you run rake db:drop (or db:reset, etc). All you have to do is add it to your Gemfile and this issue should go away. At the time of this writing it works with Rails 4 and up and has been tested on Postgres 9.x. Source code is available on githubfor anyone interested.

我写了一个名为pgreset的 gem ，当您运行 rake db:drop（或 db:reset 等）时，它会自动终止与相关数据库的连接。你所要做的就是将它添加到你的 Gemfile 中，这个问题就会消失。在撰写本文时，它适用于 Rails 4 及更高版本，并已在 Postgres 9.x 上进行测试。任何感兴趣的人都可以在github上找到源代码。

gem 'pgreset'

回答by Manuel Meurer

You can simply monkeypatch the ActiveRecord code that does the dropping.

您可以简单地对执行删除操作的 ActiveRecord 代码进行猴子补丁。

For Rails 3.x:

对于 Rails 3.x：

# lib/tasks/databases.rake
def drop_database(config)
  raise 'Only for Postgres...' unless config['adapter'] == 'postgresql'
  Rake::Task['environment'].invoke
  ActiveRecord::Base.connection.select_all "select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='#{config['database']}' AND state='idle';"
  ActiveRecord::Base.establish_connection config.merge('database' => 'postgres', 'schema_search_path' => 'public')
  ActiveRecord::Base.connection.drop_database config['database']
end

For Rails 4.x:

对于 Rails 4.x：

# config/initializers/postgresql_database_tasks.rb
module ActiveRecord
  module Tasks
    class PostgreSQLDatabaseTasks
      def drop
        establish_master_connection
        connection.select_all "select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='#{configuration['database']}' AND state='idle';"
        connection.drop_database configuration['database']
      end
    end
  end
end

(from: http://www.krautcomputing.com/blog/2014/01/10/how-to-drop-your-postgres-database-with-rails-4/)

（来自：http: //www.krautcomputing.com/blog/2014/01/10/how-to-drop-your-postgres-database-with-rails-4/）