怎样更直观的查看金仓数据库KingbaseES数据库日志 |
您所在的位置：网站首页 › mq日志怎么看 › 怎样更直观的查看金仓数据库KingbaseES数据库日志 |
|
数据库日志相关参数：默认设置 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 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |