Question

use dockerfile and Docker Compose deploy app in debian droplet, however, PostgreSQL was not found

Posted on December 30, 2023
Debian 11 Deployment DigitalOcean Droplets PostgreSQL Python
Asked by yyz20002008

Hello I followed this instracution # Python CRUD Rest API using Flask, SQLAlchemy, Postgres, Docker, Docker Compose for Docker command and tried to use Dockerfile and Docker Compose file deploy app in debian droplet not app platform because of cost consideration. however, it is succesful in local but errored in cloud. I believe it is becuase the PostgreSQL database was not found. Do I need to change URL in docker file? what’s URL I should use after deployment. I thought I can use droplet’s ip address <=> localhost? Please refer files and errors below. Thanks so much. docker compose file:

version: "3.9"

services:
  flask_app:
    container_name: flask_app
    image: dockerhub-flask_live_app:1.0.0
    build: .
    ports:
      - "5000:5000"
    environment:
      - DB_URL=postgresql://postgres:postgres123456@flask_db:5432/postgres
    depends_on:
      - flask_db
  flask_db:
    container_name: flask_db
    image: postgres:16
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=postgres123456
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata: {}

docker file：

# Use an official Python runtime as a parent image
FROM python:3.9-slim

# Set environment variables for Python
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1
# set working directory
WORKDIR /app

# Install system dependencies
RUN apt-get update && apt-get install -y --no-install-recommends \
    unzip \
    wget \
    gnupg2 \
    ca-certificates \
    apt-transport-https \
    software-properties-common
# Add Google Chrome to the repositories
RUN wget -q -O - https://dl.google.com/linux/linux_signing_key.pub | apt-key add - \
    && sh -c 'echo "deb [arch=amd64] http://dl.google.com/linux/chrome/deb/ stable main" >> /etc/apt/sources.list.d/google-chrome.list'

# Install Google Chrome
RUN DEBIAN_FRONTEND=noninteractive
RUN apt-get -f install
RUN apt-get update && apt-get install -y --no-install-recommends google-chrome-stable

#OR
#wget https://dl.google.com/linux/direct/google-chrome-stable_current_amd64.deb
#sudo dpkg -i google-chrome-stable_current_amd64.deb
#sudo apt-get -f install -y

RUN google-chrome --version
RUN wget -q --continue -P /chromedriver "https://edgedl.me.gvt1.com/edgedl/chrome/chrome-for-testing/120.0.6099.71/linux64/chromedriver-linux64.zip" \
     && unzip /chromedriver/chromedriver* -d /usr/local/bin/


# Move and adjust permissions for chromedrive
# RUN wget https://edgedl.me.gvt1.com/edgedl/chrome/chrome-for-testing/119.0.6045.105/linux64/chromedriver-linux64.zip
# RUN && unzip chromedriver-linux64.zip
# RUN && mv chromedriver-linux64/chromedriver /usr/bin
# RUN && chown root:root /usr/bin/chromedriver
# RUN && chmod +x /usr/bin/chromedriver

# Install Python dependencies
COPY requirements.txt /app/
#RUN apt-get install python3-pip
RUN pip install --upgrade pip
RUN pip install --no-cache-dir -r requirements.txt

# Copy the current directory contents into the container at /app
COPY . /app/

EXPOSE 5000

ENV FLASK_APP=app.py

#CMD [ "python3", "-m" , "flask", "run", "--host=0.0.0.0"]
CMD ["gunicorn"  , "-b", "0.0.0.0:5000", "app:app"]

app.py

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:postgres123456@flask_db:5432/postgres'

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

from models import FlightDB,db
db.init_app(app)

