数据库查询最佳实践 - 防止连接池耗尽

关键技能 - 在查询 Railway 生产数据库前请务必阅读

问题描述

在本地开发环境中查询 Railway 生产数据库时，可能会遇到以下错误：

psycopg2.OperationalError: sorry, too many clients already

出现该问题的原因包括：

• 多个本地开发服务器正在运行（npm run dev, npm run develop）
• 每个开发服务器都保持数据库连接打开
• 连接池上限被达到（总共 300 个连接）
• 无法建立新的连接

始终使用此方法

1. 查询前先检查连接池状态

在进行任何数据库查询之前，请先检查当前活动连接数：

python3 << 'EOF'
import psycopg2

DATABASE_URL = "postgresql://postgres:password@host:port/railway"

try:
    conn = psycopg2.connect(DATABASE_URL, connect_timeout=10)
    cur = conn.cursor()

    # 检查当前活动连接数
    cur.execute("""
        SELECT count(*)
        FROM pg_stat_activity
        WHERE datname = 'railway'
    """)
    active = cur.fetchone()[0]

    # 检查连接池上限
    cur.execute("SHOW max_connections")
    max_conn = cur.fetchone()[0]

    print(f"当前活动连接数: {active}/{max_conn}")

    if active > int(max_conn) * 0.9:
        print(f"警告: 连接池已使用 {(active/int(max_conn))*100:.1f}%")
        print("建议在查询前关闭本地开发服务器")
    else:
        print("连接池状态健康 - 可安全执行查询")

    cur.close()
    conn.close()

except Exception as e:
    print(f"无法连接: {e}")
    print("\n解决方案:")
    print("1. 终止本地开发服务器: pkill -9 -f 'npm run dev'")
    print("2. 等待 30 秒以让连接释放")
    print("3. 重新尝试查询")
EOF

2. 始终使用上下文管理器

切勿这样做：

conn = psycopg2.connect(DATABASE_URL)
cur = conn.cursor()
cur.execute("SELECT * FROM users")
# 忘记关闭！导致连接泄漏！

必须这样做：

import psycopg2

DATABASE_URL = "postgresql://..."

try:
    conn = psycopg2.connect(DATABASE_URL, connect_timeout=30)
    cur = conn.cursor()

    # 执行查询
    cur.execute("SELECT * FROM users")
    results = cur.fetchall()

    # 处理结果...

finally:
    # 总是在 finally 块中关闭连接
    if cur:
        cur.close()
    if conn:
        conn.close()

3. 使用短生命周期连接

对于一次性查询，应立即打开、执行、关闭连接：

def get_user_count():
    """获取用户数量 - 连接在函数内打开并立即关闭"""
    conn = None
    try:
        conn = psycopg2.connect(DATABASE_URL, connect_timeout=30)
        cur = conn.cursor()

        cur.execute("SELECT COUNT(*) FROM users")
        count = cur.fetchone()[0]

        cur.close()
        return count

    finally:
        if conn:
            conn.close()  # 连接立即释放

# 正确：函数返回后连接已关闭
user_count = get_user_count()

4. 查询前停止开发服务器

如需执行数据库查询，请先停止所有本地开发服务器：

# 停止所有开发服务器
pkill -9 -f "npm run dev"
pkill -9 -f "npm run develop"

# 等待连接关闭
sleep 10

# 现在可以安全查询
python3 scripts/your_query_script.py

# 查询完成后重启开发服务器
cd src/backend && npm run dev &
cd AINative-website && npm run dev &

5. 优先使用 Railway CLI 执行查询（推荐）

尽可能避免使用 Python 脚本，改用 Railway CLI：

# 登录 Railway
railway login

# 关联项目
railway link

# 直接执行 SQL 查询
railway run psql -c "SELECT COUNT(*) FROM users WHERE created_at >= NOW() - INTERVAL '30 days'"

该方式使用 Railway 管理的连接，不会占用本地连接池。

紧急情况：连接池已满

若遇到“too many clients already”错误：

方法一：终止本地开发服务器

pkill -9 -f "npm run dev"
pkill -9 -f "npm run develop"
pkill -9 node
sleep 30  # 等待数据库连接释放

方法二：检查 Railway 上的活跃连接

railway run psql -c "
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start
FROM pg_stat_activity
WHERE datname = 'railway'
ORDER BY query_start DESC
LIMIT 20"

方法三：终止空闲连接（最后手段）

railway run psql -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'railway'
  AND state = 'idle'
  AND query_start < NOW() - INTERVAL '10 minutes'"

查询前检查清单

在执行任何数据库查询前，请确认：

• [ ] 是否有本地开发服务器在运行？→ 请先停止
• [ ] 连接池是否健康？→ 请运行连接状态检查
• [ ] 是否使用 try/finally 确保关闭连接？→ 是
• [ ] 是否使用短生命周期连接？→ 是
• [ ] 是否可使用 Railway CLI 替代？→ 优先选择此方式

总结

黄金法则：

查询完成后必须立即关闭数据库连接。

开发服务器运行期间绝不能保持连接开放。

最佳实践流程：

1. 停止开发服务器
2. 执行数据库查询
3. 关闭连接
4. 重启开发服务器

更优方案：

对于临时查询，优先使用 Railway CLI，而非编写 Python 脚本。