PostgreSQL「server closed the connection unexpectedly」の原因と安全な解決手順【恒久対策付き】

# psql
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

# libpq/各種アプリ
FATAL:  server closed the connection unexpectedly
error: server closed the connection unexpectedly

# 併発しがちな関連メッセージ（サーバ側ログ）
FATAL:  terminating connection due to administrator command
FATAL:  the database system is in recovery mode
LOG:    server process (PID XXXX) was terminated by signal 9: Killed by OOM Killer
LOG:    server process (PID XXXX) was terminated by signal 11: Segmentation fault

1) サーバ異常終了/再起動：クラッシュ、OOM、kill、OS再起動、コンテナ再作成
2) 管理者・ジョブ・プール/プロキシによる切断：pg_terminate_backend、PgBouncer、監視ツール
3) ネットワーク/ロードバランサ/ファイアウォール：アイドル/非アクティブ切断、NATタイムアウト
4) タイムアウト設定：idle_in_transaction_session_timeout、client_idle_timeout(PgBouncer) 等
5) 拡張/プラグイン/ドライバの不具合：shared_preload_libraries、古い拡張
6) 資源枯渇/設定ミス：ファイルディスクリプタ上限、max_connections過多、メモリ不足

sudo systemctl status postgresql
sudo journalctl -u postgresql -n 200 --no-pager

