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

Question

I have a Rails app that uses PostgreSQL as a backend with a cert environment that tries to mimic production, except that it needs to have the database reset periodically for QA.

When I attempt to execute db:reset from a Capistrano task during deployment I get the error:

ERROR: database "database_name" is being accessed by other users

and the database cannot be dropped as part of the reset task resulting in deployment failing. Is there a way I can reset database connections from Capistrano so I can successfully drop the table? Piping the SQL to psql from a Capistrano task might work but I was wondering if there was a better way to go about this.

Answer 1

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'