Issue Migrating DB to Heroku - PG::UndefinedTable: ERROR: relation "users" does not exist

Question

For starters I am using two different dbs, sqlite in development and postgresql in production.

I think the issue is related to forgetting to constantly migrate to heroku after changes to my db. I probably did four or five migrations locally before migrating on heroku. So anyways, the issue is that when i tun heroku run rails db:migrate I get an error saying PG::UndefinedTable: ERROR: relation "users" does not exist although in development it definitely does. I'll try and attach all the necessary info below. Let me know if anything is missing.

schema.rb

# This file is auto-generated from the current state of the database. Instead
# of editing this file, please use the migrations feature of Active Record to
# incrementally modify your database, and then regenerate this schema definition.
#
# Note that this schema.rb definition is the authoritative source for your
# database schema. If you need to create the application database on another
# system, you should be using db:schema:load, not running all the migrations
# from scratch. The latter is a flawed and unsustainable approach (the more migrations
# you'll amass, the slower it'll run and the greater likelihood for issues).
#
# It's strongly recommended that you check this file into your version control system.

ActiveRecord::Schema.define(version: 20161011053831) do

  create_table "comments", force: :cascade do |t|
    t.integer  "user_id"
    t.text     "body"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.integer  "post_id"
    t.index ["post_id"], name: "index_comments_on_post_id"
    t.index ["user_id"], name: "index_comments_on_user_id"
  end

  create_table "posts", force: :cascade do |t|
    t.string   "title"
    t.text     "body"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "users", force: :cascade do |t|
    t.string   "first_name"
    t.string   "last_name"
    t.datetime "created_at",                          null: false
    t.datetime "updated_at",                          null: false
    t.string   "email",                  default: "", null: false
    t.string   "encrypted_password",     default: "", null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,  null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
  end

end

The terminal output

Running rails db:migrate on ⬢ [retracted]... up, run.1231 (Free)
D, [2016-10-11T06:15:07.562673 #3] DEBUG -- :    (0.8ms)  SELECT pg_try_advisory_lock(6976026441189414000);
D, [2016-10-11T06:15:07.572811 #3] DEBUG -- :   ActiveRecord::SchemaMigration Load (1.0ms)  SELECT "schema_migrations".* FROM "schema_migrations"
I, [2016-10-11T06:15:07.577534 #3]  INFO -- : Migrating to CreateComments (20160928024026)
D, [2016-10-11T06:15:07.579481 #3] DEBUG -- :    (0.7ms)  BEGIN
== 20160928024026 CreateComments: migrating ===================================
-- create_table(:comments)
D, [2016-10-11T06:15:07.594892 #3] DEBUG -- :    (13.9ms)  CREATE TABLE "comments" ("id" serial primary key, "user_id" integer, "body" text, "blog_post_id" integer, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_03de2dc08c"
FOREIGN KEY ("user_id")
  REFERENCES "users" ("id")
, CONSTRAINT "fk_rails_29c545254e"
FOREIGN KEY ("blog_post_id")
  REFERENCES "blog_posts" ("id")
)
D, [2016-10-11T06:15:07.595917 #3] DEBUG -- :    (0.7ms)  ROLLBACK
D, [2016-10-11T06:15:07.597131 #3] DEBUG -- :    (0.9ms)  SELECT pg_advisory_unlock(6976026441189414000)
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR:  relation "users" does not exist
: CREATE TABLE "comments" ("id" serial primary key, "user_id" integer, "body" text, "blog_post_id" integer, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_03de2dc08c"
FOREIGN KEY ("user_id")
  REFERENCES "users" ("id")
, CONSTRAINT "fk_rails_29c545254e"
FOREIGN KEY ("blog_post_id")
  REFERENCES "blog_posts" ("id")
)
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/app/vendor/bundle/ruby/2.2.0/gems/activesupport-5.0.0.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:283:in `create_table'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:845:in `block in method_missing'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `block in say_with_time'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `say_with_time'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:834:in `method_missing'
/app/db/migrate/20160928024026_create_comments.rb:3:in `change'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:788:in `exec_migration'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:772:in `block (2 levels) in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:771:in `block in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:770:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:950:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1211:in `block in execute_migration_in_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `block in ddl_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/transactions.rb:211:in `transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `ddl_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1210:in `execute_migration_in_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1183:in `block in migrate_without_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `each'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `migrate_without_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `block in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1298:in `with_advisory_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1005:in `up'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:983:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/rake_proxy.rb:13:in `block in run_rake_task'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/rake_proxy.rb:10:in `run_rake_task'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/commands_tasks.rb:51:in `run_command!'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands.rb:18:in `<top (required)>'
/app/bin/rails:9:in `require'
/app/bin/rails:9:in `<main>'
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "users" does not exist
: CREATE TABLE "comments" ("id" serial primary key, "user_id" integer, "body" text, "blog_post_id" integer, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_03de2dc08c"
FOREIGN KEY ("user_id")
  REFERENCES "users" ("id")
, CONSTRAINT "fk_rails_29c545254e"
FOREIGN KEY ("blog_post_id")
  REFERENCES "blog_posts" ("id")
)
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/app/vendor/bundle/ruby/2.2.0/gems/activesupport-5.0.0.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:283:in `create_table'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:845:in `block in method_missing'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `block in say_with_time'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `say_with_time'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:834:in `method_missing'
/app/db/migrate/20160928024026_create_comments.rb:3:in `change'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:788:in `exec_migration'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:772:in `block (2 levels) in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:771:in `block in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:770:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:950:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1211:in `block in execute_migration_in_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `block in ddl_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/transactions.rb:211:in `transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `ddl_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1210:in `execute_migration_in_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1183:in `block in migrate_without_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `each'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `migrate_without_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `block in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1298:in `with_advisory_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1005:in `up'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:983:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/rake_proxy.rb:13:in `block in run_rake_task'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/rake_proxy.rb:10:in `run_rake_task'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/commands_tasks.rb:51:in `run_command!'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands.rb:18:in `<top (required)>'
/app/bin/rails:9:in `require'
/app/bin/rails:9:in `<main>'
PG::UndefinedTable: ERROR:  relation "users" does not exist
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/app/vendor/bundle/ruby/2.2.0/gems/activesupport-5.0.0.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:283:in `create_table'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:845:in `block in method_missing'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `block in say_with_time'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:814:in `say_with_time'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:834:in `method_missing'
/app/db/migrate/20160928024026_create_comments.rb:3:in `change'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:788:in `exec_migration'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:772:in `block (2 levels) in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:771:in `block in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:770:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:950:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1211:in `block in execute_migration_in_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `block in ddl_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/transactions.rb:211:in `transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1279:in `ddl_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1210:in `execute_migration_in_transaction'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1183:in `block in migrate_without_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `each'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1182:in `migrate_without_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `block in migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1298:in `with_advisory_lock'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1131:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:1005:in `up'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/migration.rb:983:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-5.0.0.1/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/rake_proxy.rb:13:in `block in run_rake_task'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/rake_proxy.rb:10:in `run_rake_task'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands/commands_tasks.rb:51:in `run_command!'
/app/vendor/bundle/ruby/2.2.0/gems/railties-5.0.0.1/lib/rails/commands.rb:18:in `<top (required)>'
/app/bin/rails:9:in `require'
/app/bin/rails:9:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

I've tried a few other solutions floating around but nothing so far has worked. Thanks!

Answer 1

It looks like you are creating comments table before users, and while using user_id foreign key in comments table it fails, because there is no such table yet.

Make sure your migrations ordered correctly (users, then comments).

And generally, you should always first create tables/columns, and only after they are created and migrated, reference them.