PostgreSQL 日誌指南:設定、慢查詢與稽核

了解 PostgreSQL 的 stderr、CSV 與 JSON 輸出設定,檢查慢查詢、鎖定等待與稽核範圍,並驗證 DataKit 日誌採集。

最佳實務
日誌管理平台示意圖

部署完,資料庫儀表板顯示延遲突然升高。要分清楚是查詢本身跑得慢,還是工作階段在等鎖,可以先看 PostgreSQL 日誌。前提是知道記錄送到了哪裡。容器裡可能是執行環境接管的標準錯誤輸出,自行管理的主機則可能由 PostgreSQL 寫入 JSON 檔案。

以下範例適用於 PostgreSQL 15 至 18。變更前先確認主版本與目前的日誌輸出路徑。

要找的線索對應哪個設定?

想確認的事 從哪個設定查起 能看到什麼
伺服器把日誌寫到哪裡? log_destination、logging_collector 執行環境或服務接管的 stderr,或 current_logfiles 所列的檔案
哪些語句花了很久? log_min_duration_statement 已完成的語句或延伸查詢協定階段達到時間門檻
工作階段有沒有等鎖? log_lock_waits 等待超過 deadlock_timeout,仍需其他資訊確認有沒有死結
需要稽核哪些 SQL? log_statement 或 pgAudit 依語句類別、物件和角色核對涵蓋範圍

1. 決定日誌輸出位置

# postgresql.conf
logging_collector = on
log_destination = 'jsonlog'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = off

變更 logging_collector 需要重啟。收集器已啟用時,可重新載入設定套用新的 log_destination。csvlog 和 jsonlog 都需要收集器,JSON 輸出從 PostgreSQL 15 開始支援。範例中的相對路徑 log 位於資料目錄下,同一個資料目錄的 current_logfiles 會列出收集器正在寫入的檔案。

時間和大小是觸發輪替的條件。100 MB 是輪替門檻,輪替本身也不會清除舊檔案。檔名加入時分秒後,同一天因檔案大小觸發輪替時也能另開檔案。若容器執行環境或服務管理員已接管 stderr 並管理保留時間,可以沿用既有路徑。 PostgreSQL 日誌設定(英文) · PostgreSQL 15 版本說明(英文)

2. 在文字日誌前綴留下有用的資訊

log_line_prefix = '%m [%p] %c %q%u@%d %a '

%m 是帶毫秒的時間戳記,%p 是程序 ID,%c 是工作階段識別碼,%s 則是程序啟動時間。非工作階段程序的前綴會在 %q 結束,其後的使用者、資料庫與應用程式名稱只出現在工作階段的記錄裡。需要時可用 %e 加入 SQLSTATE,或用 %i 加入命令標籤。

SQL 本文與耗時分開記錄時,可用工作階段識別碼找出兩筆記錄的關係。CSV 與 JSON 已帶有結構化欄位,可直接讀取。

3. 記錄慢查詢和鎖定等待

log_min_duration_statement = 500ms
log_lock_waits = on
log_checkpoints = on
log_temp_files = 0

500 ms 是範例門檻。已完成的語句耗時達到門檻時會記錄,-1 關閉這項功能,0 記錄所有已完成語句的耗時。延伸查詢協定會分開計算 Parse、Bind、Execute。流量大時,可用 log_min_duration_sample 和 log_statement_sample_rate 對較快的語句取樣。達到完整記錄門檻的語句仍會寫入日誌。

log_lock_waits 記錄超過 deadlock_timeout 的鎖定等待。檢查點日誌提供寫入的緩衝區數量和耗時等統計。log_temp_files=0 則在每個暫存檔刪除時記錄名稱和大小,可用來調查排序或雜湊處理是否使用了磁碟。

若要留下執行計畫,可在授權的工作階段載入 auto_explain,或依所需範圍預載入,再設定它自己的 auto_explain.log_min_duration。收集實際執行統計或逐節點計時都可能增加負擔。 auto_explain(英文)

