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