rake db:drop pgsql "database in use"

[JeanMertz]

Not sure if this is related to the pow, powder or pg gem, but whenever I want a 100% clean slate on my development app I usually do:

rake db:drop:all
rake db:create
rake RAILS_ENV=test db:create
rake db:migrate
rake RAILS_ENV=test db:migrate

However, when using postgresql with pow and managing pow through the powder command-line tool, I get the error database in use when I try to drop the database.

This happens even if I do any combination of powder down, powder restart or powder remove. The only solution I've found is to go into the os x activity window and close the pow session. Even though the session is automatically restarted (and shows up again in the list) I can still drop the database after this action.

Can this somehow be fixed to make it easier to drop a database in use by pow?

[bwbuchanan]

+1

[josh]

Pow doesn't make any connections to your database that wouldn't happen with script/server. Its unfortunate that pg has issues dropping databases while you have multiple connections open.

[jhirn]

Perhaps late to the game but you can monkey patch databases.rake kill postgres connections and not require shutting down pow before a db:reset.

# put this code into lib/tasks/database.rake
def kill_postgres_connections_sql(database)
  <<-EOS
  SELECT
    pg_terminate_backend(pid)
  FROM
    pg_stat_activity
  WHERE pid <> pg_backend_pid() AND datname = '#{database}';
  EOS
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.establish_connection(config.merge('database' =>'postgres','schema_search_path' => 'public'))
    ActiveRecord::Base.connection.execute(kill_postgres_connections_sql(config['database']))
    ActiveRecord::Base.connection.drop_database(config['database'])
  end
end

Idea was from this stack overflow article but i updated it to work with Postgres 9.2. The above script works with Rails 3.2.13

http://stackoverflow.com/questions/5108876/kill-a-postgresql-session-connection

[manuelmeurer]

For people coming here through Google, this is how you do it in Rails 4:

# 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

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

[christiannaths]

@manuelmeurer 👍

[ibroadfo]

an even simpler solution is to just killall pow before dropping the db.

[dafalcon]

Thanks for the solutions! I packed this up in a gem called pgreset, including support for older versions of postgresql, at https://rubygems.org/gems/pgreset. Source is available at https://github.com/falconed/pgreset.

[manuelmeurer]

Nice, I updated my blog post to mention this gem!