GitLab CI/CD Docker执行器无法连接PostgreSQL数据库求助

问题描述 #

按照GitLab官方文档配置PostgreSQL服务后，在CI作业中尝试通过DB_HOST=postgres连接数据库时，psycopg抛出错误：

(psycopg.OperationalError) connection is bad: Name or service not known

相关配置文件 #

.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

# 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

env.py中sqlalchemy.url配置

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

DATABASE_URL定义

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

遗漏的配置步骤 #

1. 配置PostgreSQL服务的初始化与连接环境变量 #

GitLab CI的Postgres服务不会自动注入连接所需的环境变量，需要手动在CI配置中添加两类变量：

• Postgres容器初始化变量：用于创建数据库、用户和密码
• 应用连接变量：供Python代码读取建立连接

修改.gitlab-ci.yml的default段：

default:
  services:
    - postgres:15.1
  variables:
    # Postgres容器初始化变量
    POSTGRES_USER: postgres
    POSTGRES_PASSWORD: ""
    POSTGRES_DB: postgres
    # 应用连接变量
    DB_USER: postgres
    DB_PASSWORD: ""
    DB_HOST: postgres
    DB_PORT: 5432
    DB_NAME: postgres

注：如果需要密码，可将POSTGRES_PASSWORD设为自定义值，同时同步DB_PASSWORD。

2. 添加Postgres服务就绪等待逻辑 #

Postgres容器启动需要时间，直接执行alembic命令可能在数据库未就绪时触发连接失败。在before_script中添加等待逻辑：

# 安装postgres客户端工具（用于检查连接）
- apt-get update && apt-get install -y postgresql-client
# 循环等待Postgres就绪
- until pg_isready -h $DB_HOST -U $DB_USER; do sleep 1; done

3. 验证环境变量是否正确传递 #

在CI作业的script中添加变量打印步骤，确认环境变量能被Python代码读取：

- echo "DB_HOST: $DB_HOST"
- echo "DB_USER: $DB_USER"

4. 确认服务别名匹配 #

如果为Postgres服务设置了自定义别名，需确保DB_HOST与别名一致。默认情况下，服务别名就是镜像名（此处为postgres），无需额外配置，但如果修改了别名，需同步调整DB_HOST。

内容的提问来源于stack exchange，提问作者Beknur Seydazim