Faith & Code | Reflections by Errong

如何修复 PostgreSQL “剩余连接槽位保留给超级用户” 错误 ❌ 错误： 您的应用可能会出现如下报错： remaining connection slots are reserved for roles with the SUPERUSER attribute 这通常意味着 PostgreSQL 数据库的连接数已达到上限，部分连接可能是空闲事务会话，占用了可用槽位。 问题分析 PostgreSQL 通过 max_connections 限制并发连接数量。当所有普通槽位被占满时，只有超级用户可以连接。应用中长时间运行或空闲事务会话（ idle-in-transaction ）会占用连接，阻止新连接建立。 检查当前连接 ℹ️ 提示： 在终止任何连接之前，请先检查当前连接状态。 SELECT pid, usename, application_name, state, backend_start FROM pg_stat_activity ORDER BY backend_start; 注意查找 state 为 idle in transaction 的应用用户连接。 终止过期的空闲事务连接 ⚠️ 警告： 仅终止应用用户连接。不要操作 postgres 系统用户或后台工作进程。 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND usename = 'your_app_user' AND now() - backend_start > interval '5 minutes'; 将 your_app_user 替换为应用使用的数据库用户名。此操作可以安全释放连接而不会影响正在执行的查询。 注意事项 ❌ 不要终止系统连接（如 TimescaleDB 工作进程、pg_cron 等） ✅...

How to Fix PostgreSQL ‘Remaining Connection Slots Reserved’ Error If your application suddenly fails with an error like: remaining connection slots are reserved for roles with the SUPERUSER attribute It usually means your PostgreSQL database has reached its maximum number of allowed connections. Some of these connections may be idle-in-transaction sessions that are holding slots unnecessarily. This guide shows how to safely free up those connections. Understanding the Problem PostgreSQL limits the total number of concurrent connections through the max_connections setting. When all normal slots are used, only superusers can connect. Long-running or idle-in-transaction sessions from your app can consume slots and prevent new connections. Check Current Connections Before terminating anything, inspect your connections with: SELECT pid, usename, application_name, state, backend_start FROM pg_stat_activity ORDER BY backend_start; Look f...