Rails + Postgres drop error: database is being accessed by other users

Question

I have a rails application running over Postgres.

I have two servers: one for testing and the other for production.

Very often I need to clone the production DB on the test server.

The command I'm runnig via Vlad is:

rake RAILS_ENV='test_server' db:drop db:create

The problem I'm having is that I receive the following error:

ActiveRecord::StatementInvalid: PGError: ERROR: database <database_name> is being accessed by other users DROP DATABASE IF EXISTS <database_name>

This happens if someone has accessed the application via web recently (postgres keeps a "session" opened)

Is there any way that I can terminate the sessions on the postgres DB?

Thank you.

Edit

I can delete the database using phppgadmin's interface but not with the rake task.

How can I replicate phppgadmin's drop with a rake task?

Smoothie

Make sure you don't have connections to the database or it won't drop it. Check more about this here. – Company

Answer 1

If you kill the running postgresql connections for your application, you can then run db:drop just fine. So how to kill those connections? I use the following rake task:

# lib/tasks/kill_postgres_connections.rake
task :kill_postgres_connections => :environment do
  db_name = "#{File.basename(Rails.root)}_#{Rails.env}"
  sh = <<EOF
ps xa \
  | grep postgres: \
  | grep #{db_name} \
  | grep -v grep \
  | awk '{print $1}' \
  | xargs kill
EOF
  puts `#{sh}`
end

task "db:drop" => :kill_postgres_connections

Killing the connections out from under rails will sometimes cause it to barf the next time you try to load a page, but reloading it again re-establishes the connection.

Yellowhammer

I had to add sudo to xargs and change the database name, but it works. TY – Lyda

Same for me... changed to "sudo xargs kill" and hard-coded db_name to "my-development-database-name" – Janis

task "db:drop" => :kill_postgres_connections I think this line should be removed, it danger to extend behavior of system task, from my view. – Hateful

Rather than hardcode your database name, just use the following: db_name = Rails.configuration.database_configuration[Rails.env]['database'] – Searle

Answer 2

Easier and more updated way is: 1. Use ps -ef | grep postgres to find the connection # 2. sudo kill -9 "# of the connection

Note: There may be identical PID. Killing one kills all.

Twayblade

Which number in the result represents the PID? I see 3 unlabeled columns with numbers that look like PID. – Simple

@Simple second column (I'm using Mac Terminal) – Mantua

Nothing is found with ps, but still get the error on db:drop. – Testerman

Answer 3

Here's a quick way to kill all the connections to your postgres database.

sudo kill -9 `ps -u postgres -o pid` 

Warning: this will kill any running processes that the postgres user has open, so make sure you want to do this first.

Slipslop

In my system I use sudo kill -9 `ps -u postgres -o pid=` instead, so a PID header won't be printed by ps, so a string argument is not passed to kill, so an error won't be raised. Great tip in any case. – Unprovided

I keep getting upvoted and downvoted, resulting in near-zero rating. Seems to be a controversial "quick fix." Let me just state for the record that I did give a warning that it is dangerous. :) – Slipslop

Use this to start postgresql again if you're on Ubuntu: sudo service postgresql start – Repand

Answer 4

I use the following rake task to override the Rails drop_database method.

lib/database.rake

require 'active_record/connection_adapters/postgresql_adapter'
module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter
      def drop_database(name)
        raise "Nah, I won't drop the production database" if Rails.env.production?
        execute <<-SQL
          UPDATE pg_catalog.pg_database
          SET datallowconn=false WHERE datname='#{name}'
        SQL

        execute <<-SQL
          SELECT pg_terminate_backend(pg_stat_activity.pid)
          FROM pg_stat_activity
          WHERE pg_stat_activity.datname = '#{name}';
        SQL
        execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
      end
    end
  end
end

Laureen

Have you ever ended up reading that production warning? Just curious :P – Sustentacular

Answer 5

Step 1

Get a list of all postgres connections with ps -ef | grep postgres

The list will look like this:

  502   560   553   0 Thu08am ??         0:00.69 postgres: checkpointer process             
  502   565   553   0 Thu08am ??         0:00.06 postgres: bgworker: logical replication launcher
  502 45605   553   0  2:23am ??         0:00.01 postgres: st myapp_development [local] idle 

Step 2

Stop whatever connection you want with sudo kill -9 <pid>, where pid is the value in the second column. In my case, I wanted to stop the last row, with pid 45605, so I use:

sudo kill -9 45605

Moschatel

Answer 6

