Capistrano with PostgreSQL, error: database is being accessed by other users

With PostgreSQL you can issue the following statement to return the backend pids of all open connections other than then this one:

SELECT pid FROM pg_stat_activity where pid <> pg_backend_pid();

Then you can issue a a termination request to each of those backends with

SELECT pg_terminate_backend($1);

Binding the pids returned from the first statement to each pg_terminate_backend exec.

If the other connections are not using the same user as you, you will have to connect as a superuser to successfully issue the terminates.

• Admin signalling functions docs
• Monitoring stats functions
• pg_stat_activity view docs

UPDATE: Incorporating comments and expressing as Capistrano task:

desc "Force disconnect of open backends and drop database"task :force_close_and_drop_db do  dbname = 'your_database_name'  run "psql -U postgres",      :data => <<-"PSQL"         REVOKE CONNECT ON DATABASE #{dbname} FROM public;         ALTER DATABASE #{dbname} CONNECTION LIMIT 0;         SELECT pg_terminate_backend(pid)           FROM pg_stat_activity           WHERE pid <> pg_backend_pid()           AND datname='#{dbname}';         DROP DATABASE #{dbname};      PSQLend

I have combined dbenhur's answer with this Capistrano task to achieve the result I needed works like a charm:

desc 'kill pgsql users so database can be dropped'task :kill_postgres_connections do  run 'echo "SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname=\'database_name\';" | psql -U postgres'end

This assumes the auth_method for user postgres set to 'trust' in pg_hba.conf

Then you can just call it in your deploy task after update_code and before migrate

after 'deploy:update_code', 'kill_postgres_connections'

You can simply monkeypatch the ActiveRecord code that does the dropping.

For Rails 3.x:

# lib/tasks/databases.rakedef drop_database(config)  raise 'Only for Postgres...' unless config['adapter'] == 'postgresql'  Rake::Task['environment'].invoke  ActiveRecord::Base.connection.select_all "select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='#{config['database']}' AND state='idle';"  ActiveRecord::Base.establish_connection config.merge('database' => 'postgres', 'schema_search_path' => 'public')  ActiveRecord::Base.connection.drop_database config['database']end

For Rails 4.x:

# config/initializers/postgresql_database_tasks.rbmodule 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  endend

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