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.

can you try using rails instead of rake like `RAILS_ENV=production DISABLE_DATABASE_ENVIRONMENT_CHECK=1 bin/rails db:reset` — neydroid, Nov 23 '16 at 23:23

score 44 · Accepted Answer · edited Dec 12 '16 at 09:40

44

Try this it worked for me:

RAILS_ENV=production rake db:drop DISABLE_DATABASE_ENVIRONMENT_CHECK=1

in a single line.

edited Dec 12 '16 at 09:40

Rav

1,327
3
18
32

answered Dec 12 '16 at 08:35

Prashant

593
4
6

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? – Miguel Peniche Jan 30 '22 at 20:46

yozzz · Answer 2 · 2018-05-15T12:43:11.743

36

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

edited May 15 '18 at 12:43

answered Nov 21 '17 at 13:29

yozzz

1,267
1
22
30

This is the proper way to do this after the import of a production database to your development environment. – iGEL Dec 21 '20 at 15:22
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```) – Robert Travis Pierce Mar 05 '21 at 11:02
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. – Tintin81 Dec 22 '22 at 15:40

score 17 · Answer 3 · answered Sep 24 '17 at 16:19

17

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

answered Sep 24 '17 at 16:19

Kostas Rousis

5,918
1
33
38

Just noticed the title doesn't include Heroku, but this is what I was looking for! – karns Jan 17 '20 at 20:33

Marshall · Answer 4 · 2018-08-27T18:03:58.987

3

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

edited Aug 27 '18 at 18:03

answered Jul 11 '17 at 13:26

Marshall

88
6

yep, that's the way to go if you won't disconnect your db and can rollback your migrations – hatenine Feb 05 '19 at 12:10
Worked like a charm - migrated back my DB to the beginning of time, re-deploying my code then automatically migrated to the latest schema – AxelTheGerman Nov 19 '21 at 16:40

score 2 · Answer 5 · answered Jan 23 '19 at 16:06

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!

score 2 · Answer 6 · answered Feb 07 '20 at 13:46

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

score 1 · Answer 7 · edited May 23 '17 at 12:34

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

score 0 · Answer 8 · answered Nov 27 '16 at 02:49

0

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.

answered Nov 27 '16 at 02:49

marahin

1

Rails 5 how to clear or delete production postgres database

8 Answers8