GitHub Actions中psycopg2连接PostgreSQL报OperationalError求助

错误信息 #

FAILED app/tests/test_db.py::test_database_retrieval - psycopg2.OperationalError: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory

更具体的连接测试错误：

FAILED app/tests/test_db.py::test_db_connection - Failed: Database connection failed: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

环境与配置 #

GitHub Actions工作流配置 #

name: CI

on:
  push:
  
jobs:

  build:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v4

      - name: Create env file
        run: |
          echo "${{ secrets.ENV_FILE }}" > $GITHUB_WORKSPACE/app/.env
          echo "${{ secrets.ENV_TEST_FILE }}" > $GITHUB_WORKSPACE/app/tests/.env.test
      
      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.x'
          cache: 'pip'
          
      - name: Install dependencies
        run: |
          python -m pip install --upgrade pip
          pip install -r requirements.txt

      - name: Set up PostgreSQL
        run: |
          sudo apt-get install postgresql libpq-dev
          sudo service postgresql start
          sudo -u postgres createuser --superuser "$USER"

      - name: Start services with Docker Compose
        run: docker-compose up -d

      - name: Wait for healthchecks
        run: 
          timeout 60s sh -c 'until docker ps | grep flex_db | grep -q healthy; do echo "Waiting for container to be healthy..."; sleep 2; done'
      
      - name: Test code with pytest
        run: |
          pytest app/tests/
          
      - name: Shutdown Docker Compose
        if: always()
        run: docker-compose down

      - name: Analysing the code with pylint
        run: 
          pylint $GITHUB_WORKSPACE/app/*.py
        
      - name: Remove .env file
        if: always()
        run: |
          rm $GITHUB_WORKSPACE/app/.env
          rm $GITHUB_WORKSPACE/app/tests/.env.test

      - name: Discord notification
        if: always()
        env:
          DISCORD_WEBHOOK: ${{ secrets.DISCORD_WEBHOOK }}
        uses: Ilshidur/action-discord@master
        with:
          args: 'The deployment process has completed. Status: ${{ job.status }}.'

测试文件test_db.py #

"""
This module contains tests for database connectivity and data retrieval for the Flask application.
"""

import os
import pytest
from dotenv import load_dotenv
from app import create_app
import psycopg2


@pytest.fixture
def app():
    load_dotenv(dotenv_path='.env.test', override=True)
    app = create_app()
    with app.app_context():
        yield app


@pytest.fixture
def client(app):
    return app.test_client()


def test_db_connection(app):
    try:
        with psycopg2.connect(
                dbname=os.getenv('POSTGRES_DB'),
                user=os.getenv('POSTGRES_USER'),
                password=os.getenv('POSTGRES_PASSWORD')
        ) as conn:
            print('Connected to the PostgreSQL server.')
    except (psycopg2.DatabaseError, Exception) as error:
        print(error)
        pytest.fail(f"Database connection failed: {error}")


def test_database_retrieval(client):
    sql_statement = """
        SELECT exercise_name, exercises.exercise_description, sets, repetitions, rest_time
        FROM workouts
        JOIN workoutexercises ON workouts.workout_id = workoutexercises.workout_id
        JOIN exercises ON workoutexercises.exercise_id = exercises.exercise_id
        WHERE workouts.workout_id = 4;
    """

    with client.application.app_context():
        with psycopg2.connect(
                dbname=os.getenv('POSTGRES_DB'),
                user=os.getenv('POSTGRES_USER'),
                password=os.getenv('POSTGRES_PASSWORD')

        ) as conn:
            with conn.cursor() as cur:
                cur.execute(sql_statement)
                results = cur.fetchall()
                print(results)
                assert results

Docker Compose配置 #

version: '3.8'
services:
  db:
    container_name: flex_db
    image: postgres
    restart: always
    env_file:
      - app/.env
    volumes:
      - ./sql:/docker-entrypoint-initdb.d
    ports:
      - "5433:5432"
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -U $${POSTGRES_USER} -d $${POSTGRES_DB} -t 1" ]
      interval: 10s
      timeout: 10s
      retries: 10
      start_period: 10s

  flex_app:
    container_name: flex_app
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "5000:5000"
    depends_on:
      db:
        condition: service_healthy
    links:
      - db

volumes:
  sql:

Flask应用初始化文件__init__.py #

"""
This module initializes the Flask application, configures it with environment variables,
sets up the database with SQLAlchemy, and registers blueprints for routing.
"""

import os
from flask import Flask
from dotenv import load_dotenv
from flask_sqlalchemy import SQLAlchemy

load_dotenv()

db = SQLAlchemy()


def create_app():
    """
        Creates and configures an instance of the Flask application. Environment variables are used to
        set the secret key and database configuration. The SQLAlchemy database instance is initialized
        with the app, and the main blueprint for routing is registered.

        Returns:
            app: The instance of the Flask application.
        """
    app = Flask(__name__)

    app.secret_key = os.getenv('APP_SECRET_KEY')

    db_user = os.getenv('POSTGRES_USER')
    db_password = os.getenv('POSTGRES_PASSWORD')
    db_host = os.getenv('POSTGRES_HOST')
    db_port = "5432"
    db_name = os.getenv('POSTGRES_DB')
    db_uri = f"postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"
    app.config['SQLALCHEMY_DATABASE_URI'] = db_uri

    db.init_app(app)

    with app.app_context():
        from .routes import main as main_blueprint
        app.register_blueprint(main_blueprint)

    return app

已尝试的排查步骤 #

• 确认GitHub Actions工作流中PostgreSQL服务已正确启动
• 反复检查环境变量与连接字符串
• 在线搜索同类问题

解决方案与排查思路 #

1. 核心问题：连接目标错误 #

工作流中同时启动了本地系统PostgreSQL和Docker容器PostgreSQL，但测试代码默认连接本地Unix套接字（/var/run/postgresql/.s.PGSQL.5432），而Docker容器的PostgreSQL映射端口为5433:5432，导致测试连到了未正确配置的本地服务，而非目标容器数据库。

解决方式二选一：

• 删除本地PostgreSQL安装步骤：无需在GitHub Actions runner中安装本地PostgreSQL，Docker容器已提供所需数据库服务，直接移除Set up PostgreSQL步骤即可。
• 修改测试代码的连接参数：在psycopg2连接时指定主机和端口，对应Docker映射的配置：

  # 在test_db.py的连接代码中添加host和port参数
  with psycopg2.connect(
          dbname=os.getenv('POSTGRES_DB'),
          user=os.getenv('POSTGRES_USER'),
          password=os.getenv('POSTGRES_PASSWORD'),
          host=os.getenv('POSTGRES_HOST', 'localhost'),  # 确保.env.test中POSTGRES_HOST为localhost
          port=os.getenv('POSTGRES_PORT', '5433')       # 对应Docker映射的5433端口
  ) as conn:
  

2. 验证环境变量正确性 #

确保app/tests/.env.test中包含正确的数据库连接配置：

POSTGRES_HOST=localhost
POSTGRES_PORT=5433
POSTGRES_DB=你的数据库名
POSTGRES_USER=你的用户名
POSTGRES_PASSWORD=你的密码

同时确认GitHub Secrets中ENV_TEST_FILE的内容与上述一致。

3. 优化容器等待逻辑 #

当前的健康检查等待逻辑依赖docker ps的输出匹配，可靠性不足，替换为直接调用容器内的pg_isready命令：

# 替换Wait for healthchecks步骤的run命令
timeout 60s sh -c 'until docker-compose exec -T db pg_isready -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"; do echo "Waiting for database to be ready..."; sleep 2; done'

添加-T参数避免终端分配问题，确保命令能在GitHub Actions环境中正常执行。

4. 避免本地服务端口冲突 #

如果保留本地PostgreSQL安装步骤，需确保其端口不与Docker容器映射的端口冲突，但此步骤完全多余，建议直接删除以简化流程。

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