解決PostgreSQL日志信息占用磁盤過大的問題

Posted on by

當PostgreSQL啟用日志時,若postgresql.conf日志的相關參數還使用默認值的話磁盤很容易被撐爆.因此在啟用瞭logging_collector參數時,需要對其它相關的參數進行調整.

系統默認參數如下

#log_destination = 'stderr' #日志格式,值為stderr, csvlog, syslog, and eventlog之一.
logging_collector = on #啟用日志
#log_directory = 'log' #日志文件存儲目錄
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #日志文件命名方,默認為每秒一個文件(postgresql-2017-10-18_231548.log)
#log_file_mode = 0600 #日志文件權限
#log_truncate_on_rotation = off #是否截斷日志文件

調整後的參數

log_destination = 'csvlog' #日志格式,值為stderr, csvlog, syslog, and eventlog之一.
logging_collector = on #啟用日志
log_directory = 'log' #日志文件存儲目錄
log_filename = 'postgresql-%j.log' #日志文件命名方式,最多保存一年的日志.同時要打開log_truncate_on_rotation,否則日志以追加的方式顯示在後面.
log_file_mode = 0600 #日志文件權限
log_truncate_on_rotation = on #是否截斷日志文件

重點內容

log_destination = 'csvlog'
log_filename = 'postgresql-%j.log'
log_truncate_on_rotation = on

log_destination:建議設置為csvlog,以便將日志鏈接到PostgreSQL中查看.參看Error Reporting and Logging 19.8.4. Using CSV-Format Log Output

log_filename :設置日志文件名,需結合log_truncate_on_rotation = on使用.可根據自己的需要調整, 例如:

log_filename = 'postgresql-%I.log' #最多保存12小時的日志,每小時一個文件
log_filename = 'postgresql-%H.log' #最多保存24小時的日志,每小時一個文件
log_filename = 'postgresql-%w.log' #最多保存一周的日志,每天一個文件
log_filename = 'postgresql-%d.log' #最多保存一個月的日志,每天一個文件
log_filename = 'postgresql-%j.log' #最多保存一年的日志,每天一個文件

補充：PostgreSQL 日志系統 及 設置錯誤導致磁盤塞滿案例

今天早上偶然看到QQ 群裡面有一個人,在問問題,問題不重要,主要是沒有人回答, 然後這個人馬上就用非常讓人難以接受的詞匯,問候瞭群裡面沒有回答他的一幹人等, 其實我有點可憐他, 問一個問題沒有人回答,就如此,你是經歷瞭什麼,讓你連5分鐘的耐心都沒有, 每個人都有自己的生活軌跡, 不回答你是很正常的,

終究 nothing is impossible , right?

正文

在眾多的數據庫中，POSTGRESQL 的日志的系統的豐富度和日志的詳細的程度，都是可圈可點的，在網上不少同學都在問各種POSTGRESQL的問題，其實這些問題都可以在日志中找到答案，或者提交一些日志給問題的解決者，提高問題的解決速度和問題的定位的準確度。

首先我們先從日志的詳細度來入手，log_min_messages 定義瞭日志的詳細程度，其實我們在選擇上可能會有一些糾結，糾結點在error warning notice 這三種，大部分人可能在選擇error ，出錯就報錯誤，warning 也有相關選擇，實際上選擇不同的日志的詳細度也是有相關的一些考慮

1 如果你對PG本身不熟悉，測試系統可以開啟notice ，這樣便於你去查看一些你不理解，的東西並快速的進行學習，如果是生產系統初始階段可以開啟warning 對系統的初始時期的一些問題，可能是配置上，或者系統級別的一些問題進行更深的理解，如果是穩定運行一段時間的系統則可以將其調整到 error 方面，降低一些不必要的日志的寫入，對性能和空間都有幫助。

這裡建議大傢可以使用warning 來作為常規的日志的詳細度的使用。

2 如果有人問，在語句執行的時候，我的語句被莫名其名的kill 瞭我怎麼查出來。下面的 log_min_error_statment 設置的選擇項就與其有關瞭，

