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 採集,並讓它能讀到來源。若在容器內寫檔,將日誌目錄掛載到檔案採集器能讀取的位置。同一個日誌串流選一條路徑,就能避免重複收集。
