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

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};
      PSQL
end