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.
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.
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}; PSQL end
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With