models.py:

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import func
db = SQLAlchemy()
class FlightDB(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    DRP_DATETIME =  db.Column(db.Text)
    ARR_DATETIME =  db.Column(db.Text)
    DEP_AIRPORT =   db.Column(db.Text)
    ARR_AIRPORT =   db.Column(db.Text)
    AIRLINE =       db.Column(db.Text)
    FLIGHTNUMBER =  db.Column(db.Text)
    PRICE =         db.Column(db.Text)
    LINK =          db.Column(db.Text)
    CREATEDDATE =   db.Column(db.DateTime(timezone=True),
                           server_default=func.now())
    #def __init__(self, DRP_DATETIME, ARR_DATETIME,ARR_AIRPORT, DEP_AIRPORT, AIRLINE,FLIGHTNUMBER,PRICE,LINK,CREATEDDATE):
    def __repr__(self):
        return f'<Flight {self.AIRLINE+self.FLIGHTNUMBER}>'

error message(I don’t have “flight_db” in the code, it may come from some translation in sqlalchemy):

root@debian-s-1vcpu-512mb-10gb-nyc1-01:~/flight# docker compose up flask_app
[+] Running 2/0
 ✔ Container flask_db   Running                                                                                                      0.0s
 ✔ Container flask_app  Created                                                                                                      0.0s
Attaching to flask_app
flask_app  | [2023-12-30 21:04:38 +0000] [1] [INFO] Starting gunicorn 21.2.0
flask_app  | [2023-12-30 21:04:38 +0000] [1] [INFO] Listening at: http://0.0.0.0:5000 (1)
flask_app  | [2023-12-30 21:04:38 +0000] [1] [INFO] Using worker: sync
flask_app  | [2023-12-30 21:04:38 +0000] [6] [INFO] Booting worker with pid: 6
flask_app  | [2023-12-30 21:08:21,445] ERROR in app: Exception on / [GET]
flask_app  | Traceback (most recent call last):
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
flask_app  |     self.dialect.do_execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
flask_app  |     cursor.execute(statement, parameters)
flask_app  | psycopg2.errors.UndefinedTable: relation "flight_db" does not exist
flask_app  | LINE 2: FROM flight_db ORDER BY flight_db."CREATEDDATE" DESC
flask_app  |              ^
flask_app  |
flask_app  |
flask_app  | The above exception was the direct cause of the following exception:
flask_app  |
flask_app  | Traceback (most recent call last):
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1455, in wsgi_app
flask_app  |     response = self.full_dispatch_request()
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 869, in full_dispatch_request
flask_app  |     rv = self.handle_user_exception(e)
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask_cors/extension.py", line 176, in wrapped_function
flask_app  |     return cors_after_request(app.make_response(f(*args, **kwargs)))
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 867, in full_dispatch_request
flask_app  |     rv = self.dispatch_request()
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 852, in dispatch_request
flask_app  |     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
flask_app  |   File "/app/app.py", line 258, in index
flask_app  |     posts = FlightDB.query.order_by(FlightDB.CREATEDDATE.desc()).all()
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2693, in all
flask_app  |     return self._iter().all()  # type: ignore
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2847, in _iter
flask_app  |     result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
flask_app  |     return self._execute_internal(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal
flask_app  |     result: Result[Any] = compile_state_cls.orm_execute_statement(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
flask_app  |     result = conn.execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
flask_app  |     return meth(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
flask_app  |     return connection._execute_clauseelement(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
flask_app  |     ret = self._execute_context(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
flask_app  |     return self._exec_single_context(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
flask_app  |     self._handle_dbapi_exception(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
flask_app  |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
flask_app  |     self.dialect.do_execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
flask_app  |     cursor.execute(statement, parameters)
flask_app  | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "flight_db" does not exist
flask_app  | LINE 2: FROM flight_db ORDER BY flight_db."CREATEDDATE" DESC
flask_app  |              ^
flask_app  |
flask_app  | [SQL: SELECT flight_db.id AS flight_db_id, flight_db."DRP_DATETIME" AS "flight_db_DRP_DATETIME", flight_db."ARR_DATETIME" AS "flight_db_ARR_DATETIME", flight_db."DEP_AIRPORT" AS "flight_db_DEP_AIRPORT", flight_db."ARR_AIRPORT" AS "flight_db_ARR_AIRPORT", flight_db."AIRLINE" AS "flight_db_AIRLINE", flight_db."FLIGHTNUMBER" AS "flight_db_FLIGHTNUMBER", flight_db."PRICE" AS "flight_db_PRICE", flight_db."LINK" AS "flight_db_LINK", flight_db."CREATEDDATE" AS "flight_db_CREATEDDATE"
flask_app  | FROM flight_db ORDER BY flight_db."CREATEDDATE" DESC]
flask_app  | (Background on this error at: https://sqlalche.me/e/20/f405)
flask_app  | [2023-12-30 21:08:21,619] ERROR in app: Exception on / [GET]
flask_app  | Traceback (most recent call last):
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
flask_app  |     self.dialect.do_execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
flask_app  |     cursor.execute(statement, parameters)
flask_app  | psycopg2.errors.UndefinedTable: relation "flight_db" does not exist
flask_app  | LINE 2: FROM flight_db ORDER BY flight_db."CREATEDDATE" DESC
flask_app  |              ^
flask_app  |
flask_app  |
flask_app  | The above exception was the direct cause of the following exception:
flask_app  |
flask_app  | Traceback (most recent call last):
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1455, in wsgi_app
flask_app  |     response = self.full_dispatch_request()
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 869, in full_dispatch_request
flask_app  |     rv = self.handle_user_exception(e)
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask_cors/extension.py", line 176, in wrapped_function
flask_app  |     return cors_after_request(app.make_response(f(*args, **kwargs)))
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 867, in full_dispatch_request
flask_app  |     rv = self.dispatch_request()
flask_app  |   File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 852, in dispatch_request
flask_app  |     return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
flask_app  |   File "/app/app.py", line 258, in index
flask_app  |     posts = FlightDB.query.order_by(FlightDB.CREATEDDATE.desc()).all()
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2693, in all
flask_app  |     return self._iter().all()  # type: ignore
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2847, in _iter
flask_app  |     result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2308, in execute
flask_app  |     return self._execute_internal(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2190, in _execute_internal
flask_app  |     result: Result[Any] = compile_state_cls.orm_execute_statement(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
flask_app  |     result = conn.execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
flask_app  |     return meth(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
flask_app  |     return connection._execute_clauseelement(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
flask_app  |     ret = self._execute_context(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
flask_app  |     return self._exec_single_context(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
flask_app  |     self._handle_dbapi_exception(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
flask_app  |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
flask_app  |     self.dialect.do_execute(
flask_app  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
flask_app  |     cursor.execute(statement, parameters)
flask_app  | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "flight_db" does not exist
flask_app  | LINE 2: FROM flight_db ORDER BY flight_db."CREATEDDATE" DESC
flask_app  |              ^
flask_app  |
flask_app  | [SQL: SELECT ***
flask_app  | FROM flight_db ORDER BY flight_db."CREATEDDATE" DESC]
flask_app  | (Background on this error at: https://sqlalche.me/e/20/f405)
flask_app  | [2023-12-30 21:08:52 +0000] [1] [CRITICAL] WORKER TIMEOUT (pid:6)
flask_app  | [2023-12-30 21:08:52 +0000] [6] [INFO] Worker exiting (pid: 6)
flask_app  | [2023-12-30 21:08:52 +0000] [1] [ERROR] Worker (pid:6) exited with code 1
flask_app  | [2023-12-30 21:08:52 +0000] [1] [ERROR] Worker (pid:6) exited with code 1.
flask_app  | [2023-12-30 21:08:52 +0000] [290] [INFO] Booting worker with pid: 290

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby Iliev

Site Moderator

• January 2, 2024

Hi there,

Based on the error message and the provided Docker Compose configuration, it looks like your Flask application is not able to find the PostgreSQL database, which is leading to the UndefinedTable error. This issue might be due to how the services are configured or networked in your Docker Compose setup.

What you could do is, in your Docker Compose file, add a health check for the PostgreSQL service. This ensures that the database is fully operational before your Flask application starts interacting with it:

services:
  flask_db:
    # ... other configuration ...
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

Then, modify your Flask application service in Docker Compose to depend on the healthy state of the PostgreSQL service. This can be done using the depends_on configuration:

services:
  flask_app:
    # ... other configuration ...
    depends_on:
      flask_db:
        condition: service_healthy

On another note, by default, Docker Compose sets up a network for your containers and they should be able to communicate using their service names as hostnames. Ensure that your Flask app is correctly pointing to flask_db as the database host.

Also, you’re exposing PostgreSQL’s port to the host machine. This is generally not necessary unless you need to access the database from outside the Docker network.

Let me know how it goes!

Best,

Bobby