Heroku/Rails: PG:: Undefined Table: error "[tablename]" does not exist on heroku rails migration

Question

My app works fine in production, but when I move to production and run 'heroku run rake db:migrate', I get the following error:

    PG::UndefinedTable: ERROR:  table "applications" does not exist
    Migrating to DropApplications (20160509013805)
        (0.8ms)  BEGIN
    == 20160509013805 DropApplications: migrating             =================================
    -- drop_table(:applications)
       (1.1ms)  DROP TABLE "applications"
        (0.5ms)  ROLLBACK
    rake aborted!
     StandardError: An error has occurred, this and all later migrations    canceled:

     PG::UndefinedTable: ERROR:  table "applications" does not exist
   : DROP TABLE "applications"

However, there IS an 'applications' table in my database. The drop perhaps has something to do with when I dropped and then remade the applications scaffold the other day. How do I fix this?

      create_table "applications", force: :cascade do |t|
t.string   "name"
t.string   "gender"
t.date     "date_of_birth"
t.string   "gpa"
t.text     "essay"
t.datetime "created_at",    null: false
t.datetime "updated_at",    null: false
      end

So when I run run rake:db setup, I see the following results:

      Status   Migration ID    Migration Name
    --------------------------------------------------
       up     20160505200754  ********** NO FILE **********
       up     20160508234634  Create users
      up     20160508234945  Add devise to users
       up     20160509013805  ********** NO FILE **********
       up     20160509014328  ********** NO FILE **********
        20160509014911  Create applications

The two before "create applications" I attempted to delete. So, I try to rake the heroku db like so: $ heroku run rake --trace db:migrate VERSION=20151127134901 but I still get the error with the "drop tables"- its trying to do the 20160509013805 migrations. How do I delete those migrations completely where it says no file so it doesn't try to rake those too? Thank you in advance.

That helps and puts me on the right track... I think. I just updated the original post take a look if you can — zasman, May 11 '16 at 12:40

score 5 · Accepted Answer · answered May 10 '16 at 02:36

5

There might be a problem in your migration files. If you deleted one but another corresponding file remains, it can cause this error. I would have commented but I don't have the reputation yet.

answered May 10 '16 at 02:36

Yimanei

242
2
8

1

Those are orphaned migrations. To solve it, you can delete your `db/migrate/` folder (don't delete schema.rb) and run `rake db:schema:load` or `rake db:setup`. Alternatively there is a gem but I never tried it : https://github.com/jalkoby/squasher You can also check this SO question: http://stackoverflow.com/questions/16041790/what-is-the-best-way-to-resolve-rails-orphaned-migrations – Yimanei May 11 '16 at 14:24
Actually your orphaned migrations issue is now solved. But you don't have a user with privileges to create a db. PG isn't correctly set up and can't proceed. It's a common problem with different solutions depending on your environment, you might have to provide your username and password if required (hide it in environment variables) to `database.yml`. See http://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist?rq=1 and http://stackoverflow.com/questions/17633422/psql-fatal-database-user-does-not-exist?rq=1 for solution examples. – Yimanei May 12 '16 at 02:40
Ok, so I reinstalled postgresl, but it still says that role doesn't exist. How do I create a new role? And then if I create a new role on my computer do I then put that on my database.yml file? – zasman May 12 '16 at 03:36
You should have a "username" line for your databases in `database.yml`. As for creating a user, it depends on whether you are on Mac, Windows... I encourage you browsing through SO, there are solid answers. Setting up pg on your machine is an important task, and it's likely you'll have to ajust some more config as well. – Yimanei May 12 '16 at 03:56
Ok, I changed the postgresql password using the links you followed and now I'm back to square one with the trouble pushing to heroku (saying the deleted migrations don't exist), which is better than before when I couldn't even run on production. I'll keep working. Thanks! – zasman May 12 '16 at 13:41
I ran heroku run rake:schema:load and then heroku run rake:db:migrate and it looks like it successfully worked. Thank you! – zasman May 12 '16 at 13:42

score 2 · Answer 2 · answered May 11 '16 at 20:24

Resetting heroku db:

Heroku doesn't allow to drop db. Instead you should be doing heroku pg:reset. This commands needs you to specify the DATABASE which can be found by heroku pg:info, then you will get an output like below:

=== DATABASE_URL
Plan:        Hobby-dev
Status:      Available
Connections: 0/20
PG Version:  9.4.4
Created:     2015-10-11 23:14 UTC
Data Size:   6.7 MB
Tables:      5
Rows:        0/10000 (In compliance)
Fork/Follow: Unsupported
Rollback:    Unsupported
Add-on:      postgresql-cubed-1838

Once you get the output like above use the heroku pg:reset command with appending the "Add-on" field value. Example I would be doing this:

$ heroku pg:reset postgresql-cubed-1838

score 0 · Answer 3 · edited Apr 25 '20 at 09:42

0

I had this issue but all I had to do was run heroku run rake db:migrate because I had changed my database since the last time I pushed to Heroku.

edited Apr 25 '20 at 09:42

David Buck

3,752
35
31
35

answered Apr 25 '20 at 09:31

Tom Haywood

21
1

Heroku/Rails: PG:: Undefined Table: error "[tablename]" does not exist on heroku rails migration

3 Answers3