Drop database, postgresql

Question

I am trying to drop my production postgresql database but apparently, there are some processes that stop me. I host in DigitalOcean on Ubuntu 14.04 with nginx and puma.

The code I try in the console:

RAILS_ENV=production bundle exec rails db:drop DISABLE_DATABASE_ENVIRONMENT_CHECK=1

The error I get:

PG::ObjectInUse: ERROR:  database "****_production" is being accessed by other users
DETAIL:  There are 5 other sessions using the database.
/home/deploy/sites/***/shared/bundle/ruby/2.3.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'

Any idea how to stop all processes and reset the database?

You need to terminate backned : http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it/5408501#5408501 — Roman Tkachuk, Feb 03 '17 at 17:44

score 0 · Answer 1 · answered Feb 03 '17 at 17:39

You would need to shut down all processes that might be using your database. In your case it's most probably Puma, which you could stop by running pumactl stop.

If you still can't delete your DB, there might be other processes left there so have a look at the output of ps aux for anything that could still hold a connection (e.g. if you're using Sidekiq, Elasticsearch, etc). You can then terminate these by issuing a kill command with their process IDs.

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

It looks like your puma is still running which is using postgresql connection. Trying again after stopping all the process that might use your database connection. Also close any rails console that you would have open on server.

ps -aef | grep puma

This will give you the list of puma process that is running on server. You can get process id(PID) from there and use below to kill the process.

sudo kill -9 PID

Dont forget the replace PID with actual process id. Once you stopped all the processes that might using postgresql, you are good to go.

If are still not able to drop db and also not able to recall what might be using your database. Try forcefully dropping your database. See here how to drop database forcefully.

Yep, that changed the error to "There are 4 other processes". However, the first command does not show any other processes — Kristiyan Tsvetanov, Feb 03 '17 at 18:29

Drop database, postgresql

2 Answers2