Linux下PostgreSQL數(shù)據(jù)庫(kù)故障的系統(tǒng)化排查Shell腳本分享

更新時(shí)間：2026年07月14日 09:18:48 作者：weixin_30777913

這篇文章主要為大家詳細(xì)介紹了Linux下PostgreSQL數(shù)據(jù)庫(kù)故障系統(tǒng)化診斷的腳本,文中的示例代碼講解詳細(xì),具有一定的借鑒價(jià)值,感興趣的小伙伴可以了解下

前言

本文主要為大家分享一個(gè)Linux原生PostgreSQL(含pgvector向量庫(kù))系統(tǒng)化故障排查腳本

適用：無(wú)Docker、系統(tǒng)直接安裝PostgreSQL，支持pgvector向量擴(kuò)展專(zhuān)項(xiàng)檢測(cè)

用法：

root執(zhí)行: sudo bash pg_vector_fault_diagnosis.sh -D /var/lib/pgsql/15/data -U postgres -p 5432
postgres用戶執(zhí)行: ./pg_vector_fault_diagnosis.sh

特性：分段可單獨(dú)運(yùn)行、自動(dòng)緩存關(guān)鍵參數(shù)、向量索引專(zhuān)項(xiàng)校驗(yàn)、只讀取證不修改數(shù)據(jù)庫(kù)

安全承諾：腳本僅采集日志/狀態(tài)，無(wú)任何自動(dòng)修復(fù)、刪除、修改文件操作

完整代碼

#!/bin/bash
#===============================================================================
# 腳本名稱(chēng): pg_vector_fault_diagnosis.sh
# 
#===============================================================================
set -euo pipefail
# =====================【獨(dú)立可執(zhí)行塊1：全局顏色、緩存、工具函數(shù)、入?yún)⒔馕觥?====================
# 顏色定義 高亮可復(fù)制參數(shù)
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
WHITE='\033[1;37m'
NC='\033[0m'
# 時(shí)間戳、取證緩存目錄
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DIAG_DIR="/tmp/pg_vector_diag_${TIMESTAMP}"
CACHE_FILE="${DIAG_DIR}/pg_param_cache.txt"
RECORD_LOG="${DIAG_DIR}/full_diagnose_record.log"
mkdir -p "$DIAG_DIR"
> "$CACHE_FILE"
> "$RECORD_LOG"
# 全局緩存變量（全腳本復(fù)用，執(zhí)行后可復(fù)制）
PGDATA=""
PGUSER=""
PGPORT=""
POSTMASTER_PID=""
PG_LOG_PATH=""
CLUSTER_STATE=""
VECTOR_EXT_EXIST=false
# 基礎(chǔ)打印工具
now() { date '+%Y-%m-%d %H:%M:%S'; }
log_report() {
    echo "[$(now)] $*"
    echo "[$(now)] $*" >> "$RECORD_LOG"
}
success() { echo -e "${GREEN}[?] $1${NC}"; echo "[?] $1" >> "$RECORD_LOG"; }
warn() { echo -e "${YELLOW}[!] $1${NC}"; echo "[!] $1" >> "$RECORD_LOG"; }
error() { echo -e "${RED}[?] $1${NC}"; echo "[?] $1" >> "$RECORD_LOG"; }
# 打印可復(fù)制參數(shù)并存入緩存文件
print_copy_param() {
    local label="$1"
    local value="$2"
    echo -e "${WHITE}【可復(fù)制參數(shù) | $label】${RED}$value${NC}"
    echo "$label=$value" >> "$CACHE_FILE"
}
# 輸出預(yù)填充參數(shù)的一鍵執(zhí)行命令
quick_run_cmd() {
    echo -e "${YELLOW}?? 一鍵復(fù)制執(zhí)行：$1${NC}"
    echo "快捷命令：$1" >> "$CACHE_FILE"
}
# 默認(rèn)配置
PGDATA="${PGDATA:-/var/lib/pgsql/15/data}"
PGUSER="${PGUSER:-postgres}"
PGPORT="${PGPORT:-5432}"
# 解析命令行參數(shù)
while getopts "D:U:p:" opt; do
    case "$opt" in
        D) PGDATA="$OPTARG" ;;
        U) PGUSER="$OPTARG" ;;
        p) PGPORT="$OPTARG" ;;
        *) echo "用法: $0 [-D 數(shù)據(jù)目錄](méi) [-U 數(shù)據(jù)庫(kù)用戶] [-p 端口]" >&2; exit 1 ;;
    esac
done
# 緩存基礎(chǔ)入?yún)?
print_copy_param "PG數(shù)據(jù)目錄PGDATA" "$PGDATA"
print_copy_param "數(shù)據(jù)庫(kù)運(yùn)行用戶PGUSER" "$PGUSER"
print_copy_param "數(shù)據(jù)庫(kù)監(jiān)聽(tīng)端口PGPORT" "$PGPORT"
# 自動(dòng)切換postgres用戶執(zhí)行核心檢測(cè)
if [[ $(id -un) != "$PGUSER" ]]; then
    if id "$PGUSER" &>/dev/null; then
        log_report "自動(dòng)切換至$PGUSER用戶執(zhí)行完整診斷"
        exec su -s /bin/bash "$PGUSER" -c "$0 -D $PGDATA -U $PGUSER -p $PGPORT"
    else
        error "用戶$PGUSER不存在，請(qǐng)手動(dòng)切換postgres用戶運(yùn)行腳本"
        exit 1
    fi
fi
# ==========================================================================================
# =====================【獨(dú)立可執(zhí)行塊2：階段一 本體論：數(shù)據(jù)庫(kù)實(shí)體狀態(tài)全判定】=====================
log_report ""
log_report "========== 階段一：本體論狀態(tài)判定（區(qū)分PG進(jìn)程/集群/端口/鎖文件實(shí)體） =========="
log_report "目標(biāo)：精準(zhǔn)劃分?jǐn)?shù)據(jù)庫(kù)6種故障狀態(tài)，區(qū)分普通崩潰/向量索引卡死/OOM假死"
# 1.1 systemd服務(wù)狀態(tài)
SERVICE_ACTIVE="inactive"
if systemctl list-unit-files | grep -qE "postgresql-[0-9]+\.service"; then
    SERVICE_ACTIVE=$(systemctl is-active postgresql-*.service 2>/dev/null || echo "inactive")
