Linux下PostgreSQL數(shù)據(jù)庫故障的系統(tǒng)化排查Shell腳本分享
更新時間：2026年07月14日 09:18:48 作者：weixin_30777913
這篇文章主要為大家詳細介紹了Linux下PostgreSQL數(shù)據(jù)庫故障系統(tǒng)化診斷的腳本,文中的示例代碼講解詳細,具有一定的借鑒價值,感興趣的小伙伴可以了解下
前言
本文主要為大家分享一個Linux原生PostgreSQL(含pgvector向量庫)系統(tǒng)化故障排查腳本
適用：無Docker、系統(tǒng)直接安裝PostgreSQL，支持pgvector向量擴展專項檢測
用法：
- 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
特性：分段可單獨運行、自動緩存關(guān)鍵參數(shù)、向量索引專項校驗、只讀取證不修改數(shù)據(jù)庫
安全承諾：腳本僅采集日志/狀態(tài)，無任何自動修復(fù)、刪除、修改文件操作
完整代碼
#!/bin/bash
#===============================================================================
# 腳本名稱: pg_vector_fault_diagnosis.sh
#
#===============================================================================
set -euo pipefail
# =====================【獨立可執(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'
# 時間戳、取證緩存目錄
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"
}
# 默認配置
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ù)目錄] [-U 數(shù)據(jù)庫用戶] [-p 端口]" >&2; exit 1 ;;
esac
done
# 緩存基礎(chǔ)入?yún)?
print_copy_param "PG數(shù)據(jù)目錄PGDATA" "$PGDATA"
print_copy_param "數(shù)據(jù)庫運行用戶PGUSER" "$PGUSER"
print_copy_param "數(shù)據(jù)庫監(jiān)聽端口PGPORT" "$PGPORT"
# 自動切換postgres用戶執(zhí)行核心檢測
if [[ $(id -un) != "$PGUSER" ]]; then
if id "$PGUSER" &>/dev/null; then
log_report "自動切換至$PGUSER用戶執(zhí)行完整診斷"
exec su -s /bin/bash "$PGUSER" -c "$0 -D $PGDATA -U $PGUSER -p $PGPORT"
else
error "用戶$PGUSER不存在，請手動切換postgres用戶運行腳本"
exit 1
fi
fi
# ==========================================================================================
# =====================【獨立可執(zhí)行塊2：階段一 本體論：數(shù)據(jù)庫實體狀態(tài)全判定】=====================
log_report ""
log_report "========== 階段一：本體論狀態(tài)判定（區(qū)分PG進程/集群/端口/鎖文件實體） =========="
log_report "目標：精準劃分數(shù)據(jù)庫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主進程PID緩存
POSTMASTER_PID=$(pgrep -f "postgres:.*postmaster" || echo "")
print_copy_param "PostgreSQL主進程PID" "$POSTMASTER_PID"
# 1.3 端口監(jiān)聽檢測
PORT_OPEN="no"
if ss -tlnp | grep -q ":$PGPORT "; then
PORT_OPEN="yes"
fi
print_copy_param "端口$PGPORT監(jiān)聽狀態(tài)" "$PORT_OPEN"
# 1.4 數(shù)據(jù)目錄鎖文件檢測
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 六類故障狀態(tài)綜合判定
DB_STATE="unknown"
if [[ -n "$POSTMASTER_PID" && "$PORT_OPEN" == "yes" ]]; then
DB_STATE="活躍運行態(tài)(Active) - 進程+端口正常，故障大概率為向量查詢卡死/慢索引構(gòu)建"
elif [[ -n "$POSTMASTER_PID" ]]; then
DB_STATE="阻塞假死態(tài)(Dying) - 進程存在但端口關(guān)閉，向量內(nèi)存溢出阻塞、等待IO"
elif [[ "$SERVICE_ACTIVE" == "active" && -z "$POSTMASTER_PID" ]]; then
DB_STATE="僵死態(tài)(Zombie) - systemd顯示運行但進程消失，磁盤只讀/權(quán)限崩潰"
elif $PID_FILE_EXIST; then
DB_STATE="異常崩潰態(tài)(Crashed) - 非正常關(guān)閉，鎖文件殘留，向量索引損壞高發(fā)"
else
DB_STATE="正常關(guān)閉態(tài)(CleanStop) - 無進程無鎖文件，人為停止或定時任務(wù)關(guān)閉"
fi
log_report "數(shù)據(jù)庫綜合判定狀態(tài)：$DB_STATE"
log_report "狀態(tài)明細：systemd=$SERVICE_ACTIVE PID=$POSTMASTER_PID 端口監(jiān)聽=$PORT_OPEN 鎖文件=$PID_FILE_EXIST"
# 對應(yīng)狀態(tài)快捷排查命令
if [[ "$DB_STATE" == "活躍運行態(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
# ==========================================================================================
# =====================【獨立可執(zhí)行塊3：階段二 奧卡姆剃刀：日志致命錯誤提取（含pgvector向量專屬錯誤）】=====================
log_report ""
log_report "========== 階段二：日志剃刀導(dǎo)航（包含pgvector向量索引專屬故障關(guān)鍵詞） =========="
# 自動定位日志文件
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 "本地日志目錄無文件，數(shù)據(jù)庫日志輸出至系統(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
# 致命錯誤關(guān)鍵詞（擴展pgvector向量庫特有報錯）
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行日志匹配錯誤
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/向量索引錯誤，轉(zhuǎn)向系統(tǒng)資源檢查"
quick_run_cmd "grep -E '$CRITICAL_PATTERN' $PG_LOG_PATH"
else
error "檢測到關(guān)鍵故障日志（含向量庫異常）："
for err in "${critical_errors[@]}"; do
echo " $err"
done
fi
# 標記故障大類（含向量專屬故障標記）
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ù)庫專屬故障
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
# 緩存故障標記
print_copy_param "OOM內(nèi)核殺死標記" "$FOUND_OOM"
print_copy_param "磁盤滿標記" "$FOUND_DISK_FULL"
print_copy_param "向量索引損壞標記" "$FOUND_VECTOR_INDEX_ERR"
print_copy_param "向量查詢內(nèi)存溢出標記" "$FOUND_VECTOR_OOM"
# ==========================================================================================
# =====================【獨立可執(zhí)行塊4：階段三 辯證法：定向資源檢查（向量內(nèi)存/磁盤/內(nèi)核OOM）】=====================
log_report ""
log_report "========== 階段三：辯證法定向資源排查（根據(jù)日志錯誤只檢查對應(yīng)維度，減少無效采集） =========="
# 3.1 磁盤空間故障定向檢查
if [[ $FOUND_DISK_FULL -eq 1 ]]; then
error "日志標記磁盤空間耗盡，檢查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)存殺死定向檢查（向量高維查詢極易觸發(fā)）
if [[ $FOUND_OOM -eq 1 || $FOUND_VECTOR_OOM -eq 1 ]]; then
error "日志標記內(nèi)存溢出（普通OOM/向量高維查詢內(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 磁盤IO硬件故障定向檢查
if [[ $FOUND_IO_ERROR -eq 1 ]]; then
error "日志檢測磁盤IO硬件錯誤"
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 向量索引損壞專項檢查（pgvector獨有）
if [[ $FOUND_VECTOR_INDEX_ERR -eq 1 ]]; then
error "檢測到向量索引損壞/加載失敗，執(zhí)行向量索引健康檢測"
log_report "向量索引修復(fù)前置檢查命令（只讀查詢，無修改）"
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 向量索引名; -- 停機后執(zhí)行，修復(fù)損壞索引"
fi
# 3.5 無明確日志錯誤，執(zhí)行極簡全局資源快照
if [[ $FOUND_OOM -eq 0 && $FOUND_DISK_FULL -eq 0 && $FOUND_IO_ERROR -eq 0 && $FOUND_VECTOR_INDEX_ERR -eq 0 ]]; then
log_report "無定向日志錯誤，采集基礎(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
# ==========================================================================================
# =====================【獨立可執(zhí)行塊5：階段四 整體論：PG集群+pgvector向量擴展深度校驗】=====================
log_report ""
log_report "========== 階段四：整體論 集群元數(shù)據(jù)&pgvector向量擴展校驗 =========="
# 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ù)庫集群關(guān)閉狀態(tài)：$CLUSTER_STATE"
print_copy_param "集群關(guān)機狀態(tài)" "$CLUSTER_STATE"
if [[ "$CLUSTER_STATE" == *"shut down normally"* ]]; then
success "上次數(shù)據(jù)庫正常關(guān)閉"
elif [[ "$CLUSTER_STATE" == *"crash"* || "$CLUSTER_STATE" == *"recovery"* ]]; then
warn "數(shù)據(jù)庫上次異常崩潰，啟動將執(zhí)行WAL恢復(fù)"
fi
else
warn "無法讀取pg_controldata，數(shù)據(jù)目錄可能損壞/權(quán)限不足"
fi
# 4.2 數(shù)據(jù)目錄權(quá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)限異常，運行用戶不匹配"
quick_run_cmd "sudo chown -R $PGUSER:$PGUSER $PGDATA"
else
success "數(shù)據(jù)目錄屬主權(quán)限正常"
fi
# 4.3 pgvector向量擴展專項檢測（向量庫核心）
log_report "檢測pgvector向量擴展安裝狀態(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向量擴展已安裝，支持hnsw/ivfflat向量索引"
quick_run_cmd "psql -U $PGUSER -p $PGPORT -c '\dx vector'"
else
warn "未檢測pgvector擴展，向量相關(guān)表/索引會啟動失敗"
quick_run_cmd "CREATE EXTENSION IF NOT EXISTS vector; -- 數(shù)據(jù)庫內(nèi)執(zhí)行安裝"
fi
print_copy_param "pgvector擴展是否安裝" "$VECTOR_EXT_EXIST"
# 4.4 WAL預(yù)檢查（崩潰恢復(fù)核心）
if [[ $FOUND_WAL_CORRUPT -eq 1 ]]; then
error "WAL日志檢測損壞風(fēng)險，禁止直接啟動數(shù)據(jù)庫"
log_report "辯證恢復(fù)選擇：1. PITR時間點恢復(fù)（無數(shù)據(jù)丟失） 2. pg_resetwal強制重置（丟失最近事務(wù)）"
quick_run_cmd "sudo -u $PGUSER pg_resetwal -D $PGDATA -f"
fi
# ==========================================================================================
# =====================【獨立可執(zhí)行塊6：階段五 實用主義：分層恢復(fù)方案（區(qū)分普通PG/向量庫故障）】=====================
log_report ""
log_report "========== 階段五：辯證分層恢復(fù)建議（普通數(shù)據(jù)庫 / pgvector向量索引故障分開方案） =========="
log_report "===== 一、通用數(shù)據(jù)庫故障恢復(fù)方案 ====="
if [[ $FOUND_DISK_FULL -eq 1 ]]; then
log_report "1.磁盤滿故障：清理pg_wal歸檔/日志文件，擴容存儲，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.殘留鎖文件：確認無postgres進程后 rm -f $PGDATA/postmaster.pid"
fi
if [[ $FOUND_PERMISSION -eq 1 ]]; then
log_report "4.權(quán)限異常：chown -R postgres:postgres 整個PG數(shù)據(jù)目錄"
fi
if [[ "$DB_STATE" == "阻塞假死態(tài)"* ]]; then
log_report "5.進程假死：kill -ABRT $POSTMASTER_PID 生成core堆棧，再重啟數(shù)據(jù)庫"
fi
log_report ""
log_report "===== 二、pgvector向量數(shù)據(jù)庫專屬故障恢復(fù)方案 ====="
if [[ $FOUND_VECTOR_OOM -eq 1 ]]; then
log_report "向量查詢內(nèi)存溢出：降低單條查詢向量維度、調(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ù)：停機后執(zhí)行REINDEX INDEX 向量索引名；無法修復(fù)則重建索引"
quick_run_cmd "REINDEX INDEX CONCURRENTLY 向量索引名; -- 在線輕量修復(fù)，業(yè)務(wù)低峰執(zhí)行"
fi
log_report ""
log_report "===== 三、數(shù)據(jù)庫存活但向量查詢卡死排查 ====="
if [[ "$DB_STATE" == "活躍運行態(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); -- 終止卡死的向量查詢會話"
fi
# ==========================================================================================
# =====================【獨立可執(zhí)行塊7：階段六 道家無為：前置監(jiān)控自愈加固方案】=====================
log_report ""
log_report "========== 階段六：無為前置加固（避免同類向量故障重復(fù)發(fā)生） =========="
cat << SELFHELP_TIPS
1. Systemd服務(wù)強制加固（防止OOM優(yōu)先殺死PG）
[Service]
Restart=on-failure
RestartSec=3
OOMScoreAdjust=-1000
LimitNOFILE=65536
2. pgvector向量庫專屬參數(shù)優(yōu)化
work_mem = 128MB # 高維向量檢索調(diào)大，避免查詢OOM
max_parallel_workers_per_gather = 2 # 限制向量索引并行構(gòu)建，防止內(nèi)存打滿
3. 監(jiān)控告警配置（必加）
- 磁盤使用率>85% 觸發(fā)告警
- 日志匹配vector index corrupt / signal 9 實時告警
- pg_stat_activity長時間向量查詢會話告警
4. 混沌演練：定期模擬磁盤滿、高維向量并發(fā)查詢，驗證自愈流程
5. 向量索引定時維護：業(yè)務(wù)低峰自動REINDEX，防止長期寫入導(dǎo)致索引損壞
SELFHELP_TIPS
# =====================【獨立可執(zhí)行塊8：收尾：故障現(xiàn)場打包，參數(shù)緩存查看指引】=====================
log_report ""
log_report "========== 診斷收尾：故障現(xiàn)場全量歸檔 =========="
# 打包所有診斷快照
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向量庫)系統(tǒng)化故障診斷全部執(zhí)行完成"
log_report "根因排查優(yōu)先級：向量索引錯誤 > OOM內(nèi)存溢出 > 磁盤空間耗盡 > WAL損壞 > IO硬件故障 > 權(quán)限問題"
# =====================【內(nèi)置完整手工深度排查手冊，可單獨復(fù)制執(zhí)行】=====================
: <<'MANUAL_GUIDE'
===============================================================================
# 手工深度排查全套命令（自動復(fù)用緩存參數(shù)，直接復(fù)制運行）
===============================================================================
# 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ù)庫全部向量表結(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. 抓取卡死向量查詢堆棧
gdb -p $POSTMASTER_PID --batch -ex "bt" > ${DIAG_DIR}/pg_stack_trace.log
# 5. 清理異常WAL文件（磁盤滿應(yīng)急）
find $PGDATA/pg_wal -type f -mtime +7 -delete
# 6. 重啟數(shù)據(jù)庫服務(wù)
systemctl restart postgresql-*
===============================================================================
MANUAL_GUIDE知識擴展
PostgreSQL 故障診斷腳本
這是一個用于 Linux 下 PostgreSQL 數(shù)據(jù)庫故障系統(tǒng)化診斷的 Shell 腳本。它能自動收集關(guān)鍵診斷信息，幫助 DBA 或系統(tǒng)管理員快速定位問題。
完整代碼如下：
#!/bin/bash
# ============================================================================
# PostgreSQL 故障系統(tǒng)化診斷腳本
# 用途：收集 PostgreSQL 運行狀態(tài)、配置、日志、系統(tǒng)資源等關(guān)鍵信息，用于故障排查
# 適用：Linux 系統(tǒng)，需有 psql 客戶端訪問權(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ù)（請根據(jù)實際環(huán)境修改）
# 優(yōu)先使用環(huán)境變量，若未設(shè)置則使用默認值
# ============================================================================
export PGHOST="${PGHOST:-localhost}"
export PGPORT="${PGPORT:-5432}"
export PGUSER="${PGUSER:-postgres}"
export PGPASSWORD="${PGPASSWORD:-}" # 建議使用 .pgpass 文件，避免明文
export PGDATABASE="${PGDATABASE:-postgres}"
# 若設(shè)置了 PGPASSWORD，則 psql 使用密碼；否則依賴 .pgpass 或信任連接
PSQL_CMD="psql -X -A -t -q" # -X 忽略 .psqlrc，-A 取消對齊，-t 只顯示元組，-q 安靜
# 定義日志文件
DIAG_LOG="$DIAG_DIR/diagnostic.log"
exec > >(tee -a "$DIAG_LOG") 2>&1
echo "============================================================"
echo "PostgreSQL 診斷信息收集開始于: $(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 "錯誤：無法執(zhí)行 SQL（可能無法連接）"
else
$PSQL_CMD -c "$sql" > "$outfile" 2>/dev/null
if [ $? -ne 0 ]; then
echo "錯誤：無法執(zhí)行 SQL，可能無法連接或權(quán)限不足" > "$outfile"
fi
fi
}
# 測試數(shù)據(jù)庫連接
echo ">>> 測試數(shù)據(jù)庫連接..."
if $PSQL_CMD -c "SELECT 1" >/dev/null 2>&1; then
DB_CONNECTED=true
echo "數(shù)據(jù)庫連接成功"
else
DB_CONNECTED=false
echo "警告：無法連接到數(shù)據(jù)庫，將跳過所有 SQL 查詢，僅收集系統(tǒng)級信息"
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 "--- 負載 ---"
uptime
echo "--- 內(nèi)存 ---"
free -h
echo "--- 磁盤使用 ---"
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 版本及運行狀態(tài)（僅當(dāng)連接成功時）
if [ "$DB_CONNECTED" = true ]; then
echo ">>> 2. PostgreSQL 版本及運行狀態(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 "啟動時間: $(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 配置文件（過濾注釋和空行）
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 "無法獲取配置文件，嘗試查找常見位置..."
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)鍵性能視圖查詢（僅當(dāng)連接成功時）
if [ "$DB_CONNECTED" = true ]; then
echo ">>> 4. 關(guān)鍵性能視圖"
# 4.1 當(dāng)前連接數(shù)
echo "--- 當(dāng)前連接統(tǒng)計 ---"
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 長時間運行的事務(wù)（超過5分鐘）
echo "--- 長時間運行的事務(wù)（超過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 活動查詢（非空閑）
echo "--- 當(dāng)前活動查詢（前20個） ---"
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ù)庫大小和統(tǒng)計
echo "--- 數(shù)據(jù)庫大小和統(tǒng)計 ---"
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個表（含索引） ---"
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)計（未使用的索引）
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)計 ---"
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
# 處理相對路徑
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 "無法從數(shù)據(jù)庫獲取日志配置，嘗試常見位置..."
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)進程）
echo ">>> 6. 系統(tǒng)資源使用"
{
echo "--- PostgreSQL 進程 ---"
ps aux | grep -E 'postgres|pg_ctl' | grep -v grep
echo ""
echo "--- CPU 和內(nèi)存使用 ---"
top -b -n 1 | head -n 20
echo ""
echo "--- 磁盤 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ù)目錄磁盤使用
echo ">>> 7. 數(shù)據(jù)目錄磁盤使用"
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個子目錄大小:"
du -h --max-depth=1 "$PGDATA_DIR" 2>/dev/null | sort -hr | head -n 10
else
echo "未找到數(shù)據(jù)目錄，嘗試常見位置..."
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 "請將打包文件發(fā)送給 DBA 進行分析。"
echo "============================================================"
# 退出前提示
echo "注意：部分信息可能包含敏感數(shù)據(jù)，請妥善保管。"
exit 0使用方法
將腳本保存為 collect_pg_diag.sh，并賦予執(zhí)行權(quán)限：
chmod +x collect_pg_diag.sh
配置連接參數(shù)（可選）：
- 通過環(huán)境變量設(shè)置：
export PGHOST=localhost PGPORT=5432 PGUSER=postgres PGPASSWORD=yourpass PGDATABASE=postgres - 或使用
~/.pgpass文件（推薦，更安全）。 - 若使用默認 Unix socket 和當(dāng)前系統(tǒng)用戶，可不必設(shè)置。
執(zhí)行腳本：
./collect_pg_diag.sh
獲取結(jié)果：腳本會在 /tmp 下生成形如 pg_diag_YYYYMMDD_HHMMSS.tar.gz 的壓縮包，包含所有診斷文件。
到此這篇關(guān)于Linux下PostgreSQL數(shù)據(jù)庫故障的系統(tǒng)化排查Shell腳本分享的文章就介紹到這了,更多相關(guān)PostgreSQL數(shù)據(jù)庫故障排查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
時間的獲取在我們?nèi)粘９ぷ髦惺墙?jīng)常遇到的，下面這篇文章主要給大家介紹了關(guān)于Linux系統(tǒng)中時間的獲取和使用的相關(guān)資料，文中通過示例代碼介紹的非常詳細，需要的朋友可以參考下2018-11-11
這篇文章主要介紹了CentOS 7上為PHP5安裝suPHP的方法,需要的朋友可以參考下2020-02-02
在Ubuntu 16.04 Server上安裝Zabbix的方法
這篇文章主要介紹了在Ubuntu 16.04 Server上安裝Zabbix的方法，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-03-03
如何修改Linux系統(tǒng)的/etc/ssh/sshd_config權(quán)限
文章描述了在修改/etc/ssh/sshd_config文件權(quán)限時遇到的問題,普通用戶使用`chmod 777`命令無法修改文件權(quán)限,但切換到root用戶后,問題解決,作者希望通過分享這個經(jīng)驗幫助他人2026-03-03
pssh是python開發(fā)的一個用來批量管理linux主機的工具，現(xiàn)在我們來介紹pssh的使用方法。2018-09-09
這篇文章主要介紹了centos6超20TB磁盤的分區(qū)格式化的示例代碼，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07
linux安裝jdk并設(shè)置環(huán)境變量的方法教程(看這一篇夠了)
這篇文章主要介紹了linux安裝jdk并設(shè)置環(huán)境變量的相關(guān)資料，本文給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2020-02-02
本篇文章主要介紹了linux kill命令詳解 ，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-04-04
這篇文章主要介紹了Ubuntu 20.04 開啟隱藏錄音降噪功能,本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-08-08
本文介紹了如何在Linux上使用firewall命令開放端口,包括查看狀態(tài)、開啟和關(guān)閉端口、添加和移除IP段規(guī)則、批量添加區(qū)間端口、開放系統(tǒng)服務(wù)等操作2025-11-11