Can't connect to PostgreSQL db from docker executor (gitlab ci/cd)

Question

I tried the way explained in official gitlab documentation, configured database on the host and tried to connect with DB_HOST=postgres. But psycopg throws error: (psycopg.OperationalError) connection is bad: Name or service not known. My .gitlab-ci.yml:

image: python:3.11

default:
  services:
    - postgres:15.1

before_script:
  - python3.11 --version
  - pip3.11 --version
  - echo "hello world"

build-python:
  stage: build
  script:
    - echo "this is test job"
    - cd backend
    - pip3.11 install virtualenv
    - virtualenv venv
    - source venv/bin/activate
    - pip3.11 install -r requirements.txt
    - echo "ended building python test job"
    - alembic revision --autogenerate -m "migrating"
    - alembic upgrade head

Alembic.ini file from where I'm trying to access db:

# A generic, single database configuration.

[alembic]
# path to migration scripts
script_location = migrations

prepend_sys_path = .

version_path_separator = os  # Use os.pathsep. Default configuration used for new projects.


sqlalchemy.url = driver://user:pass@localhost/dbname


[post_write_hooks]

[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

sqlalchemy.url is defined in env.py

config.set_main_option('sqlalchemy.url', DATABASE_URL)

The DATABASE_URL:

DATABASE_URL = f'postgresql+psycopg://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}'

What did I miss to do? Thanks for any help.

Answer 1

I'm guessing the PostgreSQL container fails to start because it misses the POSTGRES_PASSWORD mandatory environement variable. See the documentation for this image, section Environment Variables. One way to troubleshoot would be to print the logs of the PostgreSQL container using

variables:
  CI_DEBUG_SERVICES: 'true'

It will show the logs of the PostgreSQL container on your job's logs.

If the PostgreSQL container starts properly, then you should be able to reach it using the postgres hostname, as according to the documentation :

The default aliases for the service’s hostname are created from its image name following these rules:

• Everything after the colon (:) is stripped.
• Slash (/) is replaced with double underscores (__) and the primary alias is created.
• Slash (/) is replaced with a single dash (-) and the secondary alias is created (requires GitLab Runner v1.1.0 or later).