暂无图片
暂无图片
5
暂无图片
暂无图片
暂无图片

PostgreSQL 15新特性预览:json日志

原创 多米爸比 2022-06-04
2083

关于PostgreSQL 15新特性的文章,之前写过两篇文章:
<<PG 15devel Merge Into语法测试>>
<<PostgreSQL 15新特性预览:版本兼容性>>

还有一篇部分新特性实验的文档:
<<PostgreSQL 15新特性预览>>

本文将详细介绍数据库日志json格式的使用方法。

一、日志配置

数据库postgresql.conf文件主要设置下面两个参数:

postgres=# show logging_collector;
 logging_collector 
-------------------
 on
(1 row)

postgres=# show log_destination;
 log_destination 
-----------------
 jsonlog
(1 row)

与csv日志格式类似,log_filename并不需要把log后缀修改为json。我的log_filename配置如下:

postgres=# show log_filename;
 log_filename  
---------------
 pg_log_%u.log
(1 row)

打开logging_collector日志开关,设置log_destination为jsonlog,数据库加载完配置之后就可以观察到*.json的文件了。

[postgres@pg ~]$ ll /opt/pgdata1500/log/
total 590316
-rw------- 1 postgres dba    545218 May 23 18:37 pg_log_1.json
-rw------- 1 postgres dba       336 May 23 15:42 pg_log_1.log
-rw------- 1 postgres dba     21869 May 24 14:01 pg_log_2.json
-rw------- 1 postgres dba       167 May 24 07:28 pg_log_2.log
-rw------- 1 postgres dba     13363 May 25 22:09 pg_log_3.json
-rw------- 1 postgres dba       167 May 25 09:33 pg_log_3.log
-rw------- 1 postgres dba     99502 May 26 21:00 pg_log_4.json
-rw------- 1 postgres dba       668 May 26 15:10 pg_log_4.log
-rw------- 1 postgres dba     97839 May 20 18:01 pg_log_5.json
-rw------- 1 postgres dba       835 May 20 17:35 pg_log_5.log
-rw------- 1 postgres dba    365840 Jun  4 09:07 pg_log_6.json
-rw------- 1 postgres dba 603164236 Jun  4 08:44 pg_log_6.log
-rw------- 1 postgres dba    134464 May 22 20:46 pg_log_7.json
-rw------- 1 postgres dba       835 May 22 12:29 pg_log_7.log

二、json日志优势

json格式日志相比csv格式日志主要有以下两个优势:

  • 可正确处理日志跨行问题
  • 保持干净有效的日志输出项

第一个优势比较明显,我们使用csv日志时,如果查询语句比较复杂,内容很长,占多行,分析日志时将不能正确解析;json日志保留了语句的换行符,观察下面json日志的message:

{
"timestamp":"2022-04-20 10:36:26.125 CST",
"user":"postgres",
"dbname":"postgres",
"pid":3881,
"remote_host":"[local]",
"session_id":"625f71aa.f29",
"line_num":1,
"ps":"idle",
"session_start":"2022-04-20 10:36:26 CST",
"vxid":"3/2",
"txid":0,
"error_severity":"LOG",
"message":"statement: select\n    relname,\n    relkind\nfrom\n    pg_class\nlimit 1;",
"application_name":"psql",
"backend_type":"client backend",
"query_id":0
}

第二个优势也非常有效,使用csv日志时,是固定的日志项,后台与后端进程的日志项里并不都是填满了内容,csv日志可以看到很多的日志空项;json日志对这一问题做了优化,精简了无实际内容的日志项,使得每行日志更加干净、清爽。

{
"timestamp":"2022-04-20 10:24:47.710 CST",
"pid":3383,
"session_id":"625f67e3.d37",
"line_num":12,
"session_start":"2022-04-20 09:54:43 CST",
"txid":0,
"error_severity":"LOG",
"message":"checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.303 s, sync=0.135 s, total=0.809 s; sync files=4, longest=0.127 s, average=0.034 s; distance=0 kB, estimate=114 kB",
"backend_type":"checkpointer",
"query_id":0
}

三、json日志进阶使用

对csv日志可以使用file_fdw进行数据库映射,然后在数据库里进行SQL分析,主要配置过程如下:
创建扩展

create extension file_fdw;

创建server

create server pglog foreign data wrapper file_fdw;

创建日志映射表

CREATE FOREIGN TABLE public.postgres_log(  
  log_time timestamp(3) with time zone,  
  user_name text,  
  database_name text,  
  process_id integer,
  connection_from text,
  session_id text,  
  session_line_num bigint,  
  command_tag text,  
  session_start_time timestamp with time zone,  
  virtual_transaction_id text,  
  transaction_id bigint,  
  error_severity text,  
  sql_state_code text,  
  message text,  
  detail text,  
  hint text,  
  internal_query text,  
  internal_query_pos integer,  
  context text,  
  query text,  
  query_pos integer,  
  location text,  
  application_name text,
  backend_type text,
  leader_pid integer,
  query_id bigint
) SERVER pglog  
OPTIONS ( filename '/opt/pgdata1402/log/pg_log_1.csv', header 'true', format 'csv' );

json日志同样也可以映射到数据库,并且字段可以映射成json类型,而且数据库中有大量的json函数可以直接使用。

通过file_fdw映射本地json日志文件

与前面csv日志类似,使用file_fdw来创建本地文件server

create server local_file_server foreign data wrapper file_fdw; 

由于服务器日志配置的是按周每天循环生成,我们也按单个文件进行映射

CREATE FOREIGN TABLE pg_log_5(  
jsonstr text
 ) SERVER local_file_server  
OPTIONS (program 'cat /opt/pgdata1500/log/pg_log_5.json |jq -cMR'); 

这里我们也借助了操作系统的jq工具,把数据库日志以原本的数据格式进行映射。

使用json构造函数转换为json类型

create materialized view mv_pg_log_5 as select json(trim(jsonstr,'"')) as jsonlog from pg_log_5;

同时我们也使用物化视图进行数据物化,随着数据量变大,可以对物化视图创建索引,来进行高效分析。

使用json函数进行日志分析

经过前面的配置,接下来我们可以在SQL层从任意连接的客户端来进行日志分析了:

注意:下面几个json函数是15新增的功能,但从beta4版本被回退,大家测试的时候会出错。

例如使用is not json验证数据是否都满足json格式

postgres=# select * from mv_pg_log_5 where jsonlog is not json;
 jsonlog 
---------
(0 rows)

使用json_query函数进行查询

postgres=# select distinct json_query(jsonlog::jsonb,'$.error_severity') from mv_pg_log_5;
 json_query 
------------
 "LOG"
 "WARNING"
 "FATAL"
 "ERROR"
(4 rows)

使用json_table函数对某条日志进行表转换列映射

select * from json_table(
    (select jsonlog::jsonb from mv_pg_log_5 where query id = ...),
    '$[*]'
    COLUMNS (
        column_a int path '$.pid',
	...
        column_b varchar path '$.backend_type'
    )
);



更多的json日志分析姿势等你来探索(欢迎评论区补充留言):
https://www.postgresql.org/docs/15/functions-json.html

保持联系

从2019年12月开始写第一篇文章,分享的初心一直在坚持,本人现在组建了一个PG乐知乐享交流群,欢迎关注我文章的小伙伴加我微信进群吹牛唠嗑,交流技术。

456.png

最后修改时间:2022-11-08 09:51:12
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论