Connect python app container, postgres container, and persistent database on external harddrive with docker compose - Debian

Docuemada
November 13, 2022
260 views
0 votes
2 Answers

Using docker-compose, I am running a postgres container (db). The data itself is persistently stored on my Windows machine. And this works. I am unable to get another container running a python application to access the database.

My docker-compose file is as follows, where I use ## to denote some options that I’ve tried that :

version: '0.1'

services
    
  db: #also the server name
    image: postgres:15.0
    restart: always
      
    environment:
       
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: my_password
      ##POSTGRES_HOST_AUTH_METHOD: trust
      ##POSTGRES_HOST: postgres
      ##PGDATA: d:/pg #/var/lib/postgresql/data/pgdata
    ports:
        #port on machine:port on container
      - 1234:5432
    volumes: 
        #path on my windows machine:path to postgres default folder
      - D:/pg:/var/lib/postgresql/data
    privileged:
        true

  app:
    image: simple_python_debug

    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=my_password
      - POSTGRES_PORT=5432
      - POSTGRES_DB_NAME=postgres
      - POSTGRES_HOSTNAME=localhost

    depends_on:
      - db

My dockerfile (simple_python_debug) for the python script is

FROM python:3.9

# Install pip requirements
COPY requirements.txt .
RUN pip3 install -r requirements.txt
WORKDIR /app #this is where the .py file is located
COPY . /app

# Creates a non-root user with an explicit UID and adds permission to access the /app folder
# For more info, please refer to https://aka.ms/vscode-docker-python-configure-containers
RUN adduser -u 5678 --disabled-password --gecos "" appuser && chown -R appuser /app
USER appuser

CMD ["python", "test.py"]

The dockerfile for postgres is simply:

services:

  db:
    image: postgres:15.0
    restart: always
    environment:
      POSTGRES_PASSWORD: my_password

test.py (stored in /app) contains

import os
from sqlalchemy import create_engine

port = os.getenv('POSTGRES_PORT')#'5432'
password = os.getenv('POSTGRES_PASSWORD')#'my_password'
user = os.getenv('POSTGRES_USER')#'postgres'
hostname = os.getenv('POSTGRES_HOSTNAME') #'localhost'
database_name = os.getenv('POSTGRES_DB_NAME') #'postgres'
connection_params = 'postgresql+psycopg2://' + user +    ':' + password + '@' + hostname + '/' + database_name 
    #+ "connect_args = {'options': '-csearch_path={}'.format(" + schema + ')}'
engine = create_engine(connection_params)

With docker desktop running on my local windows machine and postgres service on the local windows machine shutdown for good measure, I run

.../test>docker-compose up from powershell 7; part of the message for the working postgres container is:

