論盡蛋
心で書く

Drop PostgreSQL database

Update:

If you are using Rails 4.x, please read this. Thank you Manuel Meurer for letting me know the following implementation applies to Rails 3.x only.

If you are using Rails 3.x, you may continue use the following patch.


Some of my projects uses PostgreSQL as my database. However, running rake db:drop always gives me the following error:

Couldn't drop PROJECT_NAME_development : #<ActiveRecord::StatementInvalid: PG::Error: ERROR:  database "PROJECT_NAME_development" is being accessed by other users
DETAIL:  There are 2 other session(s) using the database.
: DROP DATABASE IF EXISTS "PROJECT_NAME_development">

To resolve this problem, as hinted by the message about, is to stop all sessions using that database.

However, I just found (I think) better solution from this stackoverflow answer (which points to this gist).

It is to monkey patch the rake db:drop command. To do so, simply copy the code into lib/tasks/databases.rake and now rake db:drop is patched with this code which stops the connections.

The code is as follow:

#!ruby #{Rails.root}/lib/tasks/databases.rake
=begin
  Monkey Patch 
  activerecord-3.0.9/lib/active_record/railties/databases.rake
  clears obstinate stale PG session to get parallel_tests working
  also, PG user must be superuser to use these low level PG functions
=end
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.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.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))
    ActiveRecord::Base.connection.drop_database config['database']
  end
end

comments powered by Disqus