Docker Compose中Shiny应用连接PostGIS数据库失败的解决咨询

问题场景 #

你搭建了包含PostGIS数据库和Shiny应用的Docker Compose环境：

version: "3"
services:
  db:
    image: kartoza/postgis:14-3.2
    environment:
      - POSTGRES_DB=AAAAAAAA
      - POSTGRES_USER=BBBBBBBBBB
      - POSTGRES_PASS=CCCCCCCCCC
      - POSTGRES_MULTIPLE_EXTENSIONS=postgis,postgis_raster
    ports:
      - "5432:5432"
    restart: on-failure
    healthcheck:
      test: "exit 0"
  shiny:
    container_name: shiny
    build: ./webapp4
    ports:
      - "8787:8787"
    depends_on:
      - "db"

环境启动正常，但Shiny应用执行以下连接代码时出现连接拒绝错误：

remote_conn <- dbConnect(RPostgres::Postgres(),
                         dbname = "AAAAAAAA",
                         host="localhost",
                         port="5432",
                         user="BBBBBBBBBB",
                         password="CCCCCCCCCC")

错误信息：

Warning: Error in : could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?
could not connect to server: Cannot assign requested address
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?

  55: <Anonymous>
  54: stop
  53: connection_create
  52: .local
  51: dbConnect
  49: server
   3: runApp
   2: print.shiny.appobj
   1: <Anonymous>
Error : could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?
could not connect to server: Cannot assign requested address
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?

问题原因 #

1. 容器网络寻址错误：在Shiny容器内部，localhost指向容器自身，而非PostGIS数据库容器。Docker Compose默认会为每个服务创建独立网络，服务名（db）可直接作为数据库容器的主机名使用。
2. 数据库就绪检查失效：原配置中healthcheck: test: "exit 0"是无效检查，无法确保PostgreSQL服务真正启动就绪；depends_on仅保证启动顺序，不等待数据库完全可用，可能导致Shiny启动时数据库尚未就绪。

解决方案 #

1. 修改Shiny数据库连接代码 #

将连接代码中的host从localhost改为Docker Compose中数据库服务的名称db：

remote_conn <- dbConnect(RPostgres::Postgres(),
                         dbname = "AAAAAAAA",
                         host="db",  # 替换为服务名db
                         port="5432",
                         user="BBBBBBBBBB",
                         password="CCCCCCCCCC")

2. 优化Docker Compose配置 #

更新数据库服务的健康检查规则，确保验证PostgreSQL服务就绪；同时修改Shiny服务的依赖条件，等待数据库健康检查通过后再启动：

version: "3"
services:
  db:
    image: kartoza/postgis:14-3.2
    environment:
      - POSTGRES_DB=AAAAAAAA
      - POSTGRES_USER=BBBBBBBBBB
      - POSTGRES_PASS=CCCCCCCCCC
      - POSTGRES_MULTIPLE_EXTENSIONS=postgis,postgis_raster
    ports:
      - "5432:5432"
    restart: on-failure
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U BBBBBBBBBB -d AAAAAAAA"]
      interval: 10s  # 每10秒检查一次
      timeout: 5s    # 检查超时时间5秒
      retries: 5     # 最多重试5次
  shiny:
    container_name: shiny
    build: ./webapp4
    ports:
      - "8787:8787"
    depends_on:
      db:
        condition: service_healthy  # 等待db服务健康检查通过

3. 验证修复效果 #

执行以下命令重新启动环境：

docker-compose down
docker-compose up --build

访问Shiny应用（默认地址http://localhost:8787），验证数据库连接是否正常。

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