Greenplum csvlog(日志数据)检索、释义(gp_toolkit.gp_log*)

标签

PostgreSQL , Greenplum , csvlog , gp_toolkit

背景

由于GP为分布式数据库，当查看它的一些日志时，如果到服务器上查看，会非常的繁琐，而且不好排查问题。

例如这是某个节点的日志

2018-08-23 15:34:30.004070 CST,"digoal","postgres",p12775,th-1756479360,"127.0.0.1","52802",2018-08-23 15:34:29 CST,0,con7,cmd4,seg-1,,dx4,,sx1,"LOG","00000","statement: COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",,,,,,"COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",0,,"postgres.c",1590,  
  
2018-08-23 15:34:30.004098 CST,"digoal","postgres",p12775,th-1756479360,"127.0.0.1","52802",2018-08-23 15:34:29 CST,0,con7,cmd4,seg-1,,dx4,,sx1,"ERROR","25P02","current transaction is aborted, commands ignored until end of transaction block",,,,,,"COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",0,,"postgres.c",1669,  
2018-08-23 15:34:30.004113 CST,"digoal","postgres",p12775,th-1756479360,"127.0.0.1","52802",2018-08-23 15:34:29 CST,0,con7,cmd4,seg-1,,dx4,,sx1,"LOG","00000","An exception was encountered during the execution of statement: COPY part FROM '/tmp/dss-data/part.csv' WITH csv DELIMITER '|';",,,,,,,0,,,,  

Greenplum提供了gp_toolkit.gp_log...视图，用来汇聚日志，方便查看。

postgres=# \dv gp_toolkit.gp_log*  
                       List of relations  
   Schema   |          Name          | Type | Owner  | Storage   
------------+------------------------+------+--------+---------  
 gp_toolkit | gp_log_command_timings | view | digoal | none  
 gp_toolkit | gp_log_database        | view | digoal | none  
 gp_toolkit | gp_log_master_concise  | view | digoal | none  
 gp_toolkit | gp_log_system          | view | digoal | none  
(4 rows)  

gp_toolkit.gp_log_* 视图

• gp_log_command_timings (只输出会话，PID，时间，如果关注运行较长时间的详细信息，可根据会话，PID在gp_log_system中定位)

This view uses an external table to read the log files on the master and report the execution time of SQL commands executed in a database session.   
The use of this view requires superuser permissions.  

• gp_log_master_concise (只有master节点的日志)

This view uses an external table to read a subset of the log fields from the master log file.   
The use of this view requires superuser permissions.  

• gp_log_system (汇聚master,segment,mirror节点的日志，含所有数据库)

This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists all log entries.   
Associated log entries can be identified by the session id (logsession) and command id (logcmdcount).   
The use of this view requires superuser permissions.  

• gp_log_database (汇聚master,segment,mirror节点的日志，含当前数据库)

This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists log entries associated with the current database.   
Associated log entries can be identified by the session id (logsession) and command id (logcmdcount).   
The use of this view requires superuser permissions.  

使用举例

select * from gp_toolkit.gp_log_database limit 1000;  
  
-[ RECORD 5 ]--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
logtime        | 2018-08-24 02:42:24.084998+08  
loguser        | digoal  
logdatabase    | postgres  
logpid         | p1598  
logthread      | th1426366592  
loghost        | 127.0.0.1  
logport        | 47598  
logsessiontime | 2018-08-24 02:41:42+08  
logtransaction | 0  
logsession     | con8  
logcmdcount    | cmd6  
logsegment     | seg-1  
logslice       |   
logdistxact    | dx8  
loglocalxact   |   
logsubxact     | sx1  
logseverity    | LOG  
logstate       | 00000  
logmessage     | statement: SELECT n.nspname as "Schema",  
               |   c.relname as "Name",  
               |   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",  
               |   pg_catalog.pg_get_userbyid(c.relowner) as "Owner", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' END as "Storage"  
               |   
               | FROM pg_catalog.pg_class c  
               |      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace  
               | WHERE c.relkind IN ('r','')  
               | AND c.relstorage IN ('h', 'a', 'c','')  
               |       AND n.nspname <> 'pg_catalog'  
               |       AND n.nspname <> 'information_schema'  
               |       AND n.nspname !~ '^pg_toast'  
               |   AND pg_catalog.pg_table_is_visible(c.oid)  
               | ORDER BY 1,2;  
logdetail      |   
loghint        |   
logquery       |   
logquerypos    |   
logcontext     |   
logdebug       | SELECT n.nspname as "Schema",  
               |   c.relname as "Name",  
               |   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",  
               |   pg_catalog.pg_get_userbyid(c.relowner) as "Owner", CASE c.relstorage WHEN 'h' THEN 'heap' WHEN 'x' THEN 'external' WHEN 'a' THEN 'append only' WHEN 'v' THEN 'none' WHEN 'c' THEN 'append only columnar' END as "Storage"  
               |   
               | FROM pg_catalog.pg_class c  
               |      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace  
               | WHERE c.relkind IN ('r','')  
               | AND c.relstorage IN ('h', 'a', 'c','')  
               |       AND n.nspname <> 'pg_catalog'  
               |       AND n.nspname <> 'information_schema'  
               |       AND n.nspname !~ '^pg_toast'  
               |   AND pg_catalog.pg_table_is_visible(c.oid)  
               | ORDER BY 1,2;  
