How to handle psycopg2 undefined table error when migrating postgres db to heroku?

Question

I have reviewed at least 20 stackover flow answers, plus lots of blog posts, and I am at a complete loss for how to migrate my local postgres db to heroku for a simple flask app. I have been following along with several different tutorials and everything works locally. I can push the app to production on heroku and I don't get any errors during that process. Specifically, I am running the following in the terminal in succession:

python manage.py db init
python manage.py db migrate
python manage.py db upgrade
heroku create hud-mfi-api
git remote add prod https://git.heroku.com/hud-mfi-api.git
heroku config:set APP_SETTINGS=config.ProductionConfig --remote prod
heroku addons:create heroku-postgresql:hobby-dev --app hud-mfi-api
git push prod master
heroku run python manage.py db upgrade --app hud-mfi-api

When I then go to the app, I get psycopg2.errors.UndefinedTable. Included in the error is a link to the SQLAlchemy documentation but I reviewed it extensively and I didn't find anything specific regarding UndefinedTable after migrating. I have very little experience in the Flask and SQL world and I am trying to learn by going through various tutorials.

One hypothesis I have is that I already have a database populated with values and in all of the tutorials they are setting up an empty database. I don't know why this would be an issue though if everything is working correctly on my local version (i.e. python manage.py runserver)

Once I then deploy to heroku and try to run the same get request I get this response

I apologize in advance as I know questions like this are quite common. I tried to include as much relevant information as possible but at this point I am quite frustrated with the process. I've spent time going through the documentation but at this point I think there is some issue on the SQL side but I can't parse it. Thanks in advance SO

Here is the code for my app.py

import os

from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config.from_object(os.environ['APP_SETTINGS'])
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


from models import MFI


@app.route("/getall")
def get_all():
    try:
        median_incomes = MFI.query.all()
        return jsonify([e.serialize() for e in median_incomes])
    except Exception as e:
        return(str(e))


@app.route("/get/<geoid_>")
def get_by_geoid(geoid_):
    try:
        median_income = MFI.query.filter_by(geoid=geoid_).first()
        return jsonify(median_income.serialize())
    except Exception as e:
        return(str(e))


if __name__ == '__main__':
    app.run(debug=True)

Here is the code for my manage.py file

from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand

from app import app, db

migrate = Migrate(app, db)
manager = Manager(app)

manager.add_command('db', MigrateCommand)


if __name__ == '__main__':
    manager.run()

Here is my congig.py script

import os
basedir = os.path.abspath(os.path.dirname(__file__))

class Config(object):
    DEBUG = False
    TESTING = False
    CSRF_ENABLED = True
    SECRET_KEY = 'this-really-needs-to-be-changed'
    SQLALCHEMY_DATABASE_URI = os.environ['DATABASE_URL']


class ProductionConfig(Config):
    DEBUG = False


class StagingConfig(Config):
    DEVELOPMENT = True
    DEBUG = True


class DevelopmentConfig(Config):
    DEVELOPMENT = True
    DEBUG = True


class TestingConfig(Config):
    TESTING = True

"I am at a complete loss for how to migrate my local postgres db to heroku"—typically you _wouldn't_ do this. You'd run the database migrations present in your codebase to create the _schema_ you need on Heroku. Your local database and your remote one probably shouldn't contain the same data. If you do want to copy data (e.g. as a one time task when you first set this up) you could, as a separate operation, dump your local data and then load it on Heroku. But again, normally your data should be separate. It's program state. Expecting it to be identical everywhere will cause problems. — ChrisGPT was on strike, Feb 02 '20 at 23:24
^^Thanks for the response! Do you have any recommendations for resources I could use to do this to figure out a more appropriate way to do this task? Essentially, I downloaded a series of csv files from a government data provider that doesn't offer an API. I have a python script that cleans and formats the data before writing it to postgres. My goal, is to set up an API so that I can access this data set. I'm not trying to build a full CRUD app but rather just allow for GET requests. What should I be searching for to figure out how to tackle this problem in a best practice? — buchmayne, Feb 03 '20 at 02:16
Like I said, run your migrations on Heroku to get the schema. In this case it might actually make sense to dump your local data and load it remotely, or if your ingesion script is deployed you could run it directly on Heroku. — ChrisGPT was on strike, Feb 04 '20 at 16:49

How to handle psycopg2 undefined table error when migrating postgres db to heroku?

0 Answers0