Rails 5 how to clear or delete production postgres database

Question

I am trying to delete a production database so I can start fresh. When I upgraded to rails 5 from rails 4, it is now protecting the production database from accidental deletion. It shows the following error message when I run rake db:reset.

/app# rake db:reset
  ActiveRecord::SchemaMigration Load (1.8ms)  SELECT "schema_migrations".* FROM "schema_migrations"
   (1.6ms)  SELECT "ar_internal_metadata"."value" FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1  [["key", :environment]]
  ActiveRecord::SchemaMigration Load (0.2ms)  SELECT "schema_migrations".* FROM "schema_migrations"
   (0.3ms)  SELECT "ar_internal_metadata"."value" FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1  [["key", :environment]]
  ActiveRecord::SchemaMigration Load (0.3ms)  SELECT "schema_migrations".* FROM "schema_migrations"
   (0.2ms)  SELECT "ar_internal_metadata"."value" FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1  [["key", :environment]]
rake aborted!
ActiveRecord::ProtectedEnvironmentError: You are attempting to run a destructive action against your 'production' database.
If you are sure you want to continue, run the same command with the environment variable:
DISABLE_DATABASE_ENVIRONMENT_CHECK=1
/usr/local/bundle/gems/activerecord-5.0.0.1/lib/active_record/tasks/database_tasks.rb:51:in `check_protected_environments!'
/usr/local/bundle/gems/activerecord-5.0.0.1/lib/active_record/railties/databases.rake:11:in `block (2 levels) in <top (required)>'
/usr/local/bundle/gems/rake-11.3.0/exe/rake:27:in `<top (required)>'
Tasks: TOP => db:reset => db:drop => db:check_protected_environments
(See full trace by running task with --trace)

It says that my adding the environment variable DISABLE_DATABASE_ENVIRONMENT_CHECK=1 to the command should work but it does not. I run it and it does nothing.

<606723-x9dh4:/app# DISABLE_DATABASE_ENVIRONMENT_CHECK=1 rake db:reset       
  ActiveRecord::SchemaMigration Load (1.6ms)  SELECT "schema_migrations".* FROM "schema_migrations"

Anyone know what I am doing wrong? Appreciate the help!

UPDATE:

My server is deployed using kubernetes. I am guessing that I am not able to reset the database because the server is running.

Flitting

can you try using rails instead of rake like RAILS_ENV=production DISABLE_DATABASE_ENVIRONMENT_CHECK=1 bin/rails db:reset – Hamstring

No luck, just does the same thing. – Flitting

Answer 1

Try this it worked for me:

RAILS_ENV=production rake db:drop DISABLE_DATABASE_ENVIRONMENT_CHECK=1 

in a single line.

Menchaca

Is there a way to add this to a config file in local environment so we don't need to write it every time in the terminal? – Bernadinebernadotte

Answer 2

It also happens when you dump production database into local. If you want to delete it on local machine, you will need to set bin/rails db:environment:set RAILS_ENV=development, and after rake db:drop

Roselba

This is the proper way to do this after the import of a production database to your development environment. – Durfee

Thank you for this. I had used my heroku production in development for the first time recently (heroku pg:pull DATABASE_URL my_app_name --app my_app_name --remote production) and didn't realize I would have to alter the way I dropped it. (rails db:environment:set RAILS_ENV=development; rails db:drop) – Sedgewinn

If you at one point dumped your production DB into your development DB, the value in your ar_internal_metadata has probably changed to production. So you might want to replace it with development before running rake commands in your local development environment. – Allimportant

Answer 3

From Heroku's documentation on Running Rake commands:

The db:reset task is not supported. Heroku apps do not have permission to drop and create databases. Use the heroku pg:reset command instead.

So instead of trying to have rails or rake db:reset, try this:

heroku pg:reset

Bozuwa

Just noticed the title doesn't include Heroku, but this is what I was looking for! – Smoothen

Answer 4

Well, even though this is an old post, but for future reference, you can do

    rake db:migrate VERSION=0

And then

    rake db:migrate

Wivern

yep, that's the way to go if you won't disconnect your db and can rollback your migrations – Shiah

Worked like a charm - migrated back my DB to the beginning of time, re-deploying my code then automatically migrated to the latest schema – Ichthyosaur

Answer 5

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, stop every session using the database to avoid database is being accessed by other users error.

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

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

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

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

rails db:create RAILS_ENV=production

That's all.

I hope this helps

Tuberose

Answer 6

i know, it could be a little late, but, if it fails to drop with standart rails way, u can always use psql like psql --u your_user

then enter password type \l to list all db. in my case, psql reject to drop single table, and i create additional db, like 'testdb', typed \c testdb to establish connection with it, then type drop database 'olddb_name';, then create database 'olddb_name'; and its done!

Roebuck

Answer 7

It is not working but you should run commands like this

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

and when you drop the table you have to create it back.So you will get error if you don't have rights to createdb.Here your asnwer Postgres permission denied to create database on rake db:create:all

when you type psql you exit from there by typing \q.

Like i said this is not the answer but i hope this will save your time while you are searching. Beacuse i am right there right now. GL&HF

Buoyant

Answer 8

Can you give below a shot?

RAILS_ENV=production rake db:drop
RAILS_ENV=production rake db:create

This is an old method, but that's how I used to reset the database to pristine level.

Evert