Postgres Docker容器内Flyway启动时无法连接数据库的问题

我正在为PostgreSQL构建自定义Docker镜像，启动时自动运行Flyway数据库迁移，用于GitLab CI/CD的服务容器（无法使用docker-compose）。目前已完成构建、安装和可执行配置，但启动时Flyway无法连接容器内的PostgreSQL，出现连接拒绝错误。

现有配置 #

Dockerfile #

FROM postgres:12 AS db

WORKDIR /home

# Install wget
RUN apt-get update -y \
  && apt-get install wget -y \
  && apt-get install sudo -y \
  && apt-get install lsof

RUN usermod -aG sudo postgres
RUN bash -c 'echo "postgres ALL=(ALL:ALL) NOPASSWD: ALL" | (EDITOR="tee -a" visudo)'

# Install latest version of flyway 7
RUN wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/7.9.2/flyway-commandline-7.9.2-linux-x64.tar.gz | tar xvz && \
  ln -s `pwd`/flyway-7.9.2/flyway /usr/local/bin

# Copy database migrations for flyway
COPY reds/migrations migrations

# Set environment variables for flyway
ENV FLYWAY_LOCATIONS="/home/migrations"
ENV FLYWAY_SCHEMAS="reds"
ENV FLYWAY_CONNECT_RETRIES=5
ENV FLYWAY_BASELINE_ON_MIGRATE=false
ENV FLYWAY_OUT_OF_ORDER=false

# Make postgres startup script run flyway migrations
COPY reds/init.sh /docker-entrypoint-initdb.d/init.sh
RUN chmod +x /docker-entrypoint-initdb.d/init.sh && \
  chown -R postgres /docker-entrypoint-initdb.d

EXPOSE 5432
CMD ["postgres"]

启动执行脚本（init.sh） #

#!/bin/bash
set -eou pipefail

# 试过localhost、0.0.0.0和127.0.0.1
host="$(hostname -i)"

sudo flyway migrate \
  -url="jdbc:postgresql://$host:5432/$POSTGRES_DB" \
  -schemas="$FLYWAY_SCHEMAS" \
  -locations="$FLYWAY_LOCATIONS" \
  -connectRetries="$FLYWAY_CONNECT_RETRIES" \
  -baselineOnMigrate="$FLYWAY_BASELINE_ON_MIGRATE" \
  -outOfOrder="$FLYWAY_OUT_OF_ORDER" \
  -user="$POSTGRES_USER" \
  -password="$POSTGRES_PASSWORD"

构建命令 #

$ docker build -t reds-docker:latest -f "Dockerfile.reds" .

运行命令 #

$ docker run --rm \
  --env POSTGRES_USER="postgres" \
  --env POSTGRES_PASSWORD="postgres" \
  --env POSTGRES_DB="postgres" \
  reds-docker:latest

报错信息 #

WARNING: Connection error: Connection to 172.17.0.2:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. (Caused by Connection refused (Connection refused))

完整启动日志 #

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2023-02-06 13:36:59.228 UTC [48] LOG:  starting PostgreSQL 12.13 (Debian 12.13-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-02-06 13:36:59.229 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-02-06 13:36:59.237 UTC [49] LOG:  database system was shut down at 2023-02-06 13:36:59 UTC
2023-02-06 13:36:59.239 UTC [48] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sh
WARNING: This version of Flyway is out of date. Upgrade to Flyway 9.14.1
Flyway Community Edition 7.9.2 by Redgate
WARNING: Connection error: Connection to 172.17.0.2:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. (Caused by Connection refused (Connection refused)) Retrying in 1 sec...
WARNING: Connection error: Connection to 172.17.0.2:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. (Caused by Connection refused (Connection refused)) Retrying in 2 sec...

问题原因 #

从启动日志可以看到，PostgreSQL仅监听了Unix Socket（/var/run/postgresql/.s.PGSQL.5432），没有监听任何TCP端口。而Flyway配置尝试通过TCP连接（使用容器IP或localhost），自然会被拒绝。

另外，init.sh中使用sudo完全没必要——docker-entrypoint-initdb.d目录下的脚本默认是以postgres用户身份执行的，该用户已经拥有操作数据库和Flyway的权限。

解决方案 #

方案1：使用Unix Socket连接（推荐） #

直接通过Unix Socket连接PostgreSQL，无需修改PostgreSQL的监听配置，更高效且简单。修改init.sh的连接URL即可：

#!/bin/bash
set -eou pipefail

flyway migrate \
  -url="jdbc:postgresql:///$POSTGRES_DB?socketFactory=org.postgresql.core.PGStreamSocketFactory&socketPath=/var/run/postgresql/" \
  -schemas="$FLYWAY_SCHEMAS" \
  -locations="$FLYWAY_LOCATIONS" \
  -connectRetries="$FLYWAY_CONNECT_RETRIES" \
  -baselineOnMigrate="$FLYWAY_BASELINE_ON_MIGRATE" \
  -outOfOrder="$FLYWAY_OUT_OF_ORDER" \
  -user="$POSTGRES_USER" \
  -password="$POSTGRES_PASSWORD"

同时，Dockerfile中可以移除关于sudo的配置，减少不必要的权限提升：

# 移除以下两行
# RUN usermod -aG sudo postgres
# RUN bash -c 'echo "postgres ALL=(ALL:ALL) NOPASSWD: ALL" | (EDITOR="tee -a" visudo)'

方案2：让PostgreSQL监听TCP端口 #

如果你坚持使用TCP连接，需要修改PostgreSQL配置使其监听所有地址，并允许本地TCP连接：

在init.sh开头添加配置修改和服务重启：

# 修改监听配置，允许所有IP访问
echo "listen_addresses = '*'" >> /var/lib/postgresql/data/postgresql.conf
# 重启PostgreSQL应用配置
pg_ctl -D /var/lib/postgresql/data reload

然后修改init.sh中的连接URL为本地TCP地址：

-url="jdbc:postgresql://127.0.0.1:5432/$POSTGRES_DB"

验证 #

重新构建镜像并运行，Flyway应该能成功连接PostgreSQL并执行迁移。

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