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展示

此处内容省略，网上有大量关于Elasticsearch存储与kibana展示的文章。

转载于:https://my.oschina.net/207miner/blog/3021932