Can't connect to postgres service from Flask app psycopg2 on kubernetes-CodePudding

I have setup a kubernetes cluster locally by minikube. I have the Postgres service running in the cluster. I am trying to run a Flask app that connects to the Postgres database using psycopg2, fetch records and expose them on a REST endpoint.

I am getting this erorr in gunicorn logs -

[2022-12-12 18:49:41  0000] [10] [ERROR] Error handling request /popular/locations
  File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not translate host name "postgres-postgresql.kafkaplaypen.svc.cluster.local:5432" to address: Name or service not kn>

I installed Postgres on the cluster using helm install postgres bitnami/postgresql. Here is some useful info helm showed me about my Postgres deployment -

PostgreSQL can be accessed via port 5432 on the following DNS names from within your cluster:

    postgres-postgresql.kafkaplaypen.svc.cluster.local - Read/Write connection

To get the password for "postgres" run:

    export POSTGRES_PASSWORD=$(kubectl get secret --namespace kafkaplaypen postgres-postgresql -o jsonpath="{.data.postgres-password}" | base64 -d)

To connect to your database run the following command:

    kubectl run postgres-postgresql-client --rm --tty -i --restart='Never' --namespace kafkaplaypen --image docker.io/bitnami/postgresql:15.1.0-debian-11-r7 --env="PGPASSWORD=$POSTGRES_PASSWORD" \
      --command -- psql --host postgres-postgresql -U postgres -d postgres -p 5432

    > NOTE: If you access the container using bash, make sure that you execute "/opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash" in order to avoid the error "psql: local user with ID 1001} does not exist"

To connect to your database from outside the cluster execute the following commands:

    kubectl port-forward --namespace kafkaplaypen svc/postgres-postgresql 5432:5432 &
    PGPASSWORD="$POSTGRES_PASSWORD" psql --host 127.0.0.1 -U postgres -d postgres -p 5432

Here is the code for my flask app -

app = Flask(__name__)
app.config["DEBUG"] = True

def get_db_connection():
    conn = psycopg2.connect(host='postgres-postgresql.kafkaplaypen.svc.cluster.local:5432',
                            database=os.environ['DB_NAME'],
                            user=os.environ['DB_USERNAME'],
                            password=os.environ['DB_PASSWORD'])
    return conn

@app.route('/popular/locations')
def get_popular_locations():
    conn = get_db_connection()

    cur = conn.cursor()
    cur.execute('SELECT * FROM tolocations;')
    data = cur.fetchall()

    cur.close()
    conn.close()

    return data

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8081, debug=True)

Using the following command to run flask pod -

kubectl run flaskapp -i --image flaskapp:latest --image-pull-policy Never --restart Never --namespace kafkaplaypen --env="DB_NAME=postgres" --env="DB_USERNAME=postgres" --env="DB_PASSWORD=$POSTGRES_PASSWORD"

Also, adding the dockerfile for my flask app in case it's is useful

FROM python:3.7-slim
WORKDIR /app

RUN apt-get update && apt-get install -y curl nano
RUN pip3 install flask psycopg2-binary gunicorn

COPY rest /app

EXPOSE 8081

CMD gunicorn -b 0.0.0.0:8081 --log-file /app/logs/gunicorn.log --log-level DEBUG src.expose:app

CodePudding user response：

Looking at the documentation, it looks like you need to remove the port from the host argument and use the port argument:

def get_db_connection():
    conn = psycopg2.connect(host="postgres-postgresql.kafkaplaypen.svc.cluster.local",
                            port=5432,
                            database=os.environ["DB_NAME"],
                            user=os.environ["DB_USERNAME"],
                            password=os.environ["DB_PASSWORD"])
    return conn

...or just drop the port, since you're using the default.