When we used the "kill processes" method from above, the db:drop was failing (if :kill_postgres_connections was prerequisite). I believe it was because the connection which that rake command was using was being killed. Instead, we are using a sql command to drop the connection. This works as a prerequisite for db:drop, avoids the risk of killing processes via a rather complex command, and it should work on any OS (gentoo required different syntax for kill).

cmd = %(psql -c "SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE procpid <> pg_backend_pid();" -d '#{db_name}')

Here is a rake task that reads the database name from database.yml and runs an improved (IMHO) command. It also adds db:kill_postgres_connections as a prerequisite to db:drop. It includes a warning that yells after you upgrade rails, indicating that this patch may no longer be needed.

see: https://gist.github.com/4455341, references included

Meanly

Answer 7

Please check if your rails console or server is running in another tab and then

stop the rails server and console.

then run

 rake db:drop

Pierette

Answer 8

Let your application close the connection when it's done. PostgreSQL doesn't keep connections open , it's the application keeping the connection.

Pinchhit

I can delete the database using phppgadmin's interface but not with the rake task. How can I replicate phppgadmin's drop with a rake task? – Smoothie

Sorry, can't help you there, I have no experience with rake. But, the error indicates another user is still using the database. That's why you can't delete the database, not by rake not by PhpPgAdmin, impossible. From the manual, DROP DATABASE: it cannot be executed while you or anyone else are connected to the target database. – Pinchhit

Answer 9

This worked for me (rails 6): rake db:drop:_unsafe

I think we had something in our codebase that initiated a db connection before the rake task attempted to drop it.

Bobodioulasso

Answer 10

[OSX][Development/Test] Sometimes it is hard to determine the proper PID when you have a lot of PostgreSQL processes like checkpointer, autovacuum launcher, etc. In this case, you can simply run:

brew services restart postgresql@12

Cajolery

Most straightforward answer here. – Womanlike

Answer 11

I wrote a gem called pgreset that will automatically kill connections to the database in question when you run rake db:drop (or db:reset, etc). All you have to do is add it to your Gemfile and this issue should go away. At the time of this writing it works with Rails 4 and up and has been tested on Postgres 9.x. Source code is available on github for anyone interested.

gem 'pgreset'

Sontag

Nothing but your gem would do it - the connection did not exist (ps grep searching etc), but rails thought it did. Thanks Much!! – Testerman

Answer 12

Rails is likely connecting to the database to drop it but when you log in via phppgadmin it is logging in via the template1 or postgres database, thus you are not affected by it.

Capapie

How can I force rails to drop the database? Should I define my own rake action using postgres SQL commands? – Smoothie

Answer 13

After restarting the server or computer, please try again.

It could be the simple solution.

Bigamous

Answer 14

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/)

Halifax

Answer 15

I had this same issue when working with a Rails 5.2 application and PostgreSQL database in production.

Here's how I solved it:

First, log out every connection to the database server on the PGAdmin Client if any.

Stop every session using the database from the terminal.

sudo kill -9 `ps -u postgres -o pid=`

Start the PostgreSQL server, since the kill operation above stopped the PostgreSQL server.

sudo systemctl start postgresql

Drop the database in the production environment appending the production arguments.

rails db:drop RAILS_ENV=production DISABLE_DATABASE_ENVIRONMENT_CHECK=1

That's all.

I hope this helps

Subtrahend

Answer 16

If you dockerized your app, then restart your db service

sudo docker-compose restart db

Muskellunge

Answer 17

Just make sure that the you have exited the rails console on any open terminal window and exited the rails server...this is one of the most common mistake made by people

Marcasite

Answer 18

I had a similar error saying 1 user was using the database, I realized it was ME! I shut down my rails server and then did the rake:drop command and it worked!

Gopak

Answer 19

Solution

Bash script

ENV=development

# restart postgresql
brew services restart postgresql

# get name of the db from rails app
RAILS_CONSOLE_COMMAND="bundle exec rails c -e $ENV"
DB_NAME=$(echo 'ActiveRecord::Base.connection_config[:database]' | $RAILS_CONSOLE_COMMAND | tail -2 | tr -d '\"')

# delete all connections to $DB_NAME
for pid in $(ps -ef | grep $DB_NAME | awk {'print$2'})
do
   kill -9 $pid
done

# drop db
DISABLE_DATABASE_ENVIRONMENT_CHECK=1 RAILS_ENV=$ENV bundle exec rails db:drop:_unsafe

Cambodia

Answer 20

ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR: database "database_enviroment" is being accessed by other users DETAIL: There is 1 other session using the database.

For rails 7: you can use -> rails db:purge

Reiche