Capistrano与PostgreSQL，错误：其他用户正在访问数据库 [英] Capistrano with PostgreSQL, error: database is being accessed by other users

问题描述

我有一个Rails应用程序，它使用PostgreSQL作为后端，并带有一个试图模仿生产的证书环境，除了它需要定期对数据库进行质量检查以重置它。

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.

当我在部署过程中尝试从Capistrano任务执行 db：reset 时，出现错误：

When I attempt to execute db:reset from a Capistrano task during deployment I get the error:

错误：其他用户正在访问数据库 database_name

并且在重置过程中不能删除数据库导致部署失败的任务。有什么方法可以重置Capistrano的数据库连接，以便成功删除表？从Capistrano任务将SQL插入psql可能有效，但是我想知道是否有更好的方法来解决这个问题。

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.

推荐答案

我已将 dbenhur的答案与该Capistrano任务结合起来以达到结果我需要像魅力一样的作品：

I have combined dbenhur's answer with this Capistrano task to achieve the result I needed works like a charm:

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

这假定用户postgres的auth_method设置为信任在pg_hba.conf

This assumes the auth_method for user postgres set to 'trust' in pg_hba.conf

然后您可以在 update_code 之后和<$ c之前在部署任务中调用它$ c>迁移

after 'deploy:update_code', 'kill_postgres_connections'

这篇关于Capistrano与PostgreSQL，错误：其他用户正在访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！