-1

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
Docuemada
  • 1,703
  • 2
  • 25
  • 44
  • Try to replicate the issue without docker and share the results – JRichardsz Nov 13 '22 at 03:08
  • @JRichardsz What output would be useful to you in replication and I'll post. Is there anything in particular you suggest looking out for? I had everything running directly on my windows machine without issue. Migrating just postgres to docker seemed to work (or at least I could still access with my local machine's pgadmin or with a dockerized adminer container. – Docuemada Nov 13 '22 at 03:29
  • `localhost` in Docker usually means "the current container". In your case your database is running in a separate container so you need to set `POSTGRES_HOSTNAME=db`, using the Compose service name of the database container as a host name. I don't think any of the questions you linked to are the right duplicate but this does get asked very frequently. – David Maze Nov 13 '22 at 11:53
  • @DavidMaze that was was the issue that I overlooked, thank you! JRichardsz references are useful to explain why my in-python postgres call was incorrectly linking to localhost. Despite the downvotes, I'll leave this here for reference so that others like me can confirm its a host/ports issue and not a windows or other software versioning issue. – Docuemada Nov 14 '22 at 00:52

2 Answers2

1

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
Franco Milanese
  • 402
  • 3
  • 7
1

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

JRichardsz
  • 14,356
  • 6
  • 59
  • 94