How to empty a Heroku database

Question

I'm working on a Ruby on Rails 3 webapp on Heroku. How do I empty the database?

score 725 · Accepted Answer · edited Jan 25 '18 at 06:01

725

To drop the database, if you are using SHARED_DATABASE_URL:

$ heroku pg:reset DATABASE_URL

Now to recreate the database with nothing in it:

$ heroku run rake db:migrate

To populate the database with your seed data:

$ heroku run rake db:seed

---OR---

You can combine the last two (migrate & seed) into one action by executing this:

$ heroku run rake db:setup

Edit 2014-04-18: rake db:setup doesn't work with Rails 4, it fails with a Couldn't create database error.

Edit 2014-10-09: You can use rake db:setup with Rails 4. It does give you a Couldn't create database error (because the database was already created using the heroku pg:reset command). But it also loads your database schema and your seeds after the error message.

You can do this with pretty much any rake command, but there are exceptions. For example, db:reset doesn't work via heroku run rake. You have to use pg:reset instead.

More information can be found in Heroku's documentation:

Running Rake Commands

Reset Postgres DB

edited Jan 25 '18 at 06:01

Ryan

10,798
11
46
60

answered Jan 27 '11 at 19:06

Shaun

4,789
3
22
27

when yous say `your see data` what do you mean? is there a way I can add default data into the DB? – Jan 27 '11 at 20:26
I'm using heroku free account and it tells me that i am not the owner of the DB – Jan 27 '11 at 20:36
@Abra: I meant 'seed data'. Also, db:drop doesn't work in heroku. Corrected instructions to use the proper beginning command. – Shaun Jan 27 '11 at 21:12
'Seed data' is data you have set up to be placed in your database for testing. It's usually defined in db/seeds.rb – Shaun Jan 27 '11 at 21:40
23

Thank you. Actually, "heroku pg:reset --db SHARED_DATABASE_URL" did the trick. But you got me going in the right direction. – Jay Godse Mar 17 '11 at 22:17
5

@JayGodse Heroku now says `SHARED_DATABASE_URL is deprecated, please use SHARED_DATABASE` – Phil Oct 18 '11 at 09:40
6

Actually you should be checking in your schema.rb and you should use: rake db:schema:load – Amala Dec 02 '11 at 23:06
1

DATABASE_URL will generally work more than SHARED_DATABASE_URL. – quinn Sep 13 '12 at 21:15
19

They have changed it again now is `heroku pg:reset DATABASE` – Haris Krajina Jan 23 '13 at 23:48
9

To save a couple of seconds of time, use `heroku pg:reset DATABASE --confirm appname` – GangstaGraham Jun 08 '13 at 06:08
3

For the sake of completeness: To get the value for DATABASE run `heroku pg:info` – lambinator Aug 21 '13 at 05:51
1

`rake db:setup` seems disallowed now - you'll need to run `migrate` and `seed` separately instead. – Scott Fister Oct 07 '13 at 18:56
They have changed it again. The command that worked for me now is `heroku pg:reset DATABASE_URL` – Alan David Garcia Apr 07 '14 at 01:46
`heroku run rake db:migrate db:seed` that is guaranteed to work just like `rake db:setup` used to work. You can chain commands like this to save time. – aaron-coding Dec 05 '14 at 00:31
you can also try to reset DB using this gem https://github.com/igorkasyanchuk/rails_db, in each table view you can truncate table, or you can truncate DB using SQL – Igor Kasyanchuk Oct 28 '15 at 21:36
@AlanDavidGarcia It didn't change, it's just that the answer is misleading. The `DATABASE` should actually be ``, which is a placeholder for the database name. The default database is named `DATABASE_URL` which you can see if you run `heroku pg:info`, but you can actually add more databases if you like, and manage them separately. – Franklin Yu Jan 28 '17 at 22:04
1

Thank you! Only needed `heroku pg:reset` and then follow the prompts on my end. – guero64 May 24 '22 at 12:35
As of 2022 (only a couple more weeks to go before free goes away) @guero64 is correct – J.R. Bob Dobbs Oct 31 '22 at 03:20

Dave Sag · Answer 2 · 2013-08-22T07:00:03.120

102

Heroku has deprecated the --db option now, so now use:

