kh0yjg
Last Updated: February 25, 2016
·
1.544K
· alistairholt

Rails & Postgres: Terminating active connections before dropping the database

Occasionally I have the need to drop the database in a Rails app but if you've got active connections to the database you'll get a bunch of errors when you‘re running rake db:drop or rake db:drop:all.

This is the current solution I'm using based on my original tip which used shell scripting and the alternative approach offered up in the comments by Manuel Meurer which aims to be more robust and less hacky.

WARNING: This requires Ruby 2+ for Module#prepend

Add the following file to /lib/tasks/postgresql_database_tasks_extensions.rb:

module PostgreSQLDatabaseTasksExtensions
  def drop
    establish_master_connection
    pid_column = PG.library_version >= 90200 ? 'pid' : 'procpid'
    connection.select_all %Q(
      SELECT pg_terminate_backend(pg_stat_activity.#{pid_column})
      FROM pg_stat_activity
      WHERE datname = '#{configuration['database']}'
        AND #{pid_column} <> pg_backend_pid();
    )
    super
  end
end

module ActiveRecord
  module Tasks
    class PostgreSQLDatabaseTasks
      prepend PostgreSQLDatabaseTasksExtensions
    end
  end
end

And then add the following line to the top of your app's Rakefile (/lib/tasks/YOUR_APP.rake or similar):

require_relative 'postgresql_database_tasks_extensions'

2 Responses
Add your response

13550

Thanks this helped. I had to change the library version to 90109 for psql 9.1.9

over 1 year ago ·
13551

Glad it helped. It is supposedly 9.2 in which the pid column name changed, did you find you needed 'pid' in 9.1.9?

over 1 year ago ·