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.

score 55 · Answer 1 · edited Nov 30 '15 at 09:45

55

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

edited Nov 30 '15 at 09:45

Community

1
1

answered Oct 16 '12 at 22:59

dbenhur

20,008
4
48
45

2

I'd recommend `REVOKE CONNECT ON DATABASE dbname FROM public;` then `SELECT pg_terminate_backend(pid) FROM pg_stat_activity where pid <> pg_backend_pid();` followed by `DROP DATABASE dbname;`. This gets rid of the race where new clients connect continually so you never get enough time to DROP the db. – Craig Ringer Oct 16 '12 at 23:27
Another recommendation: First, run "ALTER DATABASE dbname CONNECTION LIMIT 0;" then terminate query. Setting connection limit to 0 is not an obstacle to connect the database for you if you are using psql. – TraviJuu Oct 17 '12 at 05:39
6

In the version that I'm using (9.1.9), pid is not a column name, but procpid is: SELECT procpid FROM pg_stat_activity where procpid <> pg_backend_pid(); – Tyler Rick Aug 14 '13 at 20:10
1

I just get `ERROR: there is no parameter $1`. – mkataja Sep 30 '15 at 09:23
@mkataja `$1` is a placeholder for the pid of the backend you wish to terminate. If you're just executing sql statements with psql, literally substitute the pid number for $1. If you're writing a program, most db interface libraries offer methods to bind parameter values to statements containing `$x` placeholders, so use that to supply an integer containing the pid to terminate. – dbenhur Oct 01 '15 at 21:38

score 5 · Accepted Answer · edited May 23 '17 at 10:30

5

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'

edited May 23 '17 at 10:30

Community

1
1

answered Oct 17 '12 at 16:45

Graham Conzett

8,344
11
55
94

BTW, you don't need echo and pipe to send statements to psql. just use the `-c` cmdline option. – dbenhur Oct 18 '12 at 18:27
@dbenhur : can you update your comment with the -c option included ? – Krishna Prasad Varma Feb 25 '13 at 04:42

score 1 · Answer 3 · answered Jan 10 '14 at 14:49

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

For Rails 3.x:

# lib/tasks/databases.rake
def 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.rb
module 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
  end
end

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

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

3 Answers3

Linked