DigitalOcean Droplet中Docker容器的psycopg2连接PostgreSQL失败求助

我尝试将爬虫项目打包进Docker容器，部署到DigitalOcean的Droplet上，爬虫负责抓取网站数据并写入DigitalOcean托管的PostgreSQL数据库。本地测试正常，但部署后出现数据库连接错误。

本地测试情况 #

本地运行以下命令，爬虫能成功写入数据库：

docker compose up

GitHub Actions 构建推送脚本 #

代码推送到master分支时，通过以下脚本构建并推送镜像至DigitalOcean容器注册表：

name: CI

# Controls when the workflow will run.
on:
  # Triggers the workflow on push events but only for the master branch
  push:
    branches: [ master ]

  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:
    inputs:
      version:
        description: 'Image version'
        required: true

env:
  REGISTRY: "registry.digitalocean.com/*****-registery"
  IMAGE_NAME: "******-scraper"
  POSTGRES_USERNAME: ${{ secrets.POSTGRES_USERNAME }}
  POSTGRES_PASSWORD: ${{ secrets.POSTGRES_PASSWORD }}
  POSTGRES_HOSTNAME: ${{ secrets.POSTGRES_HOSTNAME }}
  POSTGRES_PORT: ${{ secrets.POSTGRES_PORT }}
  POSTGRES_DATABASE: ${{ secrets.POSTGRES_DATABASE }}
  SPLASH_URL: ${{ secrets.SPLASH_URL }}

jobs:
  build-compose:
    name: Build docker-compose
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v2

    - name: Install doctl
      uses: digitalocean/action-doctl@v2
      with:
        token: ${{ secrets.DIGITALOCEAN_ACCESS_TOKEN }}

    - name: Login to DO Container Registry with short-lived creds
      run: doctl registry login --expiry-seconds 1200

    - name: Remove all old images
      run: if [ ! -z "$(doctl registry repository list | grep "****-scraper")" ]; then doctl registry repository delete-manifest ****-scraper $(doctl registry repository list-tags ****-scraper | grep -o "sha.*") --force; else echo "No repository"; fi

    - name: Build compose
      run: docker compose -f docker-compose.yaml up -d

    - name: Push to Digital Ocean registery
      run: docker compose push

  deploy:
    name: Deploy from registery to droplet
    runs-on: ubuntu-latest
    needs: build-compose

Droplet 手动部署步骤 #

登录Droplet后，安装Docker和Docker Compose，执行以下命令运行镜像：

# Login to registry
docker login -u DO_TOKEN -p DO_TOKEN registry.digitalocean.com
# Stop running container
docker stop ****-scraper
# Remove old container
docker rm ****-scraper
# Run a new container from a new image
docker run -d --restart always --name ****-scraper registry.digitalocean.com/****-registery/****-scraper

错误信息 #

容器启动后，Python脚本立即报错：

psycopg2.OperationalError: could not connect to server: No such file
or directory Is the server running locally and accepting connections
on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

相关配置文件 #

Dockerfile #

# As Scrapy runs on Python, I run the official Python 3 Docker image.
FROM python:3.9.7-slim

# Set the working directory to /usr/src/app.
WORKDIR /usr/src/app

# Install libpq-dev for psycopg2 python package
RUN apt-get update \
    && apt-get -y install libpq-dev gcc

# Copy the file from the local host to the filesystem of the container at the working directory.
COPY requirements.txt ./

# Install Scrapy specified in requirements.txt.
RUN pip3 install --no-cache-dir -r requirements.txt

# Copy the project source code from the local host to the filesystem of the container at the working directory.
COPY . .

# For Slash
EXPOSE 8050

# Run the crawler when the container launches.
CMD [ "python3", "./****/launch_spiders.py" ]

docker-compose.yaml #

version: "3"

services:
  splash:
    image: scrapinghub/splash
    restart: always
    command: --maxrss 2048 --max-timeout 3600 --disable-lua-sandbox --verbosity 1
    ports:
      - "8050:8050"
  launch_spiders:
    restart: always
    build: .
    volumes:
      - .:/usr/src/app
    image: registry.digitalocean.com/****-registery/****-scraper
    depends_on:
      - splash

问题分析与解决方案 #

核心原因 #

错误提示psycopg2尝试连接本地Unix套接字，说明爬虫代码的数据库连接配置没有指向DigitalOcean托管的远程PostgreSQL，或者容器运行时缺少必要的环境变量，导致连接参数错误。

解决步骤 #

1. 检查爬虫代码的数据库连接逻辑
   确保代码中使用环境变量获取数据库连接参数，而非硬编码localhost或套接字路径。示例代码：

   import os
   import psycopg2
   
   conn = psycopg2.connect(
       host=os.getenv("POSTGRES_HOSTNAME"),
       port=os.getenv("POSTGRES_PORT", "5432"),
       dbname=os.getenv("POSTGRES_DATABASE"),
       user=os.getenv("POSTGRES_USERNAME"),
       password=os.getenv("POSTGRES_PASSWORD")
   )
   

2. 运行容器时传入环境变量
   在Droplet上运行容器时，必须通过-e参数传递所有数据库和Splash相关的环境变量。如果依赖Splash服务，建议使用docker-compose运行（避免单独启动容器无法访问Splash）：

   • 方式1：使用docker-compose
     将本地的docker-compose.yaml上传到Droplet，创建.env文件填入所有必要的环境变量（POSTGRES_USERNAME、POSTGRES_PASSWORD等），然后执行：

     docker compose up -d
     

   • 方式2：单独运行容器

     docker run -d --restart always --name ****-scraper \
       -e POSTGRES_USERNAME=你的数据库用户名 \
       -e POSTGRES_PASSWORD=你的数据库密码 \
       -e POSTGRES_HOSTNAME=你的数据库主机地址 \
       -e POSTGRES_PORT=5432 \
       -e POSTGRES_DATABASE=你的数据库名 \
       -e SPLASH_URL=http://你的DropletIP:8050 \
       registry.digitalocean.com/****-registery/****-scraper
     

3. 验证DigitalOcean PostgreSQL的访问权限
   登录DigitalOcean控制台，进入PostgreSQL数据库的"Settings" -> "Connections"，确认Droplet的IPv4地址已添加到允许访问的IP列表中。

4. 修复GitHub Actions构建步骤
   原脚本中docker compose up -d会在GitHub Actions runner上启动容器，这是多余的操作，应改为仅构建镜像：

   - name: Build compose
     run: docker compose -f docker-compose.yaml build
   
   - name: Push to Digital Ocean registery
     run: docker compose push
   

内容的提问来源于stack exchange，提问作者Anthony De Faria