CentOS上PostgreSQL的故障排查

CentOS上PostgreSQL故障排查指南

1. 查看日志文件

PostgreSQL的日志是故障排查的核心依據，通常位于/var/log/postgresql/目錄（如postgresql-<version>-main.log）或數據目錄（如/var/lib/pgsql/data/pgstartup.log）。使用tail -f /var/log/postgresql/postgresql-<version>-main.log實時查看實時日志，或less命令查看歷史日志，重點關注FATAL、ERROR級別的錯誤信息（如配置錯誤、權限問題、數據庫損壞等）。

2. 檢查服務狀態

使用systemctl status postgresql命令確認PostgreSQL服務是否正在運行。若未啟動，嘗試systemctl start postgresql啟動服務；若啟動失敗，結合日志分析具體原因（如配置錯誤、端口占用、數據目錄權限問題）。還可通過systemctl enable postgresql設置服務開機自啟。

3. 驗證配置文件正確性

• postgresql.conf（數據目錄下，如/var/lib/pgsql/data/postgresql.conf）：檢查關鍵配置項，如listen_addresses（需設置為'*'允許遠程連接）、port（默認5432，確保未被占用）、max_connections（根據服務器性能調整）、shared_buffers（內存分配，建議為物理內存的25%左右）。
• pg_hba.conf（數據目錄下）：檢查客戶端認證規則，確保允許目標IP或網段的連接。例如，允許所有IP通過密碼認證連接：host all all 0.0.0.0/0 md5；允許本地連接：local all all trust。修改后需執行ALTER SYSTEM SET listen_addresses='*'（若修改了postgresql.conf）并重啟服務（systemctl restart postgresql）使配置生效。

4. 排查連接問題

• 服務未啟動：若出現“could not connect to server: No such file or directory”或“Connection refused”，首先檢查服務狀態（systemctl status postgresql），未啟動則啟動服務。
• 端口占用：使用netstat -tunlp | grep 5432或ss -tunlp | grep 5432檢查5432端口是否被其他進程占用。若被占用，修改postgresql.conf中的port為其他端口（如5433），并重啟服務。
• 防火墻攔截：CentOS默認開啟firewalld，需放行PostgreSQL端口：firewall-cmd --add-port=5432/tcp --permanent，然后firewall-cmd --reload。若使用iptables，需添加對應規則。
• 認證失敗：若出現“FATAL: password authentication failed for user”或“Peer authentication failed”，檢查pg_hba.conf中的認證方法（如md5或password），確保用戶名、密碼正確。遠程連接時，需將METHOD改為md5或scram-sha-256，并重啟服務。

5. 分析查詢性能問題

• 慢查詢定位：使用pg_stat_activity視圖查看當前活動查詢：SELECT * FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start DESC;，重點關注query字段中的慢查詢。
• 查詢計劃分析：使用EXPLAIN命令分析慢查詢的執行計劃，找出性能瓶頸（如全表掃描、索引未使用）：EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;。若未使用索引，可創建合適的索引優化（如CREATE INDEX idx_users_age ON users(age);）。
• 更新統計信息：使用ANALYZE命令更新表統計信息，幫助優化器生成更優的執行計劃：ANALYZE users;。

6. 處理死鎖問題

死鎖通常表現為多個事務相互等待對方釋放鎖，導致查詢卡住?？赏ㄟ^以下步驟排查：

• 查看鎖信息：SELECT pid, query, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type = 'Lock';，找出等待鎖的事務。
• 終止死鎖事務：選擇影響較小的PID，執行SELECT pg_cancel_backend(pid);取消事務；若仍無法解決，使用SELECT pg_terminate_backend(pid);強制終止。

7. 檢查系統資源

使用top、htop、free -h、df -h等命令檢查服務器資源使用情況：

• CPU：若CPU使用率過高，可能需要優化查詢（如添加索引）、增加CPU資源或調整work_mem參數（內存排序緩沖區大?。?。
• 內存：若內存不足，可能導致頻繁交換（swap），需調整shared_buffers、work_mem等參數，或增加物理內存。
• 磁盤空間：若磁盤空間不足（df -h顯示根分區使用率超過80%），可能導致數據庫無法寫入或啟動失敗，需清理無用文件（如日志、臨時文件）或擴容磁盤。

8. 處理數據目錄權限問題

PostgreSQL數據目錄（如/var/lib/pgsql/data）必須由postgres用戶擁有，且權限設置為700（僅所有者可讀、寫、執行）。若權限錯誤，可使用以下命令修復：

chown -R postgres:postgres /var/lib/pgsql/data
chmod -R 0700 /var/lib/pgsql/data

修改后重啟服務。

9. 修復數據庫損壞

若數據庫因意外關機、磁盤故障等原因損壞，可使用pg_resetwal工具修復（謹慎操作，需備份數據）：

sudo -u postgres pg_resetwal -D /var/lib/pgsql/data

修復后重啟服務，檢查數據庫是否能正常啟動。