Ruby-on-rails Capistrano 与 PostgreSQL，错误：其他用户正在访问数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12924466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Capistrano with PostgreSQL, error: database is being accessed by other users
提问by Graham Conzett
I have a Rails app that uses PostgreSQL as a backend with a cert environment that tries to mimic production, except that it needs to have the database reset periodically for QA.
我有一个 Rails 应用程序，它使用 PostgreSQL 作为后端，并带有一个尝试模拟生产的证书环境，但它需要定期重置数据库以进行 QA。
When I attempt to execute db:resetfrom a Capistrano task during deployment I get the error:
当我尝试db:reset在部署期间从 Capistrano 任务执行时，出现错误：
ERROR: database "database_name" is being accessed by other users
ERROR: database "database_name" is being accessed by other users
and the database cannot be dropped as part of the reset task resulting in deployment failing. Is there a way I can reset database connections from Capistrano so I can successfully drop the table? Piping the SQL to psql from a Capistrano task might work but I was wondering if there was a better way to go about this.
并且数据库不能作为导致部署失败的重置任务的一部分被删除。有没有办法可以从 Capistrano 重置数据库连接，以便我可以成功删除表？从 Capistrano 任务将 SQL 管道化到 psql 可能会起作用，但我想知道是否有更好的方法来解决这个问题。
采纳答案by Graham Conzett
I have combined dbenhur's answerwith this Capistrano task to achieve the result I needed works like a charm:
我将dbenhur 的回答与这个 Capistrano 任务结合起来，以达到我需要的效果，就像一个魅力：
desc 'kill pgsql users so database can be dropped'
task :kill_postgres_connections do
run 'echo "SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname=\'database_name\';" | psql -U postgres'
end
This assumes the auth_method for user postgres set to 'trust' in pg_hba.conf
这假设用户 postgres 的 auth_method 在 pg_hba.conf 中设置为“信任”
Then you can just call it in your deploy task after update_codeand before migrate
然后你可以在你的部署任务之后update_code和之前调用它migrate
after 'deploy:update_code', 'kill_postgres_connections'
回答by dbenhur
With PostgreSQL you can issue the following statement to return the backend pids of all open connections other than then this one:
使用 PostgreSQL，您可以发出以下语句以返回除此之外的所有打开连接的后端 pid：
SELECT pid FROM pg_stat_activity where pid <> pg_backend_pid();
Then you can issue a a termination request to each of those backends with
然后你可以向每个后端发出终止请求
SELECT pg_terminate_backend();
Binding the pids returned from the first statement to each pg_terminate_backend exec.
将从第一个语句返回的 pid 绑定到每个 pg_terminate_backend exec。
If the other connections are not using the same user as you, you will have to connect as a superuser to successfully issue the terminates.
如果其他连接与您使用的用户不同，则您必须以超级用户身份连接才能成功发出终止。
UPDATE: Incorporating comments and expressing as Capistrano task:
更新：合并评论并表达为 Capistrano 任务：
desc "Force disconnect of open backends and drop database"
task :force_close_and_drop_db do
dbname = 'your_database_name'
run "psql -U postgres",
:data => <<-"PSQL"
REVOKE CONNECT ON DATABASE #{dbname} FROM public;
ALTER DATABASE #{dbname} CONNECTION LIMIT 0;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND datname='#{dbname}';
DROP DATABASE #{dbname};
PSQL
end
回答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/）