# クラッシュ/再起動サイン（データディレクトリのログ）
tail -n 200 $PGDATA/log/* 2>/dev/null || tail -n 200 $PGDATA/server.log 2>/dev/null

# OOMやカーネルイベント
dmesg -T | egrep -i 'out of memory|killed process|oom|segfault' | tail -n 50

# 稼働確認
pg_isready -h 127.0.0.1 -p 5432

# 典型ログの読み方
LOG:  server process (PID 1234) was terminated by signal 9: Killed
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing

# 対処ポイント
- OOM発生 → メモリ監視導入、work_mem/maintenance_work_mem/並列度の見直し
- 重いクエリ/巨大ソート/巨大集計 → 実行計画の見直し、インデックス、LIMITと分割バッチ
- 拡張クラッシュ → 拡張のアップデート/一時無効化、core dump解析
- OS/コンテナ再起動 → 自動フェイルオーバ/再接続戦略の導入

# サーバログ例
FATAL:  terminating connection due to administrator command
LOG:    disconnection: session time: 120.001...  (log_disconnections=on の場合)
ERROR:  terminating connection due to idle-in-transaction timeout

# よくある要因
- 運用ジョブが定期的に pg_terminate_backend() を実行
- PgBouncer の client_idle_timeout/server_idle_timeout/query_timeout
- 監視ツールやキルスクリプトの誤作動

# 対処
- ジョブ/スクリプトの見直し、除外条件・タグ付け
- PgBouncer のタイムアウト値調整とアプリ側のトランザクション設計見直し

# 兆候
- 一定時間アイドル後に必ず切れる（LBやFWのidle timeout）
- VPN/クラウドLB/ALB/NLB越しのみで発生
- クライアント側に "server closed..."、サーバログに痕跡が薄い

# 対処（クライアント/サーバ双方でKeepalive）
# 例：接続文字列にKeepalive設定（libpq互換）
postgresql://user:pass@host:5432/db?keepalives=1&keepalives_idle=60&keepalives_interval=30&keepalives_count=5

# サーバ側 postgresql.conf（秒）
tcp_keepalives_idle = 60
tcp_keepalives_interval = 30
tcp_keepalives_count = 5

# LB側：idle timeout をアプリ要件に合わせて延長（例えば 350〜600 秒など）

# サーバ/セッションが意図的に切断する代表例
idle_in_transaction_session_timeout = '5min'   -- 取引放置の自動切断
statement_timeout = '2min'                     -- 長時間クエリのキャンセル（切断ではなくエラー返却）
lock_timeout = '5s'

# 現行値確認（セッション内）
SHOW idle_in_transaction_session_timeout;
SHOW statement_timeout;

# セッション単位で緩和
SET idle_in_transaction_session_timeout = '0';  -- 無効化（推奨は適切値の設定）

# 共有ライブラリの確認
SHOW shared_preload_libraries;

# 一時無効化（再起動が必要）
# postgresql.conf から該当拡張を外す → 再起動 → 再発有無を確認

# ドライバ側（例：Node.js pg / JDBC / psycopg）を最新化
# known issue があるバージョンはアップグレード

# 接続上限
SHOW max_connections;
SHOW superuser_reserved_connections;

# OSのFD上限
ulimit -n
cat /proc/sys/fs/file-max

# メモリ利用の観測（例）
ps -o pid,rss,cmd -u postgres | sort -k2 -nr | head
free -h

# postgresql.conf（代表例）
log_line_prefix = '%m [%p] %u@%d %h '
log_min_messages = info
log_min_error_statement = error
log_connections = on
log_disconnections = on
log_statement = 'none'             -- 本番はサンプリング/条件付き推奨
log_lock_waits = on
shared_preload_libraries = 'auto_explain'      -- 必要に応じて
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = on

# 一時的にコアダンプを有効化（Linux）
sudo sysctl -w kernel.core_pattern=/tmp/core.%e.%p.%t
ulimit -c unlimited

# クラッシュ後
gdb /usr/lib/postgresql/16/bin/postgres /tmp/core.postgres.1234.XXXX -batch -ex "bt full"
# スタックトレースをベンダや拡張作者に提示

// 疑似コード：トランザクション境界でのみリトライ
for attempt in 1..5 {
  conn = pool.get()
  try {
    conn.begin()
    do_work(conn)        # idempotentに設計
    conn.commit()
    break
  } catch (e) {
    conn.rollback()
    if (isTransient(e)) {
      sleep(backoff(attempt))  # 200ms, 400ms, 800ms...
      continue
    }
    throw e
  } finally {
    conn.close()
  }
}

-- 接続断が多いアプリ/ホストを把握（log_disconnections=on 前提でログ解析が基本）
SELECT client_addr, application_name, count(*) AS disconnects
FROM pg_stat_activity  -- 参考：継続的監視は外部で
GROUP BY 1,2;

-- 現在の長時間トランザクション/アイドルinトランザクション
SELECT pid, usename, application_name, state, xact_start, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_start NULLS LAST;

# libpq 互換（環境変数）
export PGOPTIONS=""
export PGCONNECT_TIMEOUT=10
# 接続文字列
postgresql://u:p@host:5432/db?connect_timeout=10&keepalives=1&keepalives_idle=60&keepalives_interval=30&keepalives_count=5

# psycopg (Python)
psycopg.connect(
  "dbname=db user=u password=p host=host port=5432",
  connect_timeout=10, keepalives=1, keepalives_idle=60, keepalives_interval=30, keepalives_count=5
)

# Node pg
new Pool({
  connectionString: process.env.DSN,
  statement_timeout: 0,
  query_timeout: 0,
  connectionTimeoutMillis: 10000,
  keepAlive: true,
  keepAliveInitialDelayMillis: 60000
})

- サーバログの粒度を適正化（log_disconnections, auto_explain）
- LB/FWのidle timeout と Keepalive の整合性
- PgBouncer 等とのタイムアウト整備（client/server_idle_timeout）
- メモリ監視とOOMアラート、重いクエリの継続的観測
- 拡張/ドライバの定期アップデート
- 安全なリトライ・接続再確立の実装（トランザクション境界でのみ）
- 本番での強制 pg_terminate_backend の乱用禁止

#!/usr/bin/env bash
# server-closed-conn triage (Linux)
set -euo pipefail
SVC=postgresql
echo "[*] service status"; systemctl is-active --quiet $SVC || echo "inactive"
echo "[*] recent journal"; journalctl -u $SVC -n 100 --no-pager | tail -n 50
echo "[*] postgres logs"; tail -n 100 $PGDATA/log/* 2>/dev/null || true
echo "[*] kernel OOM/segfault"; dmesg -T | egrep -i 'oom|out of memory|segfault|killed process' | tail -n 50
echo "[*] active long xacts"; psql -Atqc "
  SELECT pid, state, now()-xact_start AS xact_age, client_addr, application_name
  FROM pg_stat_activity
  WHERE xact_start IS NOT NULL
  ORDER BY xact_start LIMIT 10;" || true

# 管理者PowerShell
Get-Service postgresql-x64-16
Get-EventLog -LogName Application -Newest 100 | ? {$_.Source -match "PostgreSQL"} | Select TimeGenerated, EntryType, Message
# WinFW/AV製品のタイムアウト・スキャンで切断が起きる例あり → 例外設定を検討