test-db-1   |
test-db-1   | PostgreSQL Database directory appears to contain a database; Skipping initialization
test-db-1   |
test-db-1   | 2022-11-12 15:27:14.665 UTC [1] LOG:  starting PostgreSQL 15.0 (Debian 15.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
test-db-1   | 2022-11-12 15:27:14.665 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
test-db-1   | 2022-11-12 15:27:14.665 UTC [1] LOG:  listening on IPv6 address "::", port 5432
test-db-1   | 2022-11-12 15:27:14.674 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

test.py fails on calling create_engine; the tail of the error message is

test-app-1  |     Is the server running on host "localhost" (127.0.0.1) and accepting
test-app-1  |     TCP/IP connections on port 5432?
test-app-1  | could not connect to server: Cannot assign requested address
test-app-1  |     Is the server running on host "localhost" (::1) and accepting
test-app-1  |     TCP/IP connections on port 5432?
test-app-1  |
test-app-1  | (Background on this error at: https://sqlalche.me/e/14/e3q8)

There are several stack overflow related questions and the error message. There’s even medium articles on using postgres with python and docker.

https://stefanopassador.medium.com/docker-compose-with-python-and-posgresql-45c4c5174299

Docker – How can run the psql command in the postgres container?

Can't connect python app container to local postgres

From inside of a Docker container, how do I connect to the localhost of the machine?

Perhaps I’m missing something simple, but any suggestions or pointing to the appropriate docker docs could help get me unstuck.

EDIT:
As recommended by @JRichardz complete error message is below for posterity:

test-app-1  | Traceback (most recent call last):
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect
test-app-1  |     return fn()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 307, in connect
test-app-1  |     return _ConnectionFairy._checkout(self)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 767, in _checkout
test-app-1  |     fairy = _ConnectionRecord.checkout(pool)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 425, in checkout
test-app-1  |     rec = pool._do_get()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
test-app-1  |     self._dec_overflow()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
test-app-1  |     compat.raise_(
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
test-app-1  |     raise exception
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
test-app-1  |     return self._create_connection()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 253, in _create_connection
test-app-1  |     return _ConnectionRecord(self)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 368, in __init__
test-app-1  |     self.__connect()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 611, in __connect
test-app-1  |     pool.logger.debug("Error on connect(): %s", e)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
test-app-1  |     compat.raise_(
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
test-app-1  |     raise exception
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
test-app-1  |     connection = pool._invoke_creator(self)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 578, in connect
test-app-1  |     return dialect.connect(*cargs, **cparams)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect
test-app-1  |     return self.dbapi.connect(*cargs, **cparams)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
test-app-1  |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
test-app-1  | psycopg2.OperationalError: could not connect to server: Connection refused
test-app-1  |     Is the server running on host "localhost" (127.0.0.1) and accepting
test-app-1  |     TCP/IP connections on port 5432?
test-app-1  | could not connect to server: Cannot assign requested address
test-app-1  |     Is the server running on host "localhost" (::1) and accepting
test-app-1  |     TCP/IP connections on port 5432?
test-app-1  |
test-app-1  |
test-app-1  | The above exception was the direct cause of the following exception:
test-app-1  |
test-app-1  | Traceback (most recent call last):
test-app-1  |   File "/app/btc_sma_recommendations_1_inflection.py", line 51, in <module>
test-app-1  |     with engine.connect() as connection:
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3166, in connect
test-app-1  |     return self._connection_cls(self, close_with_result=close_with_result)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
test-app-1  |     else engine.raw_connection()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3245, in raw_connection
test-app-1  |     return self._wrap_pool_connect(self.pool.connect, _connection)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3215, in _wrap_pool_connect
test-app-1  |     Connection._handle_dbapi_exception_noconnection(
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2069, in _handle_dbapi_exception_noconnection
test-app-1  |     util.raise_(
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
test-app-1  |     raise exception
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect
test-app-1  |     return fn()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 307, in connect
test-app-1  |     return _ConnectionFairy._checkout(self)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 767, in _checkout
test-app-1  |     fairy = _ConnectionRecord.checkout(pool)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 425, in checkout
test-app-1  |     rec = pool._do_get()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
test-app-1  |     self._dec_overflow()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
test-app-1  |     compat.raise_(
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
test-app-1  |     raise exception
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
test-app-1  |     return self._create_connection()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 253, in _create_connection
test-app-1  |     return _ConnectionRecord(self)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 368, in __init__
test-app-1  |     self.__connect()
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 611, in __connect
test-app-1  |     pool.logger.debug("Error on connect(): %s", e)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
test-app-1  |     compat.raise_(
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
test-app-1  |     raise exception
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
test-app-1  |     connection = pool._invoke_creator(self)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 578, in connect
test-app-1  |     return dialect.connect(*cargs, **cparams)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect
test-app-1  |     return self.dbapi.connect(*cargs, **cparams)
test-app-1  |   File "/usr/local/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
test-app-1  |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
test-app-1  | sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
test-app-1  |     Is the server running on host "localhost" (127.0.0.1) and accepting
test-app-1  |     TCP/IP connections on port 5432?
test-app-1  | could not connect to server: Cannot assign requested address
test-app-1  |     Is the server running on host "localhost" (::1) and accepting
test-app-1  |     TCP/IP connections on port 5432?
test-app-1  |
test-app-1  | (Background on this error at: https://sqlalche.me/e/14/e3q8)
test-app-1 exited with code 1
test-db-1   |
test-db-1   | PostgreSQL Database directory appears to contain a database; Skipping initialization
test-db-1   |
test-db-1   | 2022-11-14 00:00:02.724 UTC [1] LOG:  starting PostgreSQL 15.0 (Debian 15.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
test-db-1   | 2022-11-14 00:00:02.724 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
test-db-1   | 2022-11-14 00:00:02.724 UTC [1] LOG:  listening on IPv6 address "::", port 5432
test-db-1   | 2022-11-14 00:00:02.731 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
test-db-1   | 2022-11-14 00:00:02.771 UTC [29] LOG:  database system was shut down at 2022-11-13 02:41:42 UTC
test-db-1   | 2022-11-14 00:00:02.867 UTC [1] LOG:  database system is ready to accept connections

Answers

- FrancoMilanese
- November 13, 2022 at 4:34 am
- 0 votes
0
It will be better to define the volumes before using them like here.

Also if you want postgres’ data to persist you need to place inside the volume the folder

/var/lib/postgresql/data

something like
```
volumes:
 - fancy-volume:
 ... fancy definition..

services:
 fancy-app:
   ...
   volumes:
   - fancy-volume:/var/lib/postgresql/data
```
Login or Signup to reply.

- JRichardsz
- November 13, 2022 at 7:29 am
- 0 votes
0
This log is the key:
```
Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?
```
In docker there is no localhost.

To demonstrate this, just replace localhost by the ip in which the postgress is running.

To understand why localhost should kot be used in docker & docker-compose and what are the solutions, check:
If this work for you, add the exact error log in the question to help future generations
Login or Signup to reply.