fi
print_copy_param "Systemd服務(wù)狀態(tài)" "$SERVICE_ACTIVE"
# 1.2 postmaster主進(jìn)程PID緩存
POSTMASTER_PID=$(pgrep -f "postgres:.*postmaster" || echo "")
print_copy_param "PostgreSQL主進(jìn)程PID" "$POSTMASTER_PID"
# 1.3 端口監(jiān)聽(tīng)檢測(cè)
PORT_OPEN="no"
if ss -tlnp | grep -q ":$PGPORT "; then
    PORT_OPEN="yes"
fi
print_copy_param "端口$PGPORT監(jiān)聽(tīng)狀態(tài)" "$PORT_OPEN"
# 1.4 數(shù)據(jù)目錄鎖文件檢測(cè)
PID_FILE_EXIST=false
if [[ -f "$PGDATA/postmaster.pid" ]]; then
    PID_FILE_EXIST=true
fi
print_copy_param "殘留postmaster.pid鎖文件" "$PID_FILE_EXIST"
# 1.5 六類(lèi)故障狀態(tài)綜合判定
DB_STATE="unknown"
if [[ -n "$POSTMASTER_PID" && "$PORT_OPEN" == "yes" ]]; then
    DB_STATE="活躍運(yùn)行態(tài)(Active) - 進(jìn)程+端口正常，故障大概率為向量查詢(xún)卡死/慢索引構(gòu)建"
elif [[ -n "$POSTMASTER_PID" ]]; then
    DB_STATE="阻塞假死態(tài)(Dying) - 進(jìn)程存在但端口關(guān)閉，向量?jī)?nèi)存溢出阻塞、等待IO"
elif [[ "$SERVICE_ACTIVE" == "active" && -z "$POSTMASTER_PID" ]]; then
    DB_STATE="僵死態(tài)(Zombie) - systemd顯示運(yùn)行但進(jìn)程消失，磁盤(pán)只讀/權(quán)限崩潰"
elif $PID_FILE_EXIST; then
    DB_STATE="異常崩潰態(tài)(Crashed) - 非正常關(guān)閉，鎖文件殘留，向量索引損壞高發(fā)"
else
    DB_STATE="正常關(guān)閉態(tài)(CleanStop) - 無(wú)進(jìn)程無(wú)鎖文件，人為停止或定時(shí)任務(wù)關(guān)閉"
fi
log_report "數(shù)據(jù)庫(kù)綜合判定狀態(tài)：$DB_STATE"
log_report "狀態(tài)明細(xì)：systemd=$SERVICE_ACTIVE PID=$POSTMASTER_PID 端口監(jiān)聽(tīng)=$PORT_OPEN 鎖文件=$PID_FILE_EXIST"
# 對(duì)應(yīng)狀態(tài)快捷排查命令
if [[ "$DB_STATE" == "活躍運(yùn)行態(tài)"* ]]; then
    quick_run_cmd "psql -U $PGUSER -p $PGPORT -c 'SELECT pid,state,wait_event,query FROM pg_stat_activity;'"
elif [[ "$DB_STATE" == "阻塞假死態(tài)"* ]]; then
    quick_run_cmd "kill -ABRT $POSTMASTER_PID; journalctl -u postgresql-* -n 200"
elif [[ "$DB_STATE" == "異常崩潰態(tài)"* ]]; then
    quick_run_cmd "pg_controldata $PGDATA"
fi
# ==========================================================================================
# =====================【獨(dú)立可執(zhí)行塊3：階段二 奧卡姆剃刀：日志致命錯(cuò)誤提?。ê琾gvector向量專(zhuān)屬錯(cuò)誤）】=====================
log_report ""
log_report "========== 階段二：日志剃刀導(dǎo)航（包含pgvector向量索引專(zhuān)屬故障關(guān)鍵詞） =========="
# 自動(dòng)定位日志文件
PG_LOG_PATH=""
TODAY_LOG="$PGDATA/log/postgresql-$(date +%a).log"
if [[ -f "$TODAY_LOG" ]]; then
    PG_LOG_PATH="$TODAY_LOG"
else
    PG_LOG_PATH=$(ls -t "$PGDATA"/log/postgresql-*.log 2>/dev/null | head -1 || echo "未找到日志文件")
fi
print_copy_param "PostgreSQL日志路徑" "$PG_LOG_PATH"
# 日志不存在則切換journalctl日志源
if [[ "$PG_LOG_PATH" == "未找到日志文件" || ! -f "$PG_LOG_PATH" ]]; then
    warn "本地日志目錄無(wú)文件，數(shù)據(jù)庫(kù)日志輸出至系統(tǒng)journald"
    quick_run_cmd "journalctl -u postgresql-* --no-pager -n 300 > ${DIAG_DIR}/pg_journal.log"
else
    log_report "采集日志文件：$PG_LOG_PATH"
fi
# 致命錯(cuò)誤關(guān)鍵詞（擴(kuò)展pgvector向量庫(kù)特有報(bào)錯(cuò)）
CRITICAL_PATTERN='PANIC|FATAL|terminating|signal 9|No space left|Input/output error|checkpoint record|vector index|hnsw|ivfflat|pgvector|high dim memory|vector memory exhausted|cannot load index|index corrupt'
declare -a critical_errors
# 讀取最近200行日志匹配錯(cuò)誤
if [[ -f "$PG_LOG_PATH" ]]; then
    while IFS= read -r line; do
        if echo "$line" | grep -qiE "$CRITICAL_PATTERN"; then
            critical_errors+=("$line")
        fi
    done < <(tail -200 "$PG_LOG_PATH" 2>/dev/null)
