PostgreSQL『connection limit exceeded for non-superusers』の原因と対処

FATAL:  connection limit exceeded for non-superusers
FATAL:  remaining connection slots are reserved for non-replication superuser connections

-- postgresql.conf（またはパラメータグループ）で予約枠を設定
superuser_reserved_connections = 3   -- 例：管理用に3スロット確保
-- ↑反映には再起動が必要（クラウドは再起動/適用が別途必要）

-- いまログインできるなら、不要接続を終了して枠を空ける
SELECT pid, usename, application_name, state, backend_start
FROM pg_stat_activity
ORDER BY backend_start;

-- 安全に切ってよい候補（例：長時間 idle だが tx でない）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state = 'idle'
  AND backend_type = 'client backend'
  AND usename IN ('appuser');  -- 影響範囲を限定

-- 総数・ユーザー別・アプリ別
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state LIKE 'idle%') AS idle
FROM pg_stat_activity;

SELECT usename, count(*)
FROM pg_stat_activity GROUP BY usename ORDER BY 2 DESC;

SELECT application_name, count(*)
FROM pg_stat_activity GROUP BY application_name ORDER BY 2 DESC;

-- 役割/DBごとの接続上限（-1は無制限）
SELECT rolname, rolconnlimit FROM pg_roles ORDER BY rolname;
SELECT datname, datconnlimit FROM pg_database ORDER BY datname;

-- 現在の上限と予約枠
SHOW max_connections;
SHOW superuser_reserved_connections;

-- 役割（ユーザー）単位の上限を見直す
ALTER ROLE appuser CONNECTION LIMIT 50;  -- -1 は無制限

-- データベース単位の上限
ALTER DATABASE mydb CONNECTION LIMIT 200;

-- 一時的に解放する（該当ロール/DBの不要接続を終了）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'appuser'  -- または datname='mydb'
  AND pid <> pg_backend_pid();

# 例：Java/HikariCP
maximumPoolSize=30
minimumIdle=5
idleTimeout=300000
# → DB側の max_connections から管理/他サービス/バッチ/メンテ枠を差し引き、
#    合算で超えないよう各アプリのプール上限を配分

# 例：Node.js (pg-pool)
new Pool({ max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000 });

# 例：Ruby on Rails
pool: 10  # DB1台に対しアプリプロセス総数×pool <= 設計上限

# PgBouncer（transaction poolingの例）
[databases]
mydb = host=127.0.0.1 dbname=mydb

[pgbouncer]
pool_mode = transaction     ; tx単位でサーバ接続を使い回す
max_client_conn = 2000      ; クライアント総数
default_pool_size = 50      ; DBへの実接続上限（合算設計の要）
server_idle_timeout = 30s
server_reset_query = DISCARD ALL

-- 取引放置の自動切断
SET idle_in_transaction_session_timeout = '5min';   -- postgresql.confでも可

-- クライアント側のソケット生存確認（OS/Driver設定）
-- tcp_keepalives_idle / tcp_keepalives_interval / tcp_keepalives_count を適切化
SHOW tcp_keepalives_idle;

# postgresql.conf（一例）
max_connections = 300
superuser_reserved_connections = 3

-- 複製送信枠
SHOW max_wal_senders;

-- バックグラウンドワーカーの存在（参考）
SELECT * FROM pg_stat_activity
WHERE backend_type NOT IN ('client backend');

-- 設定反映は「パラメータグループ」や「DBフラグ」を変更し、適用/再起動が必要
-- 監視はpg_stat_activityに加え、サービス固有メトリクス（接続数/エラー）を併用
-- メンテナンスウィンドウ中の自動フェイルオーバーで接続嵐が起きないよう、
-- クライアント側の接続リトライ/バックオフを実装

-- 1) 長時間 idle のクライアント接続候補（30分以上アイドル）
SELECT pid, usename, application_name, client_addr, state, now()-state_change AS idle_for
FROM pg_stat_activity
WHERE state = 'idle' AND now()-state_change > interval '30 minutes'
ORDER BY idle_for DESC;

-- 2) アプリ特定（例：古いリビジョン）だけを終了
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'myapp-old'
  AND state LIKE 'idle%';

-- 3) 自分自身/重要バックグラウンドを除外
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND backend_type = 'client backend'
  AND state LIKE 'idle%';

-- 準備：小さめの上限（テスト用）
ALTER SYSTEM SET max_connections = 20;
ALTER SYSTEM SET superuser_reserved_connections = 2;
SELECT pg_reload_conf();  -- サービスによっては再起動が必要

-- 疑似的に大量接続（別端末/pgbench -C など）を発生させると…
-- 一般ユーザーでの新規接続が失敗：
-- FATAL: connection limit exceeded for non-superusers

-- 対処1：不要な idle を解放
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state='idle' AND backend_type='client backend' AND pid<>pg_backend_pid();

-- 対処2：役割上限の見直し（必要な場合）
ALTER ROLE appuser CONNECTION LIMIT 30;

-- 対処3：PgBouncer（transaction pooling）経由に切替
-- （default_pool_size を 20程度に設定し、DB実接続を抑制）