postgresql 15新特性预览：json日志 -m6米乐安卓版下载

关于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乐知乐享交流群，欢迎关注我文章的小伙伴加我微信进群吹牛唠嗑，交流技术。