heroku pg:reset DATABASE_URL --confirm {the name of your app}

It's a little confusing because you use the literal text SHARED_DATABASE but where I have written {the name of your app} substitute the name of your app. For example, if your app is called my_great_app then you use:

heroku pg:reset DATABASE_URL --confirm my_great_app

edited Aug 22 '13 at 07:00

answered Sep 07 '11 at 06:19

Dave Sag

13,266
14
86
134

3

if you just paste skip the bit after (and including) the -- heroku will tell you what to type.. – baash05 Feb 01 '12 at 22:48
useful command to get the name of your postgres database on heroku : heroku config | grep HEROKU_POSTGRESQL – zero_cool Jun 07 '14 at 00:50
"you use the literal text SHARED_DATABASE" ? You are using DATABASE_URL in your code samples. Do you mean that? – Michael Durrant Jun 17 '14 at 10:30
Nope. I was referring to the other answer's use of the words `SHARED_DATABASE` – Dave Sag Jun 17 '14 at 14:34

score 72 · Answer 3 · edited Mar 24 '12 at 15:28

72

To drop the database:

$ heroku pg:reset SHARED_DATABASE --confirm NAME_OF_THE_APP

To recreate the database:

$ heroku run rake db:migrate

To seed the database:

$ heroku run rake db:seed

**Final step

$ heroku restart

edited Mar 24 '12 at 15:28

Jonathan

10,936
8
64
79

answered Nov 30 '11 at 11:50

George Yacoub

1,436
1
11
20

4

I prefer this answer over any else's. This works perfectly in my scenario on Heroku, I believe it is highly under voted! – Jonathan Mar 24 '12 at 16:04
It might be because heroku changed the syntax of these commands recently. – George Yacoub Mar 24 '12 at 19:49
1

'heroku restart' was the one key piece I was missing. Thanks. – Jeremy Nikolai Jul 02 '14 at 15:00
`heroku run rake db:migrate` no longer recreates the database – Rots Sep 30 '14 at 09:16

superluminary · Answer 4 · 2017-07-24T11:26:11.420

48

The current, ie. 2017 way to do this is:

heroku pg:reset DATABASE

https://devcenter.heroku.com/articles/heroku-postgresql#pg-reset

edited Jul 24 '17 at 11:26

answered Feb 01 '13 at 18:47

superluminary

47,086
25
151
148

1

Agreed. The current way to re-populate the database is: `heroku run rake db:migrate db:seed` – aaron-coding Dec 05 '14 at 00:33

score 14 · Answer 5 · answered Aug 26 '13 at 18:04

14

Now the command is

heroku pg:reset DATABASE_URL --confirm your_app_name

this way you can specify which app's db you want to reset. Then you can run

heroku run rake db:migrate 
heroku run rake db:seed

or direct for both above commands

heroku run rake db:setup

And now final step to restart your app

heroku restart

answered Aug 26 '13 at 18:04

Syed Ehtsham Abbas

502
7
11

1

Restarting might not be necessary but good point giving it a mention. – Francisco Quintero Oct 21 '18 at 20:04

score 11 · Answer 6 · answered Jun 09 '12 at 03:11

I contacted Heroku support, and they confirmed that it is a bug with the latest gem (I am using heroku-2.26.2)

Charlie - we are aware of this issue with the 'heroku' gem and are working to fix it.

Here's the issue if you care to follow-along - https://github.com/heroku/heroku/issues/356

Downgrading to an earlier version of the 'heroku' gem should help. I've been using v2.25.0 for most of today without issue.

Downgrade with the following commands:

gem uninstall heroku

gem install heroku --version 2.25.0

If you already have multiple gems installed, you may be presented with:

Select gem to uninstall:
 1. heroku-2.25.0
 2. heroku-2.26.2
 3. All versions

Just uninstall #2 and rerun the command. Joy!

All the gems have been deprecated now, you need to get rid of them and install the Heroku toolbelt. https://toolbelt.heroku.com/ — Ghoti, Sep 09 '13 at 21:52

score 9 · Answer 7 · answered Nov 26 '13 at 19:48

The complete answer is (for users with multi-db):

heroku pg:info - which outputs

