Docker+Nginx部署多实例Dash应用时关闭PostgreSQL连接

我通过Docker部署搭载Nginx的Dash应用，配置运行8个相同仪表盘的独立实例，每个实例可根据用户输入从PostgreSQL数据库获取数据。

但打开多个实例后，数据库连接会处于Idle、ROLLBACK或Idle、Commit状态，即便关闭仪表盘标签页也不会释放。此外，仪表盘的自动刷新机制会持续产生新连接，导致连接堆积。

我希望实现仪表盘标签页关闭时自动关闭所有相关连接。尝试过websocket方案无效，使用SQLAlchemy的scoped_session也未解决问题。手动停止代码（终端Ctrl+C）时，引擎会被释放，连接也会关闭，但无法实现自动处理。

以下是相关代码片段：

data.fetch_db_data.py #

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, scoped_session
import datetime
import pytz
import pandas as pd
import atexit
import signal
import sys

load_dotenv()

uk_dashboard_username = os.getenv("uk_dashboard_username")
uk_dashboard_password = os.getenv("uk_dashboard_password")
uk_dashboard_port = os.getenv("uk_dashboard_port")
uk_dashboard__ip_address = os.getenv("uk_dashboard_ip_address")
uk_dashboard_db_name = os.getenv("uk_dashboard_db_name")

connection_string = (
    f"postgresql://{uk_dashboard_username}:{uk_dashboard_password}@{uk_dashboard__ip_address}:{uk_dashboard_port}/{uk_dashboard_db_name}"
)

engine = create_engine(
    connection_string,
    pool_size=5,          # Max number of connections
    max_overflow=5,        # Extra connections allowed if pool is full
    pool_recycle=30,     # Recycle connections every 30 minutes
    pool_pre_ping=True,     # Check if the connection is alive before using it
    pool_timeout=60
)
SessionLocal = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=False, expire_on_commit=True))

utc = pytz.utc
amsterdam_tz = pytz.timezone("Europe/Amsterdam")

def shutdown():
    print("Disposing of database engine...")
    SessionLocal.remove()
    engine.dispose()

    # Extra cleanup for Gunicorn workers
    try:
        import os
        worker_id = os.getpid()
        print(f"Shutting down worker {worker_id}...")
    except Exception:
        pass  # Just in case there's an error

atexit.register(shutdown)
signal.signal(signal.SIGTERM, lambda signum, frame: shutdown() or sys.exit(0))
signal.signal(signal.SIGINT, lambda signum, frame: shutdown() or sys.exit(0))

# Context manager for safe session handling
from contextlib import contextmanager

@contextmanager
def get_session():
    session = SessionLocal()
    try:
        yield session
        session.commit()  # Ensure transactions are committed
    except Exception as e:
        session.rollback()  # Prevents lingering ROLLBACK connections
        raise e
    finally:
        session.close()  # Closes session properly

def fetch_data(date:datetime.datetime = datetime.datetime.today().date()):

    # Create localized datetime objects for D-00:00  and D+1-23:59 in Amsterdam time
    start_amsterdam = amsterdam_tz.localize(
        datetime.datetime.combine(date, datetime.datetime.min.time())
    )
    end_amsterdam = amsterdam_tz.localize(
        datetime.datetime.combine(date+datetime.timedelta(days=1), datetime.datetime.max.time())
    )

    # Convert to UTC
    start_utc = start_amsterdam.astimezone(utc)
    end_utc = end_amsterdam.astimezone(utc)

    query=text("""
        SELECT data
        FROM table
        WHERE delivery_start_utc BETWEEN :start_utc AND :end_utc
        ;
    """)

    # Fetch data with proper session management
    with SessionLocal() as session:
        df = pd.read_sql_query(
            query,
            session.bind,  # Use the session's connection
            params={"start_utc": start_utc, "end_utc": end_utc},
        )

    return df

run_app.py #

import dash
from layouts.main_layout import global_layout
from data.fetch_db_data import shutdown

app = dash.Dash(__name__)

app.layout=global_layout

server=app.server

from callbacks.update_data_callbacks import *
from callbacks.update_charts_callbacks import *
from callbacks.style_callbacks import *
from callbacks.timer_callbacks import *
from callbacks.input_callbacks import *

if __name__ == "__main__":
    try:
        app.run_server(host="0.0.0.0", port=8050, debug=False)
    finally:
        shutdown()  # Cleanup on full server exit

docker-compose.yml #

version: '3.8'

services:
  dash:
    build: .
    deploy:
      replicas: 8  # Runs 8 separate Dash instances
    restart: always
    volumes:
      - .:/app
    env_file:
      - .env
    environment:
      - ENV=production
    command: ["gunicorn", "-w", "4", "--threads", "4", "-k", "gevent", "-b", "0.0.0.0:8096", "uk_dash_app:server"]
    expose:
      - "8096"

  nginx_uk_dashboard:
    image: nginx:latest
    container_name: nginx_uk_dashboard
    ports:
      - "6969:6969"
    volumes:
      - ./nginx.conf:/etc/nginx/conf.d/default.conf:ro
    depends_on:
      - dash

Dockerfile #

# Use an official Python runtime as a parent image
FROM python:3.12-slim

# Install system dependencies needed for PostgreSQL and compiling Python packages
RUN apt-get update && apt-get install -y libpq-dev gcc && rm -rf /var/lib/apt/lists/*

# Set the working directory in the container
WORKDIR /app

# Copy the application files into the container
COPY . /app

# Install dependencies
RUN pip install --no-cache-dir --upgrade pip && \
    pip install -r requirements.txt

# Expose the port the app runs on
EXPOSE 8096

nginx.conf #

upstream dash_app_cluster {
    least_conn ;  # ✅ Sends requests to the least busy instance

    server dash:8096 max_fails=3 fail_timeout=5s;
}

server {
    listen 6969;

    client_max_body_size 10M;

    location / {
        proxy_pass http://dash_app_cluster;
        proxy_http_version 1.1;
        proxy_set_header Connection "";
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

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