4. 控制語句與連線的記錄範圍

log_statement = 'ddl'
log_connections = on
log_disconnections = on
log_hostname = off

log_statement 可設為 none、ddl、mod 或 all。mod 除了 DDL,還記錄符合條件的資料變更語句。即使設定為 all,基本語法分析失敗或延伸協定在 Execute 前失敗的語句仍不會出現在這類記錄中。排查這些語句時,另查 log_min_error_statement。

一起看連線和斷線日誌,可以判斷短連線是不是太多。PostgreSQL 15 至 17 的 log_connections 是布林值,18 改為選項清單並維持 on 相容。這個值涵蓋接收、身分驗證和授權,沒有 setup_durations。斷線記錄會包含工作階段長度。SQL 本文與繫結參數可能包含敏感資料,應控制記錄時間、存取權限與保留時間。 PostgreSQL 17 日誌設定(英文) · PostgreSQL 18 日誌設定(英文)

5. 保留排查錯誤所需的細節

log_error_verbosity = default
log_min_error_statement = error

log_error_verbosity 控制記錄的詳細程度,verbose 會加入 SQLSTATE 和原始碼位置。log_min_error_statement 決定何時附上出錯的 SQL,伺服器訊息本身的門檻則由 log_min_messages 決定。要統計錯誤時,使用驗證過的嚴重度或 SQLSTATE 篩選。查詢出現 ERROR 和工作階段因 FATAL 終止,代表不同狀況。

用一筆慢查詢檢查集中採集

在測試環境用可辨識、達到門檻且無副作用的查詢產生日誌,再檢查原始輸出。在 DataKit 檔案採集(簡體中文)指定實際路徑並確認讀取權限。DataKit PostgreSQL 整合(簡體中文)也提供檔案輸入設定。文件的內建解析範例對應特定文字格式,JSON 或 CSV 須採用相符的解析規則。

將資料庫名稱、使用者、時間與嚴重度和原始記錄對照。JSON 的耗時可能在 message 裡,也可能沒有 statement 欄位。轉送敏感 SQL 前,先驗證欄位擷取與遮蔽,再用資料庫事件中確認過的欄位和值設定觀測雲日誌檢測(簡體中文)。

常見問題

csvlog 和 jsonlog 該選哪個?

PostgreSQL 15 以上可用逐行 JSON。解析時允許欄位省略,並確認時間戳記與時區。已有 CSV 管線也可以沿用,但要核對伺服器版本的欄位順序,以及引號和跨行內容。若目前已收集 stderr,用符合前綴的文字解析也可以。

log_statement=all 可以開多久?

縮短蒐證時間,並留意磁碟空間及 I/O。記錄量會受流量、語句長度和參數影響。需要按類別或關聯物件稽核時,可評估與伺服器主版本相符的 pgAudit(英文)。它需要預載入,並應先建立擴充套件,再設定 pgaudit.log,以取得 DDL 物件資訊。也要確認實際啟用的類別、角色和關聯物件符合稽核需求。

FATAL: remaining connection slots 是什麼?

一般角色可用的連線名額已用完,具備權限的角色仍可能使用保留名額。先按資料庫、角色與應用程式檢查連線池用量及尚未結束的交易。別單憑閒置狀態中止工作階段。max_connections 變更需要重啟,也會影響伺服器資源配置。 連線設定(英文)

容器裡的 PostgreSQL 日誌怎麼採集?

若 PostgreSQL 寫到 stderr,應啟用符合執行環境或 Kubernetes 部署的 DataKit 採集,並讓它能讀到來源。若在容器內寫檔,將日誌目錄掛載到檔案採集器能讀取的位置。同一個日誌串流選一條路徑,就能避免重複收集。

延伸閱讀

取得專屬方案

聯絡我們

加入社群

微信扫码
加入官方交流群

立即體驗

在线开通,按量计费,真正的云服务!

立即開始

选择观测云版本

代码托管平台