关于PostgreSQL错误日志与慢查询日志收集

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 = noticelog_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 #查询重写后的SQLdebug_print_plan = on  #执行计划详细debug_pretty_print = on #对debug_print_parse,debug_print_rewritten,debug_print_plan可读性格式化#log_checkpoints = off #如果是研究pg的磁盘IO，这个需要设置为onlog_connections = on #连接日志log_disconnection = on #断开连接日志#log_duration=on #语句执行时间，对于分析

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• PostgreSQL 打印日志信息所在的源文件和行数的实例
• postgresql 切换 log、xlog日志的实现
• 解决PostgreSQL日志信息占用磁盘过大的问题
• Postgresql 如何清理WAL日志
• PostgreSQL归档配置及自动清理归档日志的操作
• Postgresql的日志配置教程详解
• PostgreSQL 日志文件的所在位置

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！