=== HEROKU_POSTGRESQL_RED <-- this is DB
Plan Basic
Status available

heroku pg:reset HEROKU_POSTGRESQL_RED --confirm app_name

More information found in: https://devcenter.heroku.com/articles/heroku-postgresql

score 8 · Answer 8 · answered Sep 10 '12 at 01:13

8

Now it's diffrent with heroku. Try: heroku pg:reset DATABASE --confirm

answered Sep 10 '12 at 01:13

jstnno

1,035
1
11
13

For some apps you still need to use SHARED_DATABASE – tfwright Oct 11 '12 at 15:29

score 8 · Answer 9 · answered Aug 17 '15 at 18:57

8

Now it's also possible to reset the database through their web interface.

Go to dashboard.heroku.com select your app and then you'll find the database under the add-ons category, click on it and then you can reset the database.

answered Aug 17 '15 at 18:57

Luís Ramalho

10,018
4
52
67

score 7 · Answer 10 · answered Aug 19 '11 at 16:51

7

Today the command

heroku pg:reset --db SHARED_DATABASE_URL

not working for shared plans, I'm resolve using

heroku pg:reset SHARED_DATABASE

answered Aug 19 '11 at 16:51

mmarqueti

257
1
5
8

score 6 · Answer 11 · answered Oct 03 '12 at 21:40

Check your heroku version. I just updated mine to 2.29.0, as follows:

heroku --version
#=> heroku-gem/2.29.0 (x86_64-linux) ruby/1.9.3

Now you can run:

heroku pg:reset DATABASE --confirm YOUR_APP_NAME

Then create your database and seed it in a single command:

heroku run rake db:setup

Now restart and try your app:

heroku restart
heroku open

score 6 · Answer 12 · answered Jan 06 '13 at 15:53

6

Login to your DB using heroku pg:psql and type the following commands:

drop schema public cascade;
create schema public;

answered Jan 06 '13 at 15:53

Manish Singh

5,848
4
43
31

score 5 · Answer 13 · answered May 12 '16 at 10:51

5

In case you prefer to use Heroku Web-site:

Go to https://postgres.heroku.com/databases
Select the database you want to reset
Click on a settings button in the right upper corner
Click "Reset Database" as shown below:
type in "RESET" and press ok

answered May 12 '16 at 10:51

Igor Kovryzhkin

2,195
1
27
22

score 4 · Answer 14 · answered May 28 '17 at 20:12

This is what worked for me.

1.clear db.

heroku pg:reset --app YOUR_APP

After running that you will have to type in your app name again to confirm.

2.migrate db to recreate.

heroku run rake db:migrate  --app YOUR_APP

3.add seed data to db.

heroku run rake db:seed --app YOUR_APP

score 3 · Answer 15 · answered Jun 29 '14 at 02:03

Assuming you want to reset your PostgreSQL database and set it back up, use:

heroku apps

to list your applications on Heroku. Find the name of your current application (application_name). Then run

heroku config | grep POSTGRESQL

to get the name of your databases. An example could be

HEROKU_POSTGRESQL_WHITE_URL

Finally, given application_name and database_url, you should run

heroku pg:reset `database_url` --confirm `application_name`
heroku run rake db:migrate
heroku restart

score 1 · Answer 16 · answered Jun 22 '17 at 20:22

1

If you are logged in from the console, this will do the job in the latest heroku toolbelt,

heroku pg:reset --confirm database-name

answered Jun 22 '17 at 20:22

Charles Skariah

670
6
18

Mark Locklear · Answer 17 · 2013-06-18T15:22:36.133

0

I always do this with the one-liner 'heroku pg:reset DATABASE'.

edited Jun 18 '13 at 15:22

answered Aug 05 '12 at 19:40

Mark Locklear

5,044
1
51
81

score 0 · Answer 18 · answered Feb 03 '17 at 07:17

Best solution for you issue will be

heroku pg:reset -r heroku  --confirm your_heroku_app_name

--confirm your_heroku_app_name

is not required, but terminal always ask me do that command.

After that command you will be have pure db, without structure and stuff, after that you can run

heroku run rake db:schema:load -r heroku

or

heroku run rake db:migrate -r heroku

How to empty a Heroku database

18 Answers18

Linked

Related