如何在Docker-Compose分布式Airflow中配置PgBouncer解决连接耗尽问题

问题背景 #

我通过Docker-Compose搭建分布式Airflow架构：主服务（WebServer、Scheduler等）部署在单台服务器，Celery Worker分布在多台服务器。当前每5分钟运行数百个任务时，出现数据库连接耗尽问题，任务日志报错：

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "SERVER" (IP), port XXXXX failed: FATAL:  sorry, too many clients already

元数据库使用PostgreSQL，其max_connections保持默认值100。由于未来任务量将增长至每5分钟数千个，仅调高连接数并非长久之计，因此引入PgBouncer进行连接池管理。

初始PgBocker配置 #

pgbouncer:
  image: "bitnami/pgbouncer:1.16.0"
  restart: always
  environment:
    POSTGRESQL_HOST: "postgres"
    POSTGRESQL_USERNAME: ${POSTGRES_USER}
    POSTGRESQL_PASSWORD: ${POSTGRES_PASSWORD}
    POSTGRESQL_PORT: ${PSQL_PORT}
    PGBOUNCER_DATABASE: ${POSTGRES_DB}
    PGBOUNCER_AUTH_TYPE: "trust"
    PGBOUNCER_IGNORE_STARTUP_PARAMETERS: "extra_float_digits"
  ports:
    - '1234:1234'
  depends_on:
    - postgres

初始PgBouncer日志（无流量） #

pgbouncer 13:29:13.87 
pgbouncer 13:29:13.87 Welcome to the Bitnami pgbouncer container
pgbouncer 13:29:13.87 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-pgbouncer
pgbouncer 13:29:13.87 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-pgbouncer/issues
pgbouncer 13:29:13.88 
pgbouncer 13:29:13.89 INFO  ==> ** Starting PgBouncer setup **
pgbouncer 13:29:13.91 INFO  ==> Validating settings in PGBOUNCER_* env vars...
pgbouncer 13:29:13.91 WARN  ==> You set the environment variable PGBOUNCER_AUTH_TYPE=trust. For safety reasons, do not use this flag in a production environment.
pgbouncer 13:29:13.91 INFO  ==> Initializing PgBouncer...
pgbouncer 13:29:13.92 INFO  ==> Waiting for PostgreSQL backend to be accessible
pgbouncer 13:29:13.92 INFO  ==> Backend postgres:9876 accessible
pgbouncer 13:29:13.93 INFO  ==> Configuring credentials
pgbouncer 13:29:13.93 INFO  ==> Creating configuration file
pgbouncer 13:29:14.06 INFO  ==> Loading custom scripts...
pgbouncer 13:29:14.06 INFO  ==> ** PgBouncer setup finished! **

pgbouncer 13:29:14.08 INFO  ==> ** Starting PgBouncer **
2022-10-25 13:29:14.089 UTC [1] LOG kernel file descriptor limit: 1048576 (hard: 1048576); max_client_conn: 100, max expected fd use: 152
2022-10-25 13:29:14.089 UTC [1] LOG listening on 0.0.0.0:1234
2022-10-25 13:29:14.089 UTC [1] LOG listening on unix:/tmp/.s.PGSQL.1234
2022-10-25 13:29:14.089 UTC [1] LOG process up: PgBouncer 1.16.0, libevent 2.1.8-stable (epoll), adns: c-ares 1.14.0, tls: OpenSSL 1.1.1d  10 Sep 2019
2022-10-25 13:30:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:31:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:32:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:33:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:34:14.089 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:35:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:36:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:37:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:38:14.090 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-25 13:39:14.089 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us

疑问 #

1. 是否需要修改Docker-Compose中的PgBouncer配置？
2. 是否需要修改AIRFLOW__DATABASE__SQL_ALCHEMY_CONN变量？

更新1：修改Worker配置后的情况 #

仅修改Worker节点的Docker-Compose.yml，将数据库端口改为PgBouncer端口，此时PgBouncer日志出现流量，但Airflow任务仅排队未执行。未修改WebServer、Scheduler等服务的配置。

修改后的Worker配置：

AIRFLOW__DATABASE__SQL_ALCHEMY_CONN: postgresql+psycopg2://<XXX>@${AIRFLOW_WEBSERVER_URL}:${PGBOUNCER_PORT}/airflow
AIRFLOW__CELERY__RESULT_BACKEND: db+postgresql://<XXX>@${AIRFLOW_WEBSERVER_URL}:${PGBOUNCER_PORT}/airflow

