数据库日志相关参数：默认设置
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'sys_log' # directory where log files are written,
#log_filename = 'kingbase-%Y-%m-%d_%H%M%S.log' # log file name pattern,
#log_file_mode = 0600 # creation mode for log files,
#log_rotation_age = 1d # Automatic rotation of logfiles will（按时间自动轮换分割日志）
#log_rotation_size = 10MB # Automatic rotation of logfiles will（按大小自动轮换分割日志）
其中log_destination包括stderr、csvlog和syslog，默认值是stderr格式，csvlog记录内容会比stderr更详细
—stderr时
[kingbase2@localhost sys_log]$ tail -1 kingbase-2023-01-04_000000.log
2023-01-04 14:12:56.979 CST [3037] STATEMENT: select userid::regrole, dbid, query from sys_stat_statements order by mean_time desc limit 5;
—csvlog 时，会记录数据库、用户信息等
TEST=# alter system set log_destination ='csvlog';
ALTER SYSTEM
TEST=#
TEST=# select sys_reload_conf();
sys_reload_conf
-----------------
t
(1 row)
TEST=# show log_destination ;
log_destination
-----------------
csvlog
(1 row)
[kingbase2@localhost sys_log]$ tail -4 kingbase-2023-01-05_153654.csv
2023-01-05 15:39:13.368 CST,"system","test",4934,"[local]",63b67e91.1346,1,"idle",2023-01-05 15:38:57 CST,4/4681,0,ERROR,42601,"syntax error at or near ""switch""",,,,,,"alter system switch logfile;",14,,"kingbase_*&+_"
KingbaseES数据库日志查看方式只能到默认的sys_log目录下打开数据库日志查看，这种查看方式类似于oracle中的alert log
[kingbase2@localhost sys_log]$ cat kingbase-2023-01-05_153654.csv |egrep error
2023-01-05 15:39:13.368 CST,"system","test",4934,"[local]",63b67e91.1346,1,"idle",2023-01-05 15:38:57 CST,4/4681,0,ERROR,42601,"syntax error at or near ""switch""",,,,,,"alter system switch logfile;",14,,"kingbase_*&+_"
但是有的时候我们会被这种显示格式困扰，不容易直观的查看。
下面介绍一种日志查看方式：
TEST=# CREATE TABLE public.sys_log (
TEST(# logtime timestamp with time zone,
TEST(# loguser text,
TEST(# logdatabase text,
TEST(# logpid text,
TEST(# loghost text,
TEST(# logsessionid text,
TEST(# logcmdcount text,
TEST(# logcmdtag text,
TEST(# logsessiontime timestamp with time zone,
TEST(# logtransaction text,
TEST(# log_level text,
TEST(# logseverity text,
TEST(# logstate text,
TEST(# logmessage text,
TEST(# logdetail text,
TEST(# loghint text,
TEST(# loginternalquery text,
TEST(# loginternalquerypos text,
TEST(# logcontext text,
TEST(# logquery text,
TEST(# logquerypos text,
TEST(# loglocation text,
TEST(# logapplicationname text);
CREATE TABLE
test=# copy public.sys_log from '/opt/Kingbase/ES/V8/data/sys_log/kingbase-2023-01-05_153654.csv' with csv;
COPY 1
test=# select * from public.sys_log limit 1;
TEST=# \x
Expanded display is on.
TEST=# select * from public.sys_log limit 1;
-[ RECORD 1 ]-------+---------------------------------
logtime | 2023-01-06 05:39:13.368000+08
loguser | system
logdatabase | test
logpid | 4934
loghost | [local]
logsessionid | 63b67e91.1346
logcmdcount | 1
logcmdtag | idle
logsessiontime | 2023-01-06 05:38:57+08
logtransaction | 4/4681
log_level | 0
logseverity | ERROR
logstate | 42601
logmessage | syntax error at or near "switch"
logdetail |
loghint |
loginternalquery |
loginternalquerypos |
logcontext |
logquery | alter system switch logfile;
logquerypos | 14
loglocation |
logapplicationname | kingbase_*&+_
注：
logtransaction 记录事务的一个位置
log_level 日志的级别
logseverity 对应FATAL, ERROR, WARN, INFO, ALL,等等，根据设置的level去记录对应的日志。
这样可以过滤出想要的内容，查看更加直观简洁，但只能每一个日志文件copy进去一张表中。
还有一种方法，可以使用file_fdw插件去直接读取数据库以外的日志文件，如下所示：
test=# create extension file_fdw;
CREATE EXTENSION
test=# \dx file_fdw;
List of installed extensions
Name | Version | Schema | Description
----------+---------+--------+-------------------------------------------
file_fdw | 1.0 | public | foreign-data wrapper for flat file access
(1 row)
创建外部表接口
test=# create server ser_file_fdw foreign data wrapper file_fdw;
CREATE SERVER
查看外部服务接口
test=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
--------------+--------+----------------------
ser_file_fdw | system | file_fdw
sysaudit_svr | system | sysaudit_fdw
(2 rows)
创建外部表查看日志
test=# CREATE foreign TABLE public.sys_log_svt (
test(# logtime timestamp with time zone,
test(# loguser text,
test(# logdatabase text,
test(# logpid text,
test(# loghost text,
test(# logsessionid text,
test(# logcmdcount text,
test(# logcmdtag text,
test(# logsessiontime timestamp with time zone,
test(# logtransaction text,
test(# log_level text,
test(# logseverity text,
test(# logstate text,
test(# logmessage text,
test(# logdetail text,
test(# loghint text,
test(# loginternalquery text,
test(# loginternalquerypos text,
test(# logcontext text,
test(# logquery text,
test(# logquerypos text,
test(# loglocation text,
test(# logapplicationname text
<kingbase-2023-01-05_153654.csv',format 'csv',header 'true',delimiter ',',null ' ');
CREATE FOREIGN TABLE
test=# select * from sys_log_svt limit 1;
logtime | loguser | logdatabase | logpid | loghost | logsessionid | logcmdcount | logcmdtag |
logsessiontime | logtransaction | log_level | logseverity | logstate | logmessa
ge | logdetail | loghint
| loginternalquery | loginternalquerypos | logcontext | logquery
| logquerypos | loglocation | logapplicationname
-------------------------------+---------+-------------+--------+---------+---------------+-------------+-----------+---
---------------------+----------------+-----------+-------------+----------+--------------------------------------------
--------------------------------------+-----------+---------------------------------------------------------------------
------------------------------+------------------+---------------------+------------+-----------------------------------
--+-------------+-------------+--------------------
2023-01-06 08:42:16.201000+08 | system | test | 15396 | [local] | 63b6a982.3c24 | 1 | SELECT | 20
23-01-06 08:42:10+08 | 4/4954 | 0 | ERROR | 42883 | function to_datetime(integer, integer, inte
ger, integer, integer) does not exist | | No function matches the given name and argument types. You might nee
d to add explicit type casts. | | | | select to_datetime(2022,1,1,01,01)
; | 8 | | kingbase_*&+_
(1 row)
以上两种方法可以将数据库日志以数据库表的方式进行查看，可以过滤出想要的内容，查看更加简洁。
标签：log,text,数据库,sys,TEST,test,日志,KingbaseES From： https://www.cnblogs.com/kingbase/p/17030663.html