PostgreSQL錯誤日志與慢查詢日志對于線上系統分析、問題預警、問題排查起到非常重要的作用，在此不做贅述。
此文檔記錄錯誤日志與慢查詢日志的收集、分析與存儲展示的方法。
一、總體思路
PostgreSQL日志輸出可以配置多種多樣的格式，其中以csvlog格式輸出的日志信息最全面。但是CSV日志只能以本地文件的方式收集，不能直接寫入網絡，實時上傳日志服務器。
日志收集：
PostgreSQL服務器分布在不同的機器，我們使用rsyslog客戶端-服務器的方式來收集日志到日志服務器。具體方法：在PostgreSQL服務器部署客戶端，在日志服務器部署服務器，客戶端監控日志文件的變化，實時將新增日志上傳到服務器，服務器根據不同客戶端上傳的日志，分別記錄在不同的文件中。
此方法的好處是在PostgreSQL服務器本地可以保存一份原始的完整的csv日志，供全量信息查看與分析。
日志分析：
使用Logstash進行日志分析，Logstash是一個開源數據收集引擎，具有實時管道功能。Logstash可以動態地將來自不同文件的數據統一起來，進行數據篩選清洗，并將數據標準化到你所選擇的目的地。
日志存儲展示：
使用傳統的Elasticsearch進行數據存儲，Kibana進行數據展示。
二、rsyslog服務器端配置
新增以下內容到rsyslog配置文件/etc/rsyslog.conf，并重啟rsyslog服務。
$PreserveFQDN on #用于正確的獲取主機名
$FileOwner root #存儲的文件屬主
$FileGroup root #文件屬主
$FileCreateMode 0644 #生成的文件權限
$DirCreateMode 0755 #生成的目錄權限
$Umask 0022
$PrivDropToUser root #可以刪除日志的用戶
$PrivDropToGroup root #可以刪除日志的用戶組
module(load="imuxsock")
module(load="imklog")
module(load="imudp")
#input(type="imudp" port="514")
module(load="imtcp" MaxSessions="500")
input(type="imtcp" port="514")
$template linefmt,"%msg:2:$%\n" #接收日志的格式(去掉開頭的空格）
$template pgloglocation,"/data/pglogs/%hostname%/%$YEAR%-%$MONTH%-%$DAY%.csv"
:rawmsg,contains,"pg_5432" ?pgloglocation;linefmt
##變量：%fromhost-ip%
三、rsyslog客戶端配置
新建配置文件/etc/rsyslog.d/10-pg.conf，并重啟rsyslog服務。
cat /etc/rsyslog.d/10-pg.conf
module(load="imuxsock")
module(load="imklog")
module(load="imfile")
#module(load="imudp")
#input(type="imudp" port="514")
module(load="imtcp" MaxSessions="500")
input(type="imtcp" port="514")
ruleset(name="remote"){
action(type="omfwd"
target="x.x.x.x" #日志服務器IP地址
port="514" #端口
protocol="tcp" #使用協議
queue.type="linkedList" #使用異步處理
queue.spoolDirectory="/var/log/rsyslog" #隊列目錄
queue.fileName="pglog" #隊列名稱
queue.maxDiskSpace="1g" #隊列占最大磁盤空間
queue.saveOnShutdown="on" #保存內存數據如果rsyslog關閉
action.resumeRetryCount="-1" #無限重試插入失敗
)
stop
}
input(
type="imfile"
File="/pg/data/log/*.csv" #PG服務器日志路徑
Facility="local1"
Severity="info"
Tag="pg_5432" #定義日志標簽，重要，服務端根據這個標簽可以識別日志
PersistStateInterval="1" #回寫偏移量數據到文件間隔時間(秒)，根據實際情況而定
deleteStateOnFileDelete="on"
reopenOnTruncate="on"
Ruleset="remote" #rsyslog.conf中定義的rule名稱
)
四、logstash配置
在日志服務器上編輯好配置文件后，啟動logstash即可。配置文件如下：
input {
file {
path => ["/data/pglogs/*/*.csv"]
start_position => "end"
codec => multiline {
pattern => "^20[0-9]{2}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}"
negate => true
what => "previous"
}
}
}
filter {
csv {
separator => ","
columns => ["log_time","user_name","database_name","process_id","connection_from","session_id","session_line_num","command_tag","session_start_time","virtual_transaction_id","transaction_id","error_severity","sql_state_code","message","detail","hint","internal_query","internal_query_pos","context","query","query_pos","location","application_name"]
convert => {
"process_id" => "integer"
"session_line_num" => "integer"
"transaction_id" => "integer"
"internal_query_pos" => "integer"
"query_pos" => "integer"
}
skip_empty_columns => true
}
mutate{
split => { "log_time" => " CST" }
add_field => { "log_time_tmp" => "%{[log_time][0]}" }
remove_field => ["log_time"]
}
date {
match => ["log_time_tmp", "yyyy-MM-dd HH:mm:ss.SSS"]
target => "@timestamp"
locale => "cn"
remove_field => ["log_time_tmp"]
}
if "duration:" in [message] and "ms" in [message] and "statement:" in [message] {
grok{
match => { "message" => "duration: %{NUMBER:duration} ms" }
}
mutate{
split => { "message" => "statement: " }
add_field => { "statement" => "%{[message][1]}" }
remove_field => ["message"]
}
}
mutate{
split => { "path" => "/" }
add_field => { "db_host" => "%{[path][3]}" }
remove_field => ["path", "host"]
convert => { "duration" => "float" }
}
}
output {
if [error_severity] == "ERROR" or [error_severity] == "FATAL" or [error_severity] == "PANIC" {
elasticsearch {
hosts => ["x.x.x.x:x", "x.x.x.x:x"]
index => "pg_error"
id => "elasticsearch_pg_error"
}
}else if [duration] and [statement] {
elasticsearch {
hosts => ["x.x.x.x:x", "x.x.x.x:x"]
index => "pg_slow"
id => "elasticsearch_pg_slow"
}
}
}
五、此處省略了Elasticsearch存儲與Kibana展示，這里不多介紹
補充：PostgreSQL開發者模式錯誤反饋與日志設置
####when何時記錄
#client_min_messages = notice
log_min_messages = debug5 #debug級別是提供給開發人員使用的，這個可以看到程序調用的信息以及SQL轉化為數據結構的信息，每分鐘的級別
####where記錄到哪里
#log_destination = 'stderr'
logging_collector = on #打開日志收集
log_directory = 'pg_log' #日志目錄
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
####what寫什么日志
debug_print_parse = on #解析樹
debug_print_rewritten = on #查詢重寫后的SQL
debug_print_plan = on #執行計劃詳細
debug_pretty_print = on #對debug_print_parse,debug_print_rewritten,debug_print_plan可讀性格式化
#log_checkpoints = off #如果是研究pg的磁盤IO，這個需要設置為on
log_connections = on #連接日志
log_disconnection = on #斷開連接日志
#log_duration=on #語句執行時間，對于分析
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL 打印日志信息所在的源文件和行數的實例
- postgresql 切換 log、xlog日志的實現
- 解決PostgreSQL日志信息占用磁盤過大的問題
- Postgresql 如何清理WAL日志
- PostgreSQL歸檔配置及自動清理歸檔日志的操作
- Postgresql的日志配置教程詳解
- PostgreSQL 日志文件的所在位置
