關(guān)于PostgreSQL 15新特性的文章，之前寫(xiě)過(guò)兩篇文章：
<<PG 15devel Merge Into語(yǔ)法測(cè)試>>
<<PostgreSQL 15新特性預(yù)覽：版本兼容性>>
還有一篇部分新特性實(shí)驗(yàn)的文檔：
<<PostgreSQL 15新特性預(yù)覽>>
本文將詳細(xì)介紹數(shù)據(jù)庫(kù)日志json格式的使用方法。
一、日志配置
數(shù)據(jù)庫(kù)postgresql.conf文件主要設(shè)置下面兩個(gè)參數(shù)：
postgres=# show logging_collector;
logging_collector
-------------------
on
(1 row)
postgres=# show log_destination;
log_destination
-----------------
jsonlog
(1 row)
與csv日志格式類(lèi)似，log_filename并不需要把log后綴修改為json。我的log_filename配置如下：
postgres=# show log_filename;
log_filename
---------------
pg_log_%u.log
(1 row)
打開(kāi)logging_collector日志開(kāi)關(guān)，設(shè)置log_destination為jsonlog，數(shù)據(jù)庫(kù)加載完配置之后就可以觀察到*.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日志優(yōu)勢(shì)
json格式日志相比csv格式日志主要有以下兩個(gè)優(yōu)勢(shì)：
- 可正確處理日志跨行問(wèn)題
- 保持干凈有效的日志輸出項(xiàng)
第一個(gè)優(yōu)勢(shì)比較明顯，我們使用csv日志時(shí)，如果查詢(xún)語(yǔ)句比較復(fù)雜，內(nèi)容很長(zhǎng)，占多行，分析日志時(shí)將不能正確解析；json日志保留了語(yǔ)句的換行符，觀察下面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
}
第二個(gè)優(yōu)勢(shì)也非常有效，使用csv日志時(shí)，是固定的日志項(xiàng)，后臺(tái)與后端進(jìn)程的日志項(xiàng)里并不都是填滿(mǎn)了內(nèi)容，csv日志可以看到很多的日志空項(xiàng)；json日志對(duì)這一問(wèn)題做了優(yōu)化，精簡(jiǎn)了無(wú)實(shí)際內(nèi)容的日志項(xiàng)，使得每行日志更加干凈、清爽。
{
"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日志進(jìn)階使用
對(duì)csv日志可以使用file_fdw進(jìn)行數(shù)據(jù)庫(kù)映射，然后在數(shù)據(jù)庫(kù)里進(jìn)行SQL分析，主要配置過(guò)程如下：
創(chuàng)建擴(kuò)展
create extension file_fdw;
創(chuàng)建server
create server pglog foreign data wrapper file_fdw;
創(chuàng)建日志映射表
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日志同樣也可以映射到數(shù)據(jù)庫(kù)，并且字段可以映射成json類(lèi)型，而且數(shù)據(jù)庫(kù)中有大量的json函數(shù)可以直接使用。
通過(guò)file_fdw映射本地json日志文件
與前面csv日志類(lèi)似，使用file_fdw來(lái)創(chuàng)建本地文件server
create server local_file_server foreign data wrapper file_fdw;
由于服務(wù)器日志配置的是按周每天循環(huán)生成，我們也按單個(gè)文件進(jìn)行映射
CREATE FOREIGN TABLE pg_log_5(
jsonstr text
) SERVER local_file_server
OPTIONS (program 'cat /opt/pgdata1500/log/pg_log_5.json |jq -cMR');
這里我們也借助了操作系統(tǒng)的jq工具，把數(shù)據(jù)庫(kù)日志以原本的數(shù)據(jù)格式進(jìn)行映射。
使用json構(gòu)造函數(shù)轉(zhuǎn)換為json類(lèi)型
create materialized view mv_pg_log_5 as select json(trim(jsonstr,'"')) as jsonlog from pg_log_5;
同時(shí)我們也使用物化視圖進(jìn)行數(shù)據(jù)物化，隨著數(shù)據(jù)量變大，可以對(duì)物化視圖創(chuàng)建索引，來(lái)進(jìn)行高效分析。
使用json函數(shù)進(jìn)行日志分析
經(jīng)過(guò)前面的配置，接下來(lái)我們可以在SQL層從任意連接的客戶(hù)端來(lái)進(jìn)行日志分析了：
注意：下面幾個(gè)json函數(shù)是15新增的功能，但從beta4版本被回退，大家測(cè)試的時(shí)候會(huì)出錯(cuò)。
例如使用is not json驗(yàn)證數(shù)據(jù)是否都滿(mǎn)足json格式
postgres=# select * from mv_pg_log_5 where jsonlog is not json;
jsonlog
---------
(0 rows)
使用json_query函數(shù)進(jìn)行查詢(xún)
postgres=# select distinct json_query(jsonlog::jsonb,'$.error_severity') from mv_pg_log_5;
json_query
------------
"LOG"
"WARNING"
"FATAL"
"ERROR"
(4 rows)
使用json_table函數(shù)對(duì)某條日志進(jìn)行表轉(zhuǎn)換列映射
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日志分析姿勢(shì)等你來(lái)探索(歡迎評(píng)論區(qū)補(bǔ)充留言)：
https://www.postgresql.org/docs/15/functions-json.html
保持聯(lián)系
從2019年12月開(kāi)始寫(xiě)第一篇文章，分享的初心一直在堅(jiān)持，本人現(xiàn)在組建了一個(gè)PG樂(lè)知樂(lè)享交流群，歡迎關(guān)注我文章的小伙伴加我微信進(jìn)群吹牛嘮嗑，交流技術(shù)。
