46

Possible Duplicate:
How to empty DB in heroku

I have a Postgres database on Heroku. It is one of the free beta ones. Locally, when testing, I often run rake db:drop && rake db:create && rake db:migrate as a way to reset the database.

However, when I try to run this on Heroku, I get the error:

Couldn't drop mydatabaseid : #<PG::Error: FATAL:  permission denied for database "postgres"
  DETAIL:  User does not have CONNECT privilege.

Uh, ok, so how am I supposed to completely reset my database, migrations and everything?

Community
  • 1
  • 1
Geoff
  • 9,470
  • 13
  • 52
  • 67

1 Answers1

90

The pg:reset command will recreate the database for you. Example usage:

$ heroku config | grep POSTGRESQL
HEROKU_POSTGRESQL_RED_URL: postgres://somedatabaseurl
$ heroku pg:reset HEROKU_POSTGRESQL_RED_URL
!    WARNING: Destructive Action
!    This command will affect the app: myappname
!    To proceed, type "myappname" or re-run this command with --confirm
> myappname
Resetting HEROKU_POSTGRESQL_RED_URL (DATABASE_URL)... done

The db:reset command would try to drop the database, which is not something that Heroku's permissions allow.

metakermit
  • 21,267
  • 15
  • 86
  • 95
Neil Middleton
  • 22,105
  • 18
  • 80
  • 134
  • Great!... where'd you find that? I read their docs here: https://devcenter.heroku.com/articles/heroku-postgresql – Geoff Jun 12 '12 at 16:24
  • 4
    In my brain somewhere - no idea where. – Neil Middleton Jun 13 '12 at 15:10
  • 3
    Here is Heroku documentation: https://devcenter.heroku.com/articles/rake – weston Oct 03 '12 at 23:12
  • 64
    Just to clarify for anyone who skipped over the above link, you want to run **`heroku pg:reset`** at the command line, not `heroku run rake pg:reset` or anything like that. – GMA Jul 30 '13 at 02:37
  • 6
    Just a small update: in order to reset `postgres` database, you are required to enter `name` of database. –  Aug 21 '13 at 14:35
  • 32
    This worked for me: `heroku config | grep POSTGRESQL` - getting the name of database e.g. `HEROKU_POSTGRESQL_BLUE_URL` and then running `heroku pg:reset HEROKU_POSTGRESQL_BLUE_URL` –  Aug 21 '13 at 14:36
  • 2
    Heroku asks you to confirm. For one line resetting try heroku pg:reset HEROKU_POSTGRESQL_COPPER_URL --confirm – Nick Barrett Feb 14 '14 at 08:30
  • 2
    Note that after you do the above, you will have to do `heroku run rake db:migrate`. – Nubtacular Apr 16 '14 at 06:22
  • 1
    `heroku run rake pg:drop` doesn't work for me. – ahnbizcad Apr 29 '14 at 08:14
  • 1
    For new apps, the db is already created by heroku and you don't need to run db:setup, just run db:migrate – Ali Jan 14 '16 at 02:33
  • 5
    When I tried the above options using database name I got an error saying that `Valid options are: DATABASE_URL`. So I entered `heroku pg:reset DATABASE_URL` and it worked just fine. – B. Bulpett Jan 18 '16 at 03:18
  • don't forget --app as some implementations of the CLI require that: `heroku config --app | grep POSTGRESQL` – SMAG Feb 21 '19 at 22:36