filebeat+logstash收集postgres慢查询日志

心已赠人 2023-02-13 14:43 13阅读 0赞

背景：在 [win10下docker启动postgres][win10_docker_postgres] 、[python连接postgres，创建表和插入数据][python_postgres] 以及ELK+Filebeat的基础上进行

## 一、Filebeat端处理 ##

### 1. 配置文件修改 ###

**1.1 filebeat.yml配置文件新增数据源类型**

- type: log
      enabled: true
      paths:
        - /usr/share/filebeat/log/facilities/*/*.log
      tags: ["facility"]
      fields:
        env: test
        source: facility
        type: logs
      fields_under_root: true
      multiline.pattern: '^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}.\d{3}'
      multiline.negate: true
      multiline.match: after
      include_lines: [ 'duration' ]

** 1.2 logstash.conf配置文件中新增过滤类型处理**

if [source] == "facility" {
            grok {
                match => [ "message", "(?m)^%{TIMESTAMP_ISO8601:timestamp}%{SPACE}%{TZ:timezone}%{SPACE}\[%{POSINT:pid}\]%{SPACE}%{WORD:logLevel}:%{SPACE}duration:%{SPACE}%{NUMBER:duration}%{SPACE}ms%{SPACE}statement:%{SPACE}%{GREEDYDATA:message}" ]
                overwrite => [ "message" ]
            }
        }

**1.3 my-postgres.conf 文件在默认配置基础上新增配置，开启日志文件慢查询记录**

logging_collector = on
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
    log_directory = '/var/log/postgresql'
    log_min_duration_statement = 1200	# milliseconds,记录执行1.2秒及以上的语句

**1.4 docker-compose-postgres.yml文件修改默认日志挂载路径**

- /d/usr/share/postgresql/log:/var/log/postgresql
    修改为
    - /d/usr/share/filebeat/facilities/log/postgresql:/var/log/postgresql

###  2. 重启容器 ###

**2.1 重启postgres、filebeat、logstash容器**

docker restart postgres filebeat logstash

### 3. 生成慢查询日志 ###

**3.1 连接postgres数据库，执行查询语句**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70][]

### 4. 查看结果 ###

**4.1 日志文件内容**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 1][]

**4.2 日志收集结果 **

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 2][]

## 二、Logstash端处理 ##

### 1. 配置文件修改 ###

**1.1 filebeat.yml配置文件注释掉include\_lines行**

# include_lines: [ 'duration' ]

**1.2 logstash.conf配置文件过滤部分修改为**

if [source] == "facility" {
            if "duration" in [message] {
                grok {
                    match => [ "message", "(?m)^%{TIMESTAMP_ISO8601:timestamp}%{SPACE}%{TZ:timezone}%{SPACE}\[%{POSINT:pid}\]%{SPACE}%{WORD:logLevel}:%{SPACE}duration:%{SPACE}%{NUMBER:duration}%{SPACE}ms%{SPACE}statement:%{SPACE}%{GREEDYDATA:message}" ]
                    overwrite => [ "message" ]
                }
            } else {
                # 删除不匹配的行
                drop {}
            }
        }

### 2. 重启容器 ###

**2.1 重启filebeat、logstash容器**

docker restart filebeat logstash

### 3. 追加日志 ###

**3.1 用文本编辑器直接在原日志文件内追加日志内容**

2020-05-29 08:35:21.546 UTC [26] LOG:  redo done at 2/26326390
    2020-05-29 08:35:22.214 UTC [1] LOG:  database system is ready to accept connections
    2020-05-29 08:36:19.817 UTC [33] LOG:  duration: 33071.529 ms  statement: SELECT * FROM test.public.company where address = '深圳'
    2020-05-29 08:36:19.817 UTC [33] LOG:  duration: 33071.529 ms  statement: SELECT * FROM test.public.company where address = '北京'
    2020-05-29 08:35:22.214 UTC [1] LOG:  database system is ready to accept connections

### 4. 查看结果 ###

**4.1 日志收集结果**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 3][]

[win10_docker_postgres]: https://blog.csdn.net/zsx18273117003/article/details/106397031
[python_postgres]: https://blog.csdn.net/zsx18273117003/article/details/106411167
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200529170436956.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200529170716851.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200529170849613.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200529173516202.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70