Unable to make 'rake db:drop' work in development with Rails 5.1.4

Question

I'm no longer able to rake db:drop since I upgraded from Rails 4.2 to Rails 5.1.4.
I get the following error message whenever I try to run the task:

PG::ObjectInUse: ERROR:  database "myapp_development" is being 
accessed by other users
DETAIL:  There is 1 other session using the database.
: DROP DATABASE IF EXISTS "myapp_development"
Couldn't drop database 'myapp_development'
rails aborted!
ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR:  database 
"myapp_development" is being accessed by other users
DETAIL:  There is 1 other session using the database.
: DROP DATABASE IF EXISTS "myapp_development"

I used to make it work with this hack:
Rails + Postgres drop error: database is being accessed by other users

Now, when I integrate this solution into my application, I get another error message:

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
Couldn't drop database 'myapp_development'
rails aborted!
PG::AdminShutdown: FATAL:  terminating connection due to 
administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Do you guys have a fix for this issue?
Btw, I'm running Postgres 9.6, ruby 2.4.2 and Rails 5.1.4.

I always receive this error when I actually access the database with another application at the same time. This is typically pgAdmin. — ulferts, Sep 27 '17 at 15:17
Side note, but it's not advised to upgrade directly from `4.2.x` to `5.1.x`. You'll probably find it easier to upgrade to `5.0.x` first. Your question makes it sound like a new bug specific to `5.1.4`, but (if this is even a rails config error/bug at all!!) it could have been caused by *any* of the incremental versions in between. — Tom Lord, Sep 27 '17 at 15:43
@max I don't but even if I did, the script provided here allowed me to run the task. https://stackoverflow.com/questions/2369744/rails-postgres-drop-error-database-is-being-accessed-by-other-users — lpdahito, Sep 27 '17 at 17:16
gem pgreset was the only thing that worked for me - ps, etc could find no such connection. — JosephK, Jun 10 '20 at 14:45

score 3 · Answer 1 · answered Sep 27 '17 at 18:22

3

Can you try to restart your DB, For postgres

sudo service postgresql restart

then try rails db:drop / rake db:drop, maybe it will work

answered Sep 27 '17 at 18:22

Prasanth_Rubyist

440
5
14

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

To drop your database your database should not be accessed by any application.

This error tells that

1. you are using rails console which is using database OR,
2. you are using some tools tool postico,pgadmin, mysql workbench which is accessing database OR
3. you are using IDE like rubymine and accessing database via it OR
4. there is sidekiq or any other background job server running which accessing it.

Solution:
Before dropping database take care of following things
exit all rails console,
close database tools like mysql workbench,pgadmin
close database tabs in IDE
stop sidekiq or any other background job

score 0 · Answer 3 · answered Sep 27 '17 at 16:12

Why ?

database "myapp_development" is being accessed by other users.

You will get this error if your database is being opened either in console i.e rails c or rails s or any other mode.

Solution

1.Close console wherever you opened rails c or stop server if running

2.If you are unable to find console or server running i.e if everything closed still you are gettin error. Then try to close running process using below command

ps -au

This command will show all running processes.Just find keyword rails c/localhost/postgres.You can also filter processes using grep command like this

ps -au | grep 'rails c'

And kill the process using below command

kill -9 PID_NUMBER // PID NUMBER you will find in second column when you run ps -au.

score 0 · Answer 4 · answered Feb 18 '18 at 18:32

It means that the database is being used by a user other than the one attempting to drop the table. The user attempting to drop the table is the one specified in config/database.yml

To see how that is, do:

za:myapp za$ cat config/database.yml | grep username
  #username: myapp
  # The password associated with the postgres role (username).
  username: myapp

Make sure that no other user is connected to the database while you are trying to drop it. In my case, another user "za" was connected to the database. I killed the session of that user and all went well.

za:myapp za$ rails db:drop
Dropped database 'myapp_development'
Dropped database 'myapp_test'

Back to work, create and then migrate:

za:myapp za$ rake db:create
za:myapp za$ rake db:migrate
RubyDep: WARNING: (To disable warnings, see:http://github.com/e2/ruby_dep/wiki/Disabling-warnings )
== 20180218181904 DeviseCreateUsers: migrating ================================
-- create_table(:users)
   -> 0.0286s
-- add_index(:users, :email, {:unique=>true})
   -> 0.0183s
-- add_index(:users, :reset_password_token, {:unique=>true})
   -> 0.0036s
== 20180218181904 DeviseCreateUsers: migrated (0.0507s) =======================

Unable to make 'rake db:drop' work in development with Rails 5.1.4

4 Answers4