#!/bin/bash
# 盈尺（Surinch）PostgreSQL 健康体检评分样例实测 v1.0
# 只能在测试实例上跑：会 DROP/CREATE ${DB}，CREATE EXTENSION pg_stat_statements（若可），造数并制造 idle in transaction / 死元组。
# 测试：PostgreSQL 17.11；库 pg_hc_lab；非客户数据。
# 用法：PSQL="psql" DB=pg_hc_lab WORK=/tmp/pg_hc ./pg-healthcheck-score-lab.sh | tee result.txt
set -uo pipefail
PSQL=${PSQL:-"psql -d postgres"}
DB=${DB:-pg_hc_lab}
WORK=${WORK:-/tmp/pg_hc_lab_work}
case "$DB" in *_lab) ;; *) echo "DB 名必须以 _lab 结尾"; exit 1;; esac
mkdir -p "$WORK"
ts(){ date '+%F %T.%3N'; }
now(){ date +%s.%N; }
ms(){ awk -v a="$1" -v b="$2" 'BEGIN{printf "%.0f", (b-a)*1000}'; }
run(){ local tag=$1; shift; local t0 t1 rc; t0=$(now); "$@" > "$WORK/$tag.out" 2> "$WORK/$tag.err"; rc=$?; t1=$(now); echo "RESULT|$tag|rc=$rc|ms=$(ms $t0 $t1)"; cat "$WORK/$tag.out"; [[ -s "$WORK/$tag.err" ]] && echo "ERR|$tag|$(tr '\n' ' ' < "$WORK/$tag.err")"; }
P(){ $PSQL -v ON_ERROR_STOP=1 "$@"; }
Pd(){ $PSQL -d "$DB" -v ON_ERROR_STOP=1 "$@"; }

echo "[$(ts)] === PostgreSQL healthcheck score lab v1.0 ==="
run version $PSQL -c "SELECT version(); SHOW shared_buffers; SHOW max_connections; SHOW autovacuum; SHOW wal_level; SHOW idle_in_transaction_session_timeout; SHOW checkpoint_timeout;"

echo "[$(ts)] 尝试在 postgres 库启用 pg_stat_statements（共享预加载可能已配置）"
run ext_try $PSQL -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" || true
# Check shared_preload
run preload $PSQL -c "SHOW shared_preload_libraries; SELECT extname FROM pg_extension ORDER BY 1;"

