rake db:drop pgsql "database in use"
JeanMertz opened this issue · 8 comments
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
?
+1
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.
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
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/
an even simpler solution is to just killall pow
before dropping the db.
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.
Nice, I updated my blog post to mention this gem!