fi
# 打印提取的崩潰日志
if [[ ${#critical_errors[@]} -eq 0 ]]; then
    success "日志未匹配致命PANIC/FATAL/向量索引錯(cuò)誤，轉(zhuǎn)向系統(tǒng)資源檢查"
    quick_run_cmd "grep -E '$CRITICAL_PATTERN' $PG_LOG_PATH"
else
    error "檢測(cè)到關(guān)鍵故障日志（含向量庫(kù)異常）："
    for err in "${critical_errors[@]}"; do
        echo "    $err"
    done
fi
# 標(biāo)記故障大類(lèi)（含向量專(zhuān)屬故障標(biāo)記）
FOUND_OOM=0
FOUND_DISK_FULL=0
FOUND_IO_ERROR=0
FOUND_WAL_CORRUPT=0
FOUND_LOCK_FILE=0
FOUND_PERMISSION=0
FOUND_VECTOR_INDEX_ERR=0
FOUND_VECTOR_OOM=0
for err in "${critical_errors[@]}"; do
    # 通用PG故障
    if echo "$err" | grep -q "signal 9"; then FOUND_OOM=1; fi
    if echo "$err" | grep -q "No space left"; then FOUND_DISK_FULL=1; fi
    if echo "$err" | grep -q "I/O error"; then FOUND_IO_ERROR=1; fi
    if echo "$err" | grep -q "checkpoint record"; then FOUND_WAL_CORRUPT=1; fi
    if echo "$err" | grep -q "lock file.*exists"; then FOUND_LOCK_FILE=1; fi
    if echo "$err" | grep -q "Permission denied"; then FOUND_PERMISSION=1; fi
    # pgvector向量數(shù)據(jù)庫(kù)專(zhuān)屬故障
    if echo "$err" | grep -qE "vector index|hnsw|ivfflat|index corrupt|cannot load index"; then FOUND_VECTOR_INDEX_ERR=1; fi
    if echo "$err" | grep -qE "vector memory exhausted|high dim memory"; then FOUND_VECTOR_OOM=1; fi
done
# 緩存故障標(biāo)記
print_copy_param "OOM內(nèi)核殺死標(biāo)記" "$FOUND_OOM"
print_copy_param "磁盤(pán)滿標(biāo)記" "$FOUND_DISK_FULL"
print_copy_param "向量索引損壞標(biāo)記" "$FOUND_VECTOR_INDEX_ERR"
print_copy_param "向量查詢(xún)內(nèi)存溢出標(biāo)記" "$FOUND_VECTOR_OOM"
# ==========================================================================================
# =====================【獨(dú)立可執(zhí)行塊4：階段三 辯證法：定向資源檢查（向量?jī)?nèi)存/磁盤(pán)/內(nèi)核OOM）】=====================
log_report ""
log_report "========== 階段三：辯證法定向資源排查（根據(jù)日志錯(cuò)誤只檢查對(duì)應(yīng)維度，減少無(wú)效采集） =========="
# 3.1 磁盤(pán)空間故障定向檢查
if [[ $FOUND_DISK_FULL -eq 1 ]]; then
    error "日志標(biāo)記磁盤(pán)空間耗盡，檢查PG數(shù)據(jù)分區(qū)占用"
    df -h "$PGDATA" || true
    quick_run_cmd "du -sh $PGDATA/* | sort -rh | head -20"
    quick_run_cmd "du -sh $PGDATA/pg_wal"
fi
# 3.2 OOM內(nèi)存殺死定向檢查（向量高維查詢(xún)極易觸發(fā)）
if [[ $FOUND_OOM -eq 1 || $FOUND_VECTOR_OOM -eq 1 ]]; then
    error "日志標(biāo)記內(nèi)存溢出（普通OOM/向量高維查詢(xún)內(nèi)存耗盡），讀取內(nèi)核OOM記錄"
    OOM_DMESG=$(dmesg -T | grep -i "killed process" | tail -10)
    if [[ -n "$OOM_DMESG" ]]; then echo "$OOM_DMESG"; fi
    quick_run_cmd "journalctl -k --no-pager | grep -i oom"
    quick_run_cmd "psql -U $PGUSER -p $PGPORT -c 'show work_mem; show shared_buffers; show max_parallel_workers_per_gather;'"
fi
# 3.3 磁盤(pán)IO硬件故障定向檢查
if [[ $FOUND_IO_ERROR -eq 1 ]]; then
    error "日志檢測(cè)磁盤(pán)IO硬件錯(cuò)誤"
    dmesg | grep -iE "I/O error|ext4 error|remount ro" | tail -10 || true
    mount | grep "$PGDATA" || true
    quick_run_cmd "iostat -x 1 5"
fi
# 3.4 向量索引損壞專(zhuān)項(xiàng)檢查（pgvector獨(dú)有）
if [[ $FOUND_VECTOR_INDEX_ERR -eq 1 ]]; then
    error "檢測(cè)到向量索引損壞/加載失敗，執(zhí)行向量索引健康檢測(cè)"
    log_report "向量索引修復(fù)前置檢查命令（只讀查詢(xún)，無(wú)修改）"
    quick_run_cmd "psql -U $PGUSER -p $PGPORT -c 'SELECT relname,relfilenode,relisreplicated FROM pg_class WHERE relam IN (SELECT oid FROM pg_am WHERE amname IN (''hnsw'',''ivfflat''));'"
    quick_run_cmd "reindex index 向量索引名; -- 停機(jī)后執(zhí)行，修復(fù)損壞索引"
fi
# 3.5 無(wú)明確日志錯(cuò)誤，執(zhí)行極簡(jiǎn)全局資源快照
if [[ $FOUND_OOM -eq 0 && $FOUND_DISK_FULL -eq 0 && $FOUND_IO_ERROR -eq 0 && $FOUND_VECTOR_INDEX_ERR -eq 0 ]]; then
    log_report "無(wú)定向日志錯(cuò)誤，采集基礎(chǔ)系統(tǒng)資源快照"
    df -h "$PGDATA" 2>/dev/null || true
    free -h
    dmesg | grep -i "killed process" | tail -3 2>/dev/null || true
    ulimit -a
    sysctl kernel.shmmax vm.max_map_count
fi
# ==========================================================================================
# =====================【獨(dú)立可執(zhí)行塊5：階段四 整體論：PG集群+pgvector向量擴(kuò)展深度校驗(yàn)】=====================
log_report ""
log_report "========== 階段四：整體論 集群元數(shù)據(jù)&pgvector向量擴(kuò)展校驗(yàn) =========="
# 4.1 集群狀態(tài) pg_controldata
CLUSTER_STATE="未知"
if command -v pg_controldata &>/dev/null && [[ -d "$PGDATA" ]]; then
    CONTROL_RAW=$(pg_controldata "$PGDATA" 2>&1) || true
    CLUSTER_STATE=$(echo "$CONTROL_RAW" | grep "Database cluster state:" | awk -F: '{print $NF}' | xargs)
    log_report "數(shù)據(jù)庫(kù)集群關(guān)閉狀態(tài)：$CLUSTER_STATE"
    print_copy_param "集群關(guān)機(jī)狀態(tài)" "$CLUSTER_STATE"
    if [[ "$CLUSTER_STATE" == *"shut down normally"* ]]; then
        success "上次數(shù)據(jù)庫(kù)正常關(guān)閉"
    elif [[ "$CLUSTER_STATE" == *"crash"* || "$CLUSTER_STATE" == *"recovery"* ]]; then
        warn "數(shù)據(jù)庫(kù)上次異常崩潰，啟動(dòng)將執(zhí)行WAL恢復(fù)"
    fi
else
    warn "無(wú)法讀取pg_controldata，數(shù)據(jù)目錄可能損壞/權(quán)限不足"
fi
# 4.2 數(shù)據(jù)目錄權(quán)限校驗(yàn)
PGDATA_OWNER=$(stat -c '%U:%G' "$PGDATA" 2>/dev/null || echo "未知")
print_copy_param "PGDATA目錄屬主" "$PGDATA_OWNER"
if [[ "$PGDATA_OWNER" != "${PGUSER}:"* ]]; then
    error "數(shù)據(jù)目錄權(quán)限異常，運(yùn)行用戶不匹配"
    quick_run_cmd "sudo chown -R $PGUSER:$PGUSER $PGDATA"
else
    success "數(shù)據(jù)目錄屬主權(quán)限正常"
fi
# 4.3 pgvector向量擴(kuò)展專(zhuān)項(xiàng)檢測(cè)（向量庫(kù)核心）
log_report "檢測(cè)pgvector向量擴(kuò)展安裝狀態(tài)"
VECTOR_EXT_EXIST=false
if psql -U "$PGUSER" -p "$PGPORT" -t -c "SELECT 1 FROM pg_extension WHERE extname='vector';" 2>/dev/null | grep -q 1; then
    VECTOR_EXT_EXIST=true
    success "pgvector向量擴(kuò)展已安裝，支持hnsw/ivfflat向量索引"
    quick_run_cmd "psql -U $PGUSER -p $PGPORT -c '\dx vector'"
else
    warn "未檢測(cè)pgvector擴(kuò)展，向量相關(guān)表/索引會(huì)啟動(dòng)失敗"
    quick_run_cmd "CREATE EXTENSION IF NOT EXISTS vector; -- 數(shù)據(jù)庫(kù)內(nèi)執(zhí)行安裝"
fi
print_copy_param "pgvector擴(kuò)展是否安裝" "$VECTOR_EXT_EXIST"
# 4.4 WAL預(yù)檢查（崩潰恢復(fù)核心）
if [[ $FOUND_WAL_CORRUPT -eq 1 ]]; then
    error "WAL日志檢測(cè)損壞風(fēng)險(xiǎn)，禁止直接啟動(dòng)數(shù)據(jù)庫(kù)"
    log_report "辯證恢復(fù)選擇：1. PITR時(shí)間點(diǎn)恢復(fù)（無(wú)數(shù)據(jù)丟失） 2. pg_resetwal強(qiáng)制重置（丟失最近事務(wù)）"
    quick_run_cmd "sudo -u $PGUSER pg_resetwal -D $PGDATA -f"
fi
# ==========================================================================================
# =====================【獨(dú)立可執(zhí)行塊6：階段五 實(shí)用主義：分層恢復(fù)方案（區(qū)分普通PG/向量庫(kù)故障）】=====================
log_report ""
log_report "========== 階段五：辯證分層恢復(fù)建議（普通數(shù)據(jù)庫(kù) / pgvector向量索引故障分開(kāi)方案） =========="
log_report "===== 一、通用數(shù)據(jù)庫(kù)故障恢復(fù)方案 ====="
if [[ $FOUND_DISK_FULL -eq 1 ]]; then
    log_report "1.磁盤(pán)滿故障：清理pg_wal歸檔/日志文件，擴(kuò)容存儲(chǔ)，systemctl restart postgresql-*"
fi
if [[ $FOUND_OOM -eq 1 ]]; then
    log_report "2.全局OOM殺死：調(diào)整shared_buffers/work_mem，systemd配置OOMScoreAdjust=-1000"
fi
if [[ $FOUND_LOCK_FILE -eq 1 ]]; then
    log_report "3.殘留鎖文件：確認(rèn)無(wú)postgres進(jìn)程后 rm -f $PGDATA/postmaster.pid"
fi
if [[ $FOUND_PERMISSION -eq 1 ]]; then
    log_report "4.權(quán)限異常：chown -R postgres:postgres 整個(gè)PG數(shù)據(jù)目錄"
fi
if [[ "$DB_STATE" == "阻塞假死態(tài)"* ]]; then
    log_report "5.進(jìn)程假死：kill -ABRT $POSTMASTER_PID 生成core堆棧，再重啟數(shù)據(jù)庫(kù)"
fi
log_report ""
log_report "===== 二、pgvector向量數(shù)據(jù)庫(kù)專(zhuān)屬故障恢復(fù)方案 ====="
if [[ $FOUND_VECTOR_OOM -eq 1 ]]; then
    log_report "向量查詢(xún)內(nèi)存溢出：降低單條查詢(xún)向量維度、調(diào)小work_mem、限制并行向量檢索數(shù)量"
    quick_run_cmd "ALTER SYSTEM SET work_mem = '64MB'; SELECT pg_reload_conf();"
fi
if [[ $FOUND_VECTOR_INDEX_ERR -eq 1 ]]; then
    log_report "向量索引損壞恢復(fù)：停機(jī)后執(zhí)行REINDEX INDEX 向量索引名；無(wú)法修復(fù)則重建索引"
    quick_run_cmd "REINDEX INDEX CONCURRENTLY 向量索引名; -- 在線輕量修復(fù)，業(yè)務(wù)低峰執(zhí)行"
fi
log_report ""
log_report "===== 三、數(shù)據(jù)庫(kù)存活但向量查詢(xún)卡死排查 ====="
if [[ "$DB_STATE" == "活躍運(yùn)行態(tài)"* ]]; then
    quick_run_cmd "psql -U $PGUSER -p $PGPORT -c 'SELECT pid,query,wait_event FROM pg_stat_activity WHERE query ILIKE ''%vector%'' OR query ILIKE ''%hnsw%'';'"
    quick_run_cmd "SELECT pg_terminate_backend(pid); -- 終止卡死的向量查詢(xún)會(huì)話"
fi
# ==========================================================================================
# =====================【獨(dú)立可執(zhí)行塊7：階段六 道家無(wú)為：前置監(jiān)控自愈加固方案】=====================
log_report ""
log_report "========== 階段六：無(wú)為前置加固（避免同類(lèi)向量故障重復(fù)發(fā)生） =========="
cat << SELFHELP_TIPS
1. Systemd服務(wù)強(qiáng)制加固（防止OOM優(yōu)先殺死PG）
    [Service]
    Restart=on-failure
    RestartSec=3
    OOMScoreAdjust=-1000
    LimitNOFILE=65536
2. pgvector向量庫(kù)專(zhuān)屬參數(shù)優(yōu)化
    work_mem = 128MB  # 高維向量檢索調(diào)大，避免查詢(xún)OOM
    max_parallel_workers_per_gather = 2  # 限制向量索引并行構(gòu)建，防止內(nèi)存打滿
3. 監(jiān)控告警配置（必加）
   - 磁盤(pán)使用率>85% 觸發(fā)告警
   - 日志匹配vector index corrupt / signal 9 實(shí)時(shí)告警
   - pg_stat_activity長(zhǎng)時(shí)間向量查詢(xún)會(huì)話告警
4. 混沌演練：定期模擬磁盤(pán)滿、高維向量并發(fā)查詢(xún)，驗(yàn)證自愈流程
5. 向量索引定時(shí)維護(hù)：業(yè)務(wù)低峰自動(dòng)REINDEX，防止長(zhǎng)期寫(xiě)入導(dǎo)致索引損壞
SELFHELP_TIPS
# =====================【獨(dú)立可執(zhí)行塊8：收尾：故障現(xiàn)場(chǎng)打包，參數(shù)緩存查看指引】=====================
log_report ""
log_report "========== 診斷收尾：故障現(xiàn)場(chǎng)全量歸檔 =========="
# 打包所有診斷快照
tar -czf "${DIAG_DIR}.tar.gz" -C /tmp "pg_vector_diag_${TIMESTAMP}" 2>/dev/null
success "? PostgreSQL+pgvector完整故障取證壓縮包生成完成"
print_copy_param "取證壓縮包完整路徑" "${DIAG_DIR}.tar.gz"
echo -e "\n${WHITE}【查看全部緩存參數(shù)（PID/日志/目錄/端口）執(zhí)行命令】${RED}cat ${CACHE_FILE}${NC}"
quick_run_cmd "cat ${CACHE_FILE}"
log_report ""
success "PostgreSQL(pgvector向量庫(kù))系統(tǒng)化故障診斷全部執(zhí)行完成"
log_report "根因排查優(yōu)先級(jí)：向量索引錯(cuò)誤 > OOM內(nèi)存溢出 > 磁盤(pán)空間耗盡 > WAL損壞 > IO硬件故障 > 權(quán)限問(wèn)題"
# =====================【內(nèi)置完整手工深度排查手冊(cè)，可單獨(dú)復(fù)制執(zhí)行】=====================
: <<'MANUAL_GUIDE'
===============================================================================
# 手工深度排查全套命令（自動(dòng)復(fù)用緩存參數(shù)，直接復(fù)制運(yùn)行）
===============================================================================
# 1. 查看所有向量索引列表
psql -U $PGUSER -p $PGPORT -c "SELECT relname,amname FROM pg_class c JOIN pg_am a ON c.relam=a.oid WHERE a.amname IN ('hnsw','ivfflat');"
# 2. 導(dǎo)出數(shù)據(jù)庫(kù)全部向量表結(jié)構(gòu)
pg_dump -U $PGUSER -p $PGPORT -s -f ${DIAG_DIR}/vector_table_schema.sql
# 3. 內(nèi)核完整OOM日志檢索
journalctl -k --no-pager | grep -i 'killed process'
# 4. 抓取卡死向量查詢(xún)堆棧
gdb -p $POSTMASTER_PID --batch -ex "bt" > ${DIAG_DIR}/pg_stack_trace.log
# 5. 清理異常WAL文件（磁盤(pán)滿應(yīng)急）
find $PGDATA/pg_wal -type f -mtime +7 -delete
# 6. 重啟數(shù)據(jù)庫(kù)服務(wù)
systemctl restart postgresql-*
===============================================================================
MANUAL_GUIDE

知識(shí)擴(kuò)展

PostgreSQL 故障診斷腳本

這是一個(gè)用于 Linux 下 PostgreSQL 數(shù)據(jù)庫(kù)故障系統(tǒng)化診斷的 Shell 腳本。它能自動(dòng)收集關(guān)鍵診斷信息，幫助 DBA 或系統(tǒng)管理員快速定位問(wèn)題。

完整代碼如下：

#!/bin/bash
# ============================================================================
# PostgreSQL 故障系統(tǒng)化診斷腳本
# 用途：收集 PostgreSQL 運(yùn)行狀態(tài)、配置、日志、系統(tǒng)資源等關(guān)鍵信息，用于故障排查
# 適用：Linux 系統(tǒng)，需有 psql 客戶端訪問(wèn)權(quán)限（建議配置 .pgpass 或環(huán)境變量）
# 作者：DBA Team
# 版本：2026.07
# ============================================================================
# 設(shè)置輸出目錄
DIAG_DIR="/tmp/pg_diag_$(date +%Y%m%d_%H%M%S)"
mkdir -p "$DIAG_DIR"
# ============================================================================
# 連接參數(shù)（請(qǐng)根據(jù)實(shí)際環(huán)境修改）
# 優(yōu)先使用環(huán)境變量，若未設(shè)置則使用默認(rèn)值
# ============================================================================
export PGHOST="${PGHOST:-localhost}"
export PGPORT="${PGPORT:-5432}"
export PGUSER="${PGUSER:-postgres}"
export PGPASSWORD="${PGPASSWORD:-}"  # 建議使用 .pgpass 文件，避免明文
export PGDATABASE="${PGDATABASE:-postgres}"
# 若設(shè)置了 PGPASSWORD，則 psql 使用密碼；否則依賴(lài) .pgpass 或信任連接
PSQL_CMD="psql -X -A -t -q"  # -X 忽略 .psqlrc，-A 取消對(duì)齊，-t 只顯示元組，-q 安靜
# 定義日志文件
DIAG_LOG="$DIAG_DIR/diagnostic.log"
exec > >(tee -a "$DIAG_LOG") 2>&1
echo "============================================================"
echo "PostgreSQL 診斷信息收集開(kāi)始于: $(date)"
echo "診斷輸出目錄: $DIAG_DIR"
echo "連接參數(shù): host=$PGHOST port=$PGPORT user=$PGUSER db=$PGDATABASE"
echo "============================================================"
# 輔助函數(shù)：執(zhí)行 SQL 并輸出到文件
run_sql() {
    local sql="$1"
    local outfile="$2"
    if [ -z "$outfile" ]; then
        $PSQL_CMD -c "$sql" 2>/dev/null || echo "錯(cuò)誤：無(wú)法執(zhí)行 SQL（可能無(wú)法連接）"
    else
        $PSQL_CMD -c "$sql" > "$outfile" 2>/dev/null
        if [ $? -ne 0 ]; then
            echo "錯(cuò)誤：無(wú)法執(zhí)行 SQL，可能無(wú)法連接或權(quán)限不足" > "$outfile"
        fi
    fi
}
# 測(cè)試數(shù)據(jù)庫(kù)連接
echo ">>> 測(cè)試數(shù)據(jù)庫(kù)連接..."
if $PSQL_CMD -c "SELECT 1" >/dev/null 2>&1; then
    DB_CONNECTED=true
    echo "數(shù)據(jù)庫(kù)連接成功"
else
    DB_CONNECTED=false
    echo "警告：無(wú)法連接到數(shù)據(jù)庫(kù)，將跳過(guò)所有 SQL 查詢(xún)，僅收集系統(tǒng)級(jí)信息"
fi
echo ""
# 1. 系統(tǒng)基本信息
echo ">>> 1. 系統(tǒng)基本信息"
{
    echo "--- 操作系統(tǒng) ---"
    uname -a
    echo "--- 發(fā)行版 ---"
    cat /etc/os-release 2>/dev/null || cat /etc/redhat-release 2>/dev/null
    echo "--- 內(nèi)核參數(shù) ---"
    sysctl kernel.hostname kernel.osrelease kernel.ostype 2>/dev/null
    echo "--- 負(fù)載 ---"
    uptime
    echo "--- 內(nèi)存 ---"
    free -h
    echo "--- 磁盤(pán)使用 ---"
    df -h
    echo "--- 文件系統(tǒng)掛載 ---"
    mount | grep -E 'ext|xfs|btrfs|zfs'
} > "$DIAG_DIR/system_info.txt"
cat "$DIAG_DIR/system_info.txt"
echo ""
# 2. PostgreSQL 版本及運(yùn)行狀態(tài)（僅當(dāng)連接成功時(shí)）
if [ "$DB_CONNECTED" = true ]; then
    echo ">>> 2. PostgreSQL 版本及運(yùn)行狀態(tài)"
    run_sql "SELECT version();" "$DIAG_DIR/version.txt"
    cat "$DIAG_DIR/version.txt"
    echo ""
    run_sql "SHOW data_directory;" "$DIAG_DIR/datadir.txt"
    echo "數(shù)據(jù)目錄: $(cat "$DIAG_DIR/datadir.txt")"
    echo ""
    run_sql "SHOW config_file;" "$DIAG_DIR/config_file.txt"
    echo "配置文件: $(cat "$DIAG_DIR/config_file.txt")"
    echo ""
    run_sql "SHOW hba_file;" "$DIAG_DIR/hba_file.txt"
    echo "pg_hba.conf: $(cat "$DIAG_DIR/hba_file.txt")"
    echo ""
    run_sql "SELECT pg_postmaster_start_time();" "$DIAG_DIR/start_time.txt"
    echo "啟動(dòng)時(shí)間: $(cat "$DIAG_DIR/start_time.txt")"
    echo ""
    run_sql "SELECT pg_is_in_recovery();" "$DIAG_DIR/is_recovery.txt"
    echo "是否處于恢復(fù)模式: $(cat "$DIAG_DIR/is_recovery.txt")"
    echo ""
fi
# 3. PostgreSQL 配置文件（過(guò)濾注釋和空行）
echo ">>> 3. PostgreSQL 配置文件內(nèi)容"
# 獲取配置文件路徑
CONF_FILE=""
if [ "$DB_CONNECTED" = true ]; then
    CONF_FILE=$(psql -X -t -c "SHOW config_file;" 2>/dev/null | head -1 | xargs)
fi
if [ -f "$CONF_FILE" ]; then
    grep -v '^#' "$CONF_FILE" | grep -v '^$' > "$DIAG_DIR/postgresql.conf"
    echo "配置文件內(nèi)容已保存至: $DIAG_DIR/postgresql.conf"
    cat "$DIAG_DIR/postgresql.conf"
else
    echo "無(wú)法獲取配置文件，嘗試查找常見(jiàn)位置..."
    for f in /etc/postgresql/*/main/postgresql.conf /var/lib/pgsql/data/postgresql.conf /usr/local/var/postgres/postgresql.conf; do
        if [ -f "$f" ]; then
            grep -v '^#' "$f" | grep -v '^$' > "$DIAG_DIR/postgresql.conf"
            echo "找到配置文件: $f，內(nèi)容已保存"
            cat "$DIAG_DIR/postgresql.conf"
            break
        fi
    done
fi
echo ""
# 4. 關(guān)鍵性能視圖查詢(xún)（僅當(dāng)連接成功時(shí)）
if [ "$DB_CONNECTED" = true ]; then
    echo ">>> 4. 關(guān)鍵性能視圖"
    # 4.1 當(dāng)前連接數(shù)
    echo "--- 當(dāng)前連接統(tǒng)計(jì) ---"
    run_sql "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;" "$DIAG_DIR/connection_stats.txt"
    cat "$DIAG_DIR/connection_stats.txt"
    echo ""
    # 4.2 長(zhǎng)時(shí)間運(yùn)行的事務(wù)（超過(guò)5分鐘）
    echo "--- 長(zhǎng)時(shí)間運(yùn)行的事務(wù)（超過(guò)5分鐘） ---"
    run_sql "SELECT pid, usename, application_name, client_addr, state, query_start, now() - query_start AS duration, substr(query, 1, 100) FROM pg_stat_activity WHERE state != 'idle' AND now() - query_start > interval '5 minutes' ORDER BY query_start;" "$DIAG_DIR/long_txns.txt"
    cat "$DIAG_DIR/long_txns.txt"
    echo ""
    # 4.3 鎖等待
    echo "--- 鎖等待信息 ---"
    run_sql "SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_locks blocked_locks JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;" "$DIAG_DIR/lock_waits.txt"
    cat "$DIAG_DIR/lock_waits.txt"
    echo ""
    # 4.4 活動(dòng)查詢(xún)（非空閑）
    echo "--- 當(dāng)前活動(dòng)查詢(xún)（前20個(gè)） ---"
    run_sql "SELECT pid, usename, application_name, client_addr, state, query_start, now() - query_start AS duration, substr(query, 1, 200) FROM pg_stat_activity WHERE state != 'idle' AND pid != pg_backend_pid() ORDER BY query_start LIMIT 20;" "$DIAG_DIR/active_queries.txt"
    cat "$DIAG_DIR/active_queries.txt"
    echo ""
    # 4.5 復(fù)制狀態(tài)（如果啟用）
    echo "--- 復(fù)制狀態(tài) ---"
    run_sql "SELECT application_name, client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;" "$DIAG_DIR/replication.txt"
    cat "$DIAG_DIR/replication.txt"
    echo ""
    # 4.6 數(shù)據(jù)庫(kù)大小和統(tǒng)計(jì)
    echo "--- 數(shù)據(jù)庫(kù)大小和統(tǒng)計(jì) ---"
    run_sql "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size, pg_stat_database.tup_returned, pg_stat_database.tup_fetched, pg_stat_database.tup_inserted, pg_stat_database.tup_updated, pg_stat_database.tup_deleted, pg_stat_database.xact_commit, pg_stat_database.xact_rollback, pg_stat_database.deadlocks FROM pg_database LEFT JOIN pg_stat_database ON pg_database.oid = pg_stat_database.datid WHERE pg_database.datallowconn = true ORDER BY pg_database_size(pg_database.datname) DESC;" "$DIAG_DIR/db_stats.txt"
    cat "$DIAG_DIR/db_stats.txt"
    echo ""
    # 4.7 表大?。ㄇ?0）
    echo "--- 最大的20個(gè)表（含索引） ---"
    run_sql "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size, pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 20;" "$DIAG_DIR/largest_tables.txt"
    cat "$DIAG_DIR/largest_tables.txt"
    echo ""
    # 4.8 索引使用統(tǒng)計(jì)（未使用的索引）
    echo "--- 可能未使用的索引（掃描次數(shù)少） ---"
    run_sql "SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan < 100 ORDER BY idx_scan LIMIT 20;" "$DIAG_DIR/unused_indexes.txt"
    cat "$DIAG_DIR/unused_indexes.txt"
    echo ""
    # 4.9 死鎖和沖突
    echo "--- 死鎖和沖突統(tǒng)計(jì) ---"
    run_sql "SELECT datname, deadlocks, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock FROM pg_stat_database WHERE deadlocks > 0 OR confl_lock > 0 OR confl_deadlock > 0;" "$DIAG_DIR/deadlocks.txt"
    cat "$DIAG_DIR/deadlocks.txt"
    echo ""
fi
# 5. 日志文件（最近100行）
echo ">>> 5. PostgreSQL 日志文件（最近100行）"
LOG_DIR=""
if [ "$DB_CONNECTED" = true ]; then
    LOG_DIR=$(psql -X -t -c "SHOW log_directory;" 2>/dev/null | head -1 | xargs)
    LOG_FILE=$(psql -X -t -c "SHOW log_filename;" 2>/dev/null | head -1 | xargs)
fi
if [ -n "$LOG_DIR" ] && [ -n "$LOG_FILE" ]; then
    # 處理相對(duì)路徑
    if [[ "$LOG_DIR" != /* ]]; then
        LOG_DIR="$PGDATA/$LOG_DIR"
    fi
    LOG_PATH="$LOG_DIR/$LOG_FILE"
    # 如果找不到，嘗試使用模式匹配
    if [ ! -f "$LOG_PATH" ]; then
        LOG_PATH=$(find "$LOG_DIR" -name "*.log" -type f -printf "%T@ %p\n" | sort -nr | head -1 | awk '{print $2}')
    fi
    if [ -f "$LOG_PATH" ]; then
        echo "日志文件: $LOG_PATH"
        tail -n 100 "$LOG_PATH" > "$DIAG_DIR/postgresql.log"
        cat "$DIAG_DIR/postgresql.log"
    else
        echo "未找到日志文件"
    fi
else
    echo "無(wú)法從數(shù)據(jù)庫(kù)獲取日志配置，嘗試常見(jiàn)位置..."
    for f in /var/log/postgresql/*.log /var/lib/pgsql/data/log/*.log /usr/local/var/postgres/log/*.log; do
        if [ -f "$f" ]; then
            echo "找到日志文件: $f"
            tail -n 100 "$f" > "$DIAG_DIR/postgresql.log"
            cat "$DIAG_DIR/postgresql.log"
            break
        fi
    done
fi
echo ""
# 6. 系統(tǒng)資源使用情況（PostgreSQL相關(guān)進(jìn)程）
echo ">>> 6. 系統(tǒng)資源使用"
{
    echo "--- PostgreSQL 進(jìn)程 ---"
    ps aux | grep -E 'postgres|pg_ctl' | grep -v grep
    echo ""
    echo "--- CPU 和內(nèi)存使用 ---"
    top -b -n 1 | head -n 20
    echo ""
    echo "--- 磁盤(pán) I/O ---"
    iostat -x 1 3 2>/dev/null || echo "iostat 未安裝"
} > "$DIAG_DIR/system_resources.txt"
cat "$DIAG_DIR/system_resources.txt"
echo ""
# 7. 數(shù)據(jù)目錄磁盤(pán)使用
echo ">>> 7. 數(shù)據(jù)目錄磁盤(pán)使用"
PGDATA_DIR=""
if [ "$DB_CONNECTED" = true ]; then
    PGDATA_DIR=$(psql -X -t -c "SHOW data_directory;" 2>/dev/null | head -1 | xargs)
fi
if [ -d "$PGDATA_DIR" ]; then
    echo "數(shù)據(jù)目錄: $PGDATA_DIR"
    du -sh "$PGDATA_DIR" 2>/dev/null
    echo "前10個(gè)子目錄大小:"
    du -h --max-depth=1 "$PGDATA_DIR" 2>/dev/null | sort -hr | head -n 10
else
    echo "未找到數(shù)據(jù)目錄，嘗試常見(jiàn)位置..."
    for d in /var/lib/pgsql/data /var/lib/postgresql/*/main /usr/local/var/postgres; do
        if [ -d "$d" ]; then
            echo "找到數(shù)據(jù)目錄: $d"
            du -sh "$d" 2>/dev/null
            du -h --max-depth=1 "$d" 2>/dev/null | sort -hr | head -n 10
            break
        fi
    done
fi
echo ""
# 8. 網(wǎng)絡(luò)連接（PostgreSQL 端口）
echo ">>> 8. PostgreSQL 端口連接數(shù)"
PGPORT_ACTUAL="${PGPORT:-5432}"
ss -tunap | grep -c ":$PGPORT_ACTUAL " > "$DIAG_DIR/port_conn_count.txt"
echo "當(dāng)前連接數(shù): $(cat "$DIAG_DIR/port_conn_count.txt")"
ss -tunap | grep ":$PGPORT_ACTUAL " | head -n 20 > "$DIAG_DIR/port_connections.txt"
cat "$DIAG_DIR/port_connections.txt"
echo ""
# 9. 打包所有收集的文件
echo "============================================================"
echo "診斷信息收集完成，輸出目錄: $DIAG_DIR"
tar -czf "$DIAG_DIR.tar.gz" -C /tmp "$(basename "$DIAG_DIR")"
echo "打包文件: $DIAG_DIR.tar.gz"
echo "請(qǐng)將打包文件發(fā)送給 DBA 進(jìn)行分析。"
echo "============================================================"
# 退出前提示
echo "注意：部分信息可能包含敏感數(shù)據(jù)，請(qǐng)妥善保管。"
exit 0

使用方法

將腳本保存為 collect_pg_diag.sh，并賦予執(zhí)行權(quán)限：

chmod +x collect_pg_diag.sh

配置連接參數(shù)（可選）：

通過(guò)環(huán)境變量設(shè)置：export PGHOST=localhost PGPORT=5432 PGUSER=postgres PGPASSWORD=yourpass PGDATABASE=postgres
或使用 ~/.pgpass 文件（推薦，更安全）。
若使用默認(rèn) Unix socket 和當(dāng)前系統(tǒng)用戶，可不必設(shè)置。

執(zhí)行腳本：

./collect_pg_diag.sh

獲取結(jié)果：腳本會(huì)在 /tmp 下生成形如 pg_diag_YYYYMMDD_HHMMSS.tar.gz 的壓縮包，包含所有診斷文件。

到此這篇關(guān)于Linux下PostgreSQL數(shù)據(jù)庫(kù)故障的系統(tǒng)化排查Shell腳本分享的文章就介紹到這了,更多相關(guān)PostgreSQL數(shù)據(jù)庫(kù)故障排查內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: