Rails + Postgres放置错误：其他用户正在访问数据库 [英] Rails + Postgres drop error: database is being accessed by other users

问题描述

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

I have a rails application running over Postgres.

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

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.

我通过Vlad运行的命令是：

The command I'm runnig via Vlad is:

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>

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

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

有什么方法可以终止postgres DB上的会话？

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

谢谢。

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

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

如何通过rake任务复制phppgadmin的drop？

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

推荐答案

如果杀死正在运行的postgresql连接到您的应用程序，然后就可以运行db：drop了。那么如何杀死那些联系呢？我使用以下rake任务：

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:

# 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 $1}' \
  | 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 + Postgres放置错误：其他用户正在访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！