PG::UndefinedTable: ERROR: relation "" does not exist - Amazon EC2, Postgresql, and Rails 5

Question

There are quite a few of these question in stackoverflow none of which have been able to help me. I am launching an amazon ec2 using rails and postgresql. I have it launched but keep getting the following error (I have done everything including rake db:migrate even everything here https://stackoverflow.com/a/19804714/7039895):

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  relation "campaigns" does not exist
LINE 8:                WHERE a.attrelid = '"campaigns"'::regclass
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
             (SELECT c.collname FROM pg_collation c, pg_type t
               WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation),
                     col_description(a.attrelid, a.attnum) AS comment
                FROM pg_attribute a LEFT JOIN pg_attrdef d
                  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
               WHERE a.attrelid = '"campaigns"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum
):

app/models/campaign.rb:15:in `block in <class:Campaign>'
app/controllers/creatives_controller.rb:30:in `storage'
app/controllers/creatives_controller.rb:5:in `index'

Here are the files from the application trace:

campaign.rb(model)

class Campaign < ApplicationRecord
   acts_as_taggable # Alias for acts_as_taggable_on :tags

     extend FriendlyId
  friendly_id :title, use: :slugged

  belongs_to :author

  has_attached_file :image, styles: { large: "600X600", medium: "300x300>", thumb: "150x150#" }, default_url: "/images/:style/missing.png"
  validates_attachment_content_type :image, content_type: /\Aimage\/.*\z/

  PER_PAGE = 3

  scope :most_recent, -> {order(published_at: :desc)}
  scope :published, -> { where(published: true) }
  scope :recent_paginated, -> (page) { most_recent.paginate(page: page, per_page: PER_PAGE) }
  scope :with_tag, -> (tag) { tagged_with(tag) if tag.present? }

  scope :list_for, -> (page, tag) do
    recent_paginated(page).with_tag(tag)
  end

  def display_day_published
    if published_at.present?
    "Published #{published_at.strftime('%-b %-d, %Y')}"
    else
      "Not published yet."
    end
  end

  def publish
    update(published: true, published_at: Time.now)
  end

  def unpublish
    update(published: false, published_at: nil)

  end
end

creatives_controller.rb (has both index and storage)

class CreativesController < ApplicationController
  layout "creative"

  def index
    @campaigns = storage.list_for(params[:page], params[:tag])
    @campaigned = Campaign.order("created_at DESC").limit(6)
    @recent = Campaign.order("created_at DESC").limit(3)

    end

    # GET /posts/1
    # GET /posts/1.json
  def show

    @campaign = storage.friendly.find(params[:id])
    @gallery = storage.friendly.find(params[:id])
  end

  def gallery
   @galleries = storage.list_for(params[:page], params[:tag])
    @galleried = Gallery.order("created_at DESC")
    @current = Gallery.order("created_at DESC")

  end


  private

  def storage
    Campaign.published
    Gallery.published

  end

end

When I run this locally it works fine. It is when I run it in my amazon ec2 instance that it keeps giving me this error. Any help would be greatly appreciated.

Also, here is the code in the index.html.erb file:

<!-- Portfolio Box -->
<ul class="list-unstyled row portfolio-box">
<% @recent.each do |campaign| %>
    <li class="col-sm-4">
        <%= link_to campaign.image.url, :class => "thumbnail fancybox", data: { rel: "gallery" }, title: campaign.title  do %>
         <%= image_tag campaign.image, :class => "full-width img-responsive" %>
         <span class="rounded-x portfolio-box-in">
           <i class="fa fa-search-plus"></i>
         </span>
        <% end %>
        <div class="headline-left margin-bottom-10"><h3 class="headline-brd"><%= campaign.title %></h3></div>
        <small class="project-tag"><i class="fa fa-tags"></i><%= raw campaign.tags.map{ |t| link_to t.name, campaigns_path(tag: t.name)}.join(', ') %></small>
        <p><%= truncate(campaign.description, length: 130) %> </p>
    </li>
    <%end%>
</ul>
<!-- End Portfolio Box -->

That SQL is what ActiveRecord uses to figure out the structure of the `campaigns` table. The error is telling you that the database that AR is connecting to doesn't have a `campaigns` table. Presumably you're missing a migration that would create that table or you're connecting to the wrong database. — mu is too short, Aug 27 '17 at 01:36
Are you checking that by connecting with the same credentials that Rails is, and when you do can you select from the table? — David Aldridge, Aug 27 '17 at 09:06
And when you check, how specifically are you checking? Something like `select '"campaigns"'::regclass` should take care of any case issues. — mu is too short, Aug 27 '17 at 17:16
You were right about I was not on the right database will explain — Jermaine Subia, Aug 27 '17 at 18:14

score 0 · Accepted Answer · answered Aug 27 '17 at 19:31

Figured it out. Thanks to @mu is too short I assumed I was connected to the the correct database. When I looked in the postgressql terminal:

postgres=# \dt;
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | campaigns | table | postgres
(1 row)

It would show me this so I assumed I was connected but somehow I created a campaign database in the postgres database and not the "martin_development" database where I needed it to be created. When I connected to the "martin_development" database I found that the campaigns table was not there which caused this error. The other issue was my migrations, it turns out that I created the campaigns table in razorsql and not through rails so no migrations were created to run with rake:db migrate. I had to rails g model campaign... in rails to create the migration. Once I ran rake:db migrate the page worked again and the error disappeared.

Learned two lessons

Double check I am on the right database.
Create tables inside of rails not through an outside database ui as the migration will not be created.

PG::UndefinedTable: ERROR: relation "" does not exist - Amazon EC2, Postgresql, and Rails 5

1 Answers1