修改后的PgBouncer日志：

2022-10-26 11:46:22.517 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-26 11:47:22.517 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-26 11:48:22.517 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-26 11:49:22.519 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-26 11:50:22.518 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-26 11:51:22.516 UTC [1] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-10-26 11:51:52.356 UTC [1] LOG C-0x5602cf8ab180: <XXX>@<IP:PORT> login attempt: db=airflow user=airflow tls=no
2022-10-26 11:51:52.359 UTC [1] LOG S-0x5602cf8b1f20: <XXX>@<IP:PORT> new connection to server (from <IP:PORT>)
2022-10-26 11:51:52.410 UTC [1] LOG C-0x5602cf8ab180: <XXX>@<IP:PORT> closing because: client close request (age=0s)
2022-10-26 11:51:52.834 UTC [1] LOG C-0x5602cf8ab180: <XXX>@<IP:PORT> login attempt: db=airflow user=airflow tls=no
2022-10-26 11:51:52.845 UTC [1] LOG C-0x5602cf8ab180: <XXX>@<IP:PORT> closing because: client close request (age=0s)
2022-10-26 11:51:56.752 UTC [1] LOG C-0x5602cf8ab180: <XXX>@<IP:PORT> login attempt: db=airflow user=airflow tls=no
2022-10-26 11:51:57.393 UTC [1] LOG C-0x5602cf8ab3b0: <XXX>@<IP:PORT> login attempt: db=airflow user=airflow tls=no
2022-10-26 11:51:57.394 UTC [1] LOG S-0x5602cf8b2150: <XXX>@<IP:PORT> new connection to server (from <IP:PORT>)
2022-10-26 11:51:59.906 UTC [1] LOG C-0x5602cf8ab180: <XXX>@<IP:PORT> closing because: client close request (age=3s)
2022-10-26 11:52:00.642 UTC [1] LOG C-0x5602cf8ab3b0: <XXX>@<IP:PORT> closing because: client close request (age=3s)

解决方案与排查要点 #

针对疑问的直接解答 #

1. 需要调整PgBouncer配置

   • 当前max_client_conn默认100，与PostgreSQL的max_connections一致，无法发挥连接池作用。需根据未来任务量设置更高值（如1000），同时配置default_pool_size（建议20-30，计算公式：PostgreSQL max_connections = default_pool_size * Airflow服务数量 + 预留连接）
   • 生产环境必须将PGBOUNCER_AUTH_TYPE从trust改为md5或scram-sha-256，避免无密码访问风险
   • 补充连接池相关环境变量：

     PGBOUNCER_MAX_CLIENT_CONN: 1000
     PGBOUNCER_DEFAULT_POOL_SIZE: 20
     PGBOUNCER_POOL_MODE: transaction  # Airflow推荐使用transaction模式，适配SQLAlchemy的连接使用逻辑
     

2. 必须修改所有Airflow服务的数据库连接变量

   • 仅修改Worker的连接无效，WebServer、Scheduler、Worker、Triggerer（若使用）所有服务的AIRFLOW__DATABASE__SQL_ALCHEMY_CONN和AIRFLOW__CELERY__RESULT_BACKEND都需指向PgBouncer的地址和端口
   • 连接字符串示例（若PgBouncer与主服务在同一Docker网络，直接用服务名pgbouncer即可）：

     AIRFLOW__DATABASE__SQL_ALCHEMY_CONN: postgresql+psycopg2://${POSTGRES_USER}:${POSTGRES_PASSWORD}@pgbouncer:${PGBOUNCER_PORT}/${POSTGRES_DB}
     AIRFLOW__CELERY__RESULT_BACKEND: db+postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@pgbouncer:${PGBOUNCER_PORT}/${POSTGRES_DB}
     

任务排队不执行的排查步骤 #

1. 确认所有Airflow服务的数据库连接均指向PgBouncer，保证Scheduler能正常读取任务、Worker能获取任务并写入结果
2. 查看Airflow Worker日志，排查是否存在数据库连接错误或Celery相关报错
3. 验证PgBouncer的pool_mode是否为transaction，session模式会导致连接长时间占用，阻碍任务执行
4. 检查PostgreSQL的实时连接数（执行SELECT count(*) FROM pg_stat_activity;），确认PgBouncer是否有效减少了后端连接
5. 确保PGBOUNCER_IGNORE_STARTUP_PARAMETERS包含extra_float_digits（已配置），避免SQLAlchemy连接时的参数冲突

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