logcursorpos   | 0  
logfunction    |   
logfile        | postgres.c  
logline        | 1590  
logstack       |   
-[ RECORD 6 ]--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
logtime        | 2018-08-24 02:43:12.031548+08  
loguser        | postgres  
logdatabase    | postgres  
logpid         | p1705  
logthread      | th1426366592  
loghost        | [local]  
logport        |   
logsessiontime | 2018-08-24 02:43:12+08  
logtransaction | 0  
logsession     | con9  
logcmdcount    |   
logsegment     | seg-1  
logslice       |   
logdistxact    |   
loglocalxact   |   
logsubxact     | sx1  
logseverity    | FATAL  
logstate       | 28000  
logmessage     | no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off  
logdetail      |   
loghint        |   
logquery       |   
logquerypos    |   
logcontext     |   
logdebug       |   
logcursorpos   | 0  
logfunction    |   
logfile        | auth.c  
logline        | 602  
logstack       |   

postgres=# select * from gp_toolkit.gp_log_command_timings order by logsession,logcmdcount limit 100;  
 logsession | logcmdcount | logdatabase | loguser  | logpid |          logtimemin           |          logtimemax           |   logduration     
------------+-------------+-------------+----------+--------+-------------------------------+-------------------------------+-----------------  
 con10      | cmd1        | postgres    | digoal   | p14843 | 2018-08-24 05:43:49.156082+08 | 2018-08-24 05:43:49.156082+08 | 00:00:00  
 con10      | cmd2        | postgres    | digoal   | p14843 | 2018-08-24 05:43:49.158241+08 | 2018-08-24 05:43:49.158241+08 | 00:00:00  
 con10      | cmd3        | postgres    | digoal   | p14843 | 2018-08-24 05:43:51.984189+08 | 2018-08-24 05:43:51.984189+08 | 00:00:00  
 con10      | cmd4        | postgres    | digoal   | p14843 | 2018-08-24 05:43:51.987823+08 | 2018-08-24 05:43:51.987823+08 | 00:00:00  
 con10      | cmd5        | postgres    | digoal   | p14843 | 2018-08-24 05:43:53.144745+08 | 2018-08-24 05:43:53.159263+08 | 00:00:00.014518  
 con10      | cmd6        | postgres    | digoal   | p14843 | 2018-08-24 05:43:56.584268+08 | 2018-08-24 05:43:56.584268+08 | 00:00:00  
 con10      | cmd7        | postgres    | digoal   | p14843 | 2018-08-24 05:43:56.586394+08 | 2018-08-24 05:43:56.586394+08 | 00:00:00  
 con10      | cmd8        | postgres    | digoal   | p14843 | 2018-08-24 05:43:58.292289+08 | 2018-08-24 05:43:58.294961+08 | 00:00:00.002672  
 con100     | cmd1        | postgres    | postgres | p8206  | 2018-08-24 05:18:03.279438+08 | 2018-08-24 05:18:03.280025+08 | 00:00:00.000587  

select * from gp_toolkit.gp_log_master_concise limit 1000;  
logtime     | 2018-08-24 02:40:34.295103+08  
logdatabase | template1  
logsession  |   
logcmdcount | cmd21  
logseverity | LOG  
logmessage  | statement:   
            |                 SELECT oid, fsname  
            |                 FROM pg_filespace  
            |                 ORDER BY fsname;  

csvlog字段含义

对于PostgreSQL来说，csvlog字段含义可以在代码，或手册中找到。

1、手册介绍

https://www.postgresql.org/docs/devel/static/runtime-config-logging.html

CREATE TABLE 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,  
  PRIMARY KEY (session_id, session_line_num)  
);  

2、通过file_fdw外部表，PG也可以在SQL中查看日志

https://www.postgresql.org/docs/devel/static/file-fdw.html

CREATE EXTENSION file_fdw;  
  
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;  
  
CREATE FOREIGN TABLE pglog (  
  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  
) SERVER pglog  
OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );  

3、通过源码查看csvlog释义

src/backend/utils/error/elog.c

/*  
 * Constructs the error message, depending on the Errordata it gets, in a CSV  
 * format which is described in doc/src/sgml/config.sgml.  
 */  
static void  
write_csvlog(ErrorData *edata)  
{  
...  

Greenplum CSVLOG释义

https://greenplum.org/docs/5100/ref_guide/gp_toolkit.html#topic16

Table 11. gp_log_command_timings view

Table 13. gp_log_master_concise view

Table 12. gp_log_database view

Table 14. gp_log_system view

参考

https://greenplum.org/docs/5100/ref_guide/gp_toolkit.html#topic16

https://www.postgresql.org/docs/devel/static/runtime-config-logging.html

https://www.postgresql.org/docs/devel/static/file-fdw.html

src/backend/utils/error/elog.c