echo "[$(ts)] 建库造数（events 100万）"
$PSQL -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$DB' AND pid <> pg_backend_pid();" >/dev/null 2>&1 || true
run dropdb $PSQL -c "DROP DATABASE IF EXISTS $DB;"
run createdb $PSQL -c "CREATE DATABASE $DB;"
run setup Pd <<SQL
CREATE TABLE events(
  id BIGSERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  status SMALLINT NOT NULL,
  amount INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_events_user ON events(user_id);
CREATE INDEX idx_events_status_created ON events(status, created_at);
INSERT INTO events(user_id, status, amount, created_at)
SELECT 1 + (g % 10000),
       (g % 5)::smallint,
       10 + (g % 900),
       TIMESTAMPTZ '2026-01-01' + ((g % 250) || ' days')::interval
FROM generate_series(1, 1000000) g;
ANALYZE events;
SELECT COUNT(*) AS events, pg_size_pretty(pg_total_relation_size('events')) AS total_size,
       pg_size_pretty(pg_relation_size('events')) AS heap_size,
       pg_size_pretty(pg_indexes_size('events')) AS index_size;
SQL

# Enable extension inside lab DB too (if preload allows)
run ext_lab Pd -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" || true
run reset_stats Pd -c "SELECT pg_stat_statements_reset();" 2>/dev/null || echo "pg_stat_statements_reset unavailable"

echo "[$(ts)] 制造慢查询 + 死元组 + idle in transaction"
# Slow-ish queries
run slow Pd <<SQL
SELECT COUNT(*) FROM events WHERE status=3 AND amount > 500;
SELECT user_id, SUM(amount) FROM events WHERE created_at > TIMESTAMPTZ '2026-06-01' GROUP BY user_id ORDER BY SUM(amount) DESC LIMIT 20;
SELECT pg_sleep(0.25);
SELECT COUNT(*) FROM events e1 WHERE EXISTS (SELECT 1 FROM events e2 WHERE e2.user_id=e1.user_id AND e2.status=4 LIMIT 1);
SQL

# Dead tuples: update 150k rows
run dead_upd Pd -c "UPDATE events SET amount = amount + 1 WHERE id <= 150000; SELECT n_live_tup, n_dead_tup, ROUND(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),2) AS dead_pct FROM pg_stat_user_tables WHERE relname='events';"

# Idle in transaction for ~8s while we snapshot
cat > "$WORK/idle_tx.sql" <<'SQL'
BEGIN;
SELECT COUNT(*) FROM events WHERE id <= 10;
SELECT pg_sleep(8);
COMMIT;
SQL
Pd -f "$WORK/idle_tx.sql" > "$WORK/idle_tx.out" 2>&1 &
IDLEPID=$!
sleep 1.5
run idle_snap Pd -c "
SELECT pid, state, wait_event_type, wait_event,
       EXTRACT(EPOCH FROM (now()-xact_start))::int AS xact_age_s,
       EXTRACT(EPOCH FROM (now()-state_change))::int AS state_age_s,
       LEFT(query,80) AS query
FROM pg_stat_activity
WHERE datname=current_database() AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;
"
wait $IDLEPID || true
echo "IDLE_TX_OUT:"; cat "$WORK/idle_tx.out"

echo "[$(ts)] 体检只读采集"
run collect Pd <<SQL
SELECT 'META' AS section, version() AS version, now() AS collected_at, current_database() AS db, 'TEST_LAB_SYNTHETIC' AS data_class;

SELECT 'CONN' AS section,
  (SELECT count(*) FROM pg_stat_activity) AS total_backends,
  (SELECT count(*) FROM pg_stat_activity WHERE state='active') AS active,
  (SELECT count(*) FROM pg_stat_activity WHERE state='idle') AS idle,
  (SELECT count(*) FROM pg_stat_activity WHERE state='idle in transaction') AS idle_in_tx,
  (SELECT count(*) FROM pg_stat_activity WHERE state='idle in transaction (aborted)') AS idle_in_tx_aborted,
  current_setting('max_connections')::int AS max_connections;

SELECT 'LONG_TX' AS section,
  COALESCE(MAX(EXTRACT(EPOCH FROM (now()-xact_start)))::int,0) AS max_xact_age_s,
  COUNT(*) FILTER (WHERE xact_start IS NOT NULL AND EXTRACT(EPOCH FROM (now()-xact_start))>=30) AS xact_ge_30s
FROM pg_stat_activity
WHERE datname=current_database() AND pid <> pg_backend_pid();

SELECT 'DEAD' AS section, relname, n_live_tup, n_dead_tup,
  ROUND(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),2) AS dead_pct,
  last_autovacuum, last_vacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC NULLS LAST;

SELECT 'BLOAT_ROUGH' AS section, c.relname,
  pg_size_pretty(pg_relation_size(c.oid)) AS heap,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
  s.n_live_tup, s.n_dead_tup,
  CASE WHEN s.n_live_tup+s.n_dead_tup>0
    THEN ROUND(100.0*s.n_dead_tup/(s.n_live_tup+s.n_dead_tup),2) ELSE 0 END AS dead_pct_proxy
FROM pg_class c
JOIN pg_namespace n ON n.oid=c.relnamespace
LEFT JOIN pg_stat_user_tables s ON s.relid=c.oid
WHERE n.nspname='public' AND c.relkind='r';

SELECT 'CHECKPOINTS' AS section,
  num_timed AS checkpoints_timed, num_requested AS checkpoints_req,
  write_time AS checkpoint_write_time, sync_time AS checkpoint_sync_time,
  buffers_written AS buffers_checkpoint
FROM pg_stat_checkpointer;
SELECT 'BGWRITER' AS section, buffers_clean, maxwritten_clean, buffers_alloc FROM pg_stat_bgwriter;

SELECT 'WAL' AS section,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS wal_lsn_bytes_approx,
  pg_current_wal_lsn() AS current_lsn,
  (SELECT count(*) FROM pg_replication_slots) AS replication_slots,
  (SELECT count(*) FROM pg_stat_replication) AS replicas,
  pg_is_in_recovery() AS in_recovery;

SELECT 'SETTINGS' AS section, name, setting, unit
FROM pg_settings
WHERE name IN ('autovacuum','autovacuum_naptime','idle_in_transaction_session_timeout',
               'shared_buffers','max_connections','wal_level','max_wal_size','checkpoint_timeout',
               'checkpoint_completion_target');
SQL