例如下面的錯誤

ERROR: current transaction is aborted, commands ignored until end of transaction block
STATEMENT: SELECT * FROM mytable WHERE id = 1 FOR UPDATE;

log_min_duration_statement 是對應慢查詢的日志，當設置的值大於0 後，則超過對應設置數字秒數的SQL 語句將被記錄。

這裡需要考慮你的系統是OLAP OR OLTP 的情況，如果設置為 1秒，但你的系統裡面的SQL 語句經常要大於1秒，則你的日志中將大量充斥這樣的SQL 導致你的日志變得非常大。

說到這個MYSQL的DB會覺得PG的日志太亂瞭，MYSQL的日志大部分是分開的，這樣有利於日志的查看和分析。這裡其實也建議PG是否可以考慮將日志分開，至少分為 SLOW LOG ERROR LOG SYSTEM LOG 等等。

當然說完不足，害的說優點，讓其他數據庫DB們羨慕的應該就是下面的選項，你不會在任何一個數據庫中，找到如此豐富選擇配置

1 log_checkpoint 對當前的checkpoint的操作進行記錄，通過這個信息可以有兩點

1 有相關的監控系統可以讀這些信息，生成圖標，讓這些信息成為一個趨勢圖來對系統進行分析，並修正系統

2 也可以手工寫python程序來收集信息，直接出報告或診斷

2 log_connections 用戶的登陸信息

3 log_disconnections 用戶的斷開的登陸的信息

4 log_error_verbosity 記錄信息的詳細程度，默認default

5 log_hostname 默認記錄信息中帶有客戶端的IP地址，不帶有對方的機器名

6 log_line_prefix 相當於對日志的打印的格式和信息的設置，有些監控系統對此是有要求的，請按照你安裝的監控系統的要求配置此欄

7 log_lock_waits 記錄語句執行中的鎖等待時間

8 log_statement 對於什麼語句進行記錄，（這個與上面的無關，有語句審計的時候可能需要打開這個開關，進行語句的收集，不建議使用all 否則對於系統的負擔太重，相當於在MYSQL中開啟genernal log）

實際上很多人在操作POSTGERSQL開始的時候，是找不到日志的，因為默認PG的日志默認是不打開的，關鍵的參數在 logging_collector 默認是off,所以安裝PG後的啟動前的第一件事情就是要將這個設置變為ON ，好讓PG從開始就開始記錄日志。

另外日志的定期清理方面PG比其他的開源數據庫要做到好多瞭，因為不少人都的自己寫日志的rotate 和 clean up的腳本，PG 這裡不需要，你隻需要在 log_rotation_age中設置你要保留幾天的日志，同時 log_truncate_on_rotation 設置為on 就可以瞭，這點是非常人性化的。或者你也可以根據日志的大小進行設置如何拋棄他。

說完這些，我們來看看實際當中會遇到什麼問題，以一個案例

在搭建完PG後，系統上線前並無問題，在系統上線後第二天，有人反饋PG的日志將系統的磁盤空間大量的占用，並且7 分鐘就產生一個日志文件，後續為瞭減少相關的日志的數量較快的增長，做瞭如下修改

log_rotation_size = 100MB

將日志的容量以及重置設置的更大

修改完畢後，不重新系統，直接加載後，日志的增長頻率已經更改瞭。但日志的對磁盤空間的占用的問題還是沒有解決。

打開日志，系統記錄瞭大量如下的信息

罪魁禍首就是下面圖中的log_statement_stats 這個設置，將他打開後，系統會根據每個SQL 產生一個語句的性能方面的統計信息，可以想象如果將他打開可以看到每條語句在執行中的狀態， duration 等等信息，但這樣就會產生大量的日志，經過統計次系統1秒產生1MB的日志，（此系統每秒插入上百條數據），在關閉後，問題解決。

所以看似一個日志的設置，如果不熟悉系統，也會造成類似的問題，並且在緊急的狀態下，可能會用較長的時間來解決。實際上日志系統還有一些其他的細節，例如時區的問題，找機會可以在說說吧

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：