# pg_stat_statements top if available
if Pd -tAc "SELECT 1 FROM pg_stat_statements LIMIT 1" >/dev/null 2>&1; then
  run stmts Pd -c "
  SELECT 'STMTS' AS section, LEFT(query,100) AS query,
    calls, ROUND(total_exec_time::numeric,1) AS total_ms,
    ROUND(mean_exec_time::numeric,1) AS mean_ms,
    ROUND(max_exec_time::numeric,1) AS max_ms,
    rows
  FROM pg_stat_statements
  WHERE dbid=(SELECT oid FROM pg_database WHERE datname=current_database())
  ORDER BY total_exec_time DESC
  LIMIT 10;
  "
  HAS_PGSS=1
else
  echo "pg_stat_statements NOT installed in $DB"
  HAS_PGSS=0
  echo "RESULT|stmts|rc=0|ms=0|skipped=no_extension" > "$WORK/stmts.out"
fi

echo "[$(ts)] 评分"
eval $(Pd -tAc "
SELECT 'TOTAL_BACKENDS='||count(*) FROM pg_stat_activity;
SELECT 'IDLE_IN_TX='||count(*) FROM pg_stat_activity WHERE state='idle in transaction';
SELECT 'MAX_CONN='||current_setting('max_connections');
SELECT 'MAX_XACT_AGE='||COALESCE(MAX(EXTRACT(EPOCH FROM (now()-xact_start)))::int,0) FROM pg_stat_activity WHERE datname=current_database();
SELECT 'DEAD_PCT='||COALESCE(ROUND(100.0*n_dead_tup/NULLIF(n_live_tup+n_dead_tup,0),2),0)
  FROM pg_stat_user_tables WHERE relname='events';
SELECT 'DEAD_TUP='||COALESCE(n_dead_tup,0) FROM pg_stat_user_tables WHERE relname='events';
SELECT 'REPL_SLOTS='||count(*) FROM pg_replication_slots;
SELECT 'REPLICAS='||count(*) FROM pg_stat_replication;
SELECT 'AUTOVACUUM='||current_setting('autovacuum');
SELECT 'IDLE_TIMEOUT='||current_setting('idle_in_transaction_session_timeout');
")

MEAN_MAX=0
if [[ "$HAS_PGSS" == "1" ]]; then
  MEAN_MAX=$(Pd -tAc "SELECT COALESCE(ROUND(MAX(mean_exec_time)::numeric,1),0) FROM pg_stat_statements WHERE dbid=(SELECT oid FROM pg_database WHERE datname=current_database())")
fi
CONN_PCT=$(awk -v c="$TOTAL_BACKENDS" -v m="$MAX_CONN" 'BEGIN{if(m>0) printf "%.1f", 100*c/m; else print 0}')

S_CONN=$(awk -v p="$CONN_PCT" -v i="$IDLE_IN_TX" 'BEGIN{ if(p>=90||i>=20) print "RED"; else if(p>=70||i>=1) print "YELLOW"; else print "GREEN"; }')
S_AV=$(awk -v d="$DEAD_PCT" -v av="$AUTOVACUUM" 'BEGIN{ if(av!="on"||d+0>=40) print "RED"; else if(d+0>=10) print "YELLOW"; else print "GREEN"; }')
S_BLOAT=$(awk -v d="$DEAD_PCT" 'BEGIN{ if(d+0>=40) print "RED"; else if(d+0>=10) print "YELLOW"; else print "GREEN"; }')
if [[ "$HAS_PGSS" == "1" ]]; then
  S_SLOW=$(awk -v m="$MEAN_MAX" 'BEGIN{ if(m+0>=5000) print "RED"; else if(m+0>=1000) print "YELLOW"; else print "GREEN"; }')
else
  S_SLOW="N/A"
fi
if [[ "$REPLICAS" == "0" && "$REPL_SLOTS" == "0" ]]; then
  S_REPL="N/A"
else
  S_REPL="GREEN"
fi
# Checkpoint: just informational green for lab unless checkpoints_req dominates — pull numbers
CKPT_REQ=$(Pd -tAc "SELECT num_requested FROM pg_stat_checkpointer")
CKPT_TIMED=$(Pd -tAc "SELECT num_timed FROM pg_stat_checkpointer")
S_CKPT=$(awk -v r="$CKPT_REQ" -v t="$CKPT_TIMED" 'BEGIN{ if(t+0==0 && r+0>10) print "YELLOW"; else if(r+0>t+0 && r+0>20) print "YELLOW"; else print "GREEN"; }')

OVERALL=$(awk -v a="$S_CONN,$S_AV,$S_BLOAT,$S_SLOW,$S_CKPT,$S_REPL" 'BEGIN{
  n=split(a,x,","); w="GREEN";
  for(i=1;i<=n;i++){ if(x[i]=="N/A") continue; if(x[i]=="RED") w="RED"; else if(x[i]=="YELLOW" && w!="RED") w="YELLOW"; }
  print w;
}')

cat > "$WORK/score_report.md" <<REP
# PostgreSQL 健康体检样例报告（测试库 · 自造数据）

- 采集时间：$(date '+%F %T %Z')
- 引擎：PostgreSQL 17.11（见 VERSION 段）
- 库：${DB}（events 100 万行）
- 数据类别：TEST_LAB_SYNTHETIC（非客户数据）
- pg_stat_statements：$([[ "$HAS_PGSS" == "1" ]] && echo 已启用 || echo 未启用，慢查询维度 N/A)
- 复制：replicas=${REPLICAS}，slots=${REPL_SLOTS} → $([[ "$S_REPL" == "N/A" ]] && echo 未测 || echo 已测)

## 等级规则（本样例口径）

| 维度 | 绿 | 黄 | 红 |
|---|---|---|---|
| 连接 / idle in transaction | 连接占用 <70% 且无 idle in tx | 占用≥70% 或存在 idle in tx | 占用≥90% 或 idle in tx≥20 |
| autovacuum / 死元组 | autovacuum=on 且 dead%<10 | dead%≥10 | autovacuum 关闭或 dead%≥40 |
| 膨胀粗估 | 以 dead% 作代理，同死元组 | 同左 | 同左（正式体检另看 bloat 查询） |
| 慢查询 pg_stat_statements | mean 最大 <1000ms | ≥1000ms | ≥5000ms；**扩展未装则 N/A** |
| 复制槽 / WAL | 无从库/槽则 N/A；有则槽不异常堆积 | 槽落后 | 槽失效/磁盘危急 |
| checkpoint | 以 timed 为主 | req 明显高于 timed | （本样例不轻易标红） |

## 实测结果

| 维度 | 关键数字 | 等级 |
|---|---|---|
| 连接 / idle in tx | backends=${TOTAL_BACKENDS}/${MAX_CONN}（${CONN_PCT}%）；采集时 idle_in_tx=${IDLE_IN_TX}（制造过程中曾有 ~8s idle in transaction） | ${S_CONN} |
| autovacuum / 死元组 | autovacuum=${AUTOVACUUM}；events dead_tup=${DEAD_TUP}，dead_pct=${DEAD_PCT}%；idle_in_transaction_session_timeout=${IDLE_TIMEOUT} | ${S_AV} |
| 膨胀粗估 | 同 dead_pct=${DEAD_PCT}%（代理指标） | ${S_BLOAT} |
| 慢查询 | pgss=$([[ $HAS_PGSS == 1 ]] && echo yes || echo no)；max mean_exec_time≈${MEAN_MAX}ms | ${S_SLOW} |
| 复制槽 / WAL | replicas=${REPLICAS}，slots=${REPL_SLOTS}，wal_level 见 SETTINGS | ${S_REPL} |
| checkpoint | timed=${CKPT_TIMED}，req=${CKPT_REQ} | ${S_CKPT} |

**总体等级：${OVERALL}**（取已评分维度最差；N/A 不参与）

## 说明

1. 本报告用于展示「只读采集 → 绿/黄/红」的交付形态，不是任何客户环境结论。
2. 先 UPDATE 15 万行制造死元组，再开 8 秒事务做 idle in transaction 快照；评分以采集瞬间为准。
3. 若 shared_preload_libraries 未含 pg_stat_statements，扩展无法创建——文章会写明，正式体检材料清单会要求先装好再采集慢查询段。
4. 正式 ¥499 体检输出 P0/P1/P2、证据缺口与整改 Top5，人工复核备份恢复与权限。
REP

echo "==== SCORE_REPORT_BEGIN ===="
cat "$WORK/score_report.md"
echo "==== SCORE_REPORT_END ===="
echo "SCORE_JSON|conn=${S_CONN}|av=${S_AV}|bloat=${S_BLOAT}|slow=${S_SLOW}|repl=${S_REPL}|ckpt=${S_CKPT}|overall=${OVERALL}|backends=${TOTAL_BACKENDS}|max_conn=${MAX_CONN}|conn_pct=${CONN_PCT}|idle_in_tx=${IDLE_IN_TX}|dead_pct=${DEAD_PCT}|dead_tup=${DEAD_TUP}|mean_max_ms=${MEAN_MAX}|has_pgss=${HAS_PGSS}|replicas=${REPLICAS}|slots=${REPL_SLOTS}|ckpt_timed=${CKPT_TIMED}|ckpt_req=${CKPT_REQ}|autovacuum=${AUTOVACUUM}|idle_timeout=${IDLE_TIMEOUT}"
echo "[$(ts)] DONE"