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

Column Description
logsession The session identifier (prefixed with "con").
logcmdcount The command number within a session (prefixed with "cmd").
logdatabase The name of the database.
loguser The name of the database user.
logpid The process id (prefixed with "p").
logtimemin The time of the first log message for this command.
logtimemax The time of the last log message for this command.
logduration Statement duration from start to end time.

Table 13. gp_log_master_concise view

Column Description
logtime The timestamp of the log message.
logdatabase The name of the database.
logsession The session identifier (prefixed with "con").
logcmdcount The command number within a session (prefixed with "cmd").
logmessage Log or error message text.

Table 12. gp_log_database view

Column Description
logtime The timestamp of the log message.
loguser The name of the database user.
logdatabase The name of the database.
logpid The associated process id (prefixed with "p").
logthread The associated thread count (prefixed with "th").
loghost The segment or master host name.
logport The segment or master port.
logsessiontime Time session connection was opened.
logtransaction Global transaction id.
logsession The session identifier (prefixed with "con").
logcmdcount The command number within a session (prefixed with "cmd").
logsegment The segment content identifier (prefixed with "seg" for primary or "mir" for mirror. The master always has a content id of -1).
logslice The slice id (portion of the query plan being executed).
logdistxact Distributed transaction id.
loglocalxact Local transaction id.
logsubxact Subtransaction id.
logseverity LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2.
logstate SQL state code associated with the log message.
logmessage Log or error message text.
logdetail Detail message text associated with an error message.
loghint Hint message text associated with an error message.
logquery The internally-generated query text.
logquerypos The cursor index into the internally-generated query text.
logcontext The context in which this message gets generated.
logdebug Query string with full detail for debugging.
logcursorpos The cursor index into the query string.
logfunction The function in which this message is generated.
logfile The log file in which this message is generated.
logline The line in the log file in which this message is generated.
logstack Full text of the stack trace associated with this message.

Table 14. gp_log_system view

Column Description
logtime The timestamp of the log message.
loguser The name of the database user.
logdatabase The name of the database.
logpid The associated process id (prefixed with "p").
logthread The associated thread count (prefixed with "th").
loghost The segment or master host name.
logport The segment or master port.
logsessiontime Time session connection was opened.
logtransaction Global transaction id.
logsession The session identifier (prefixed with "con").
logcmdcount The command number within a session (prefixed with "cmd").
logsegment The segment content identifier (prefixed with "seg" for primary or "mir" for mirror. The master always has a content id of -1).
logslice The slice id (portion of the query plan being executed).
logdistxact Distributed transaction id.
loglocalxact Local transaction id.
logsubxact Subtransaction id.
logseverity LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2.
logstate SQL state code associated with the log message.
logmessage Log or error message text.
logdetail Detail message text associated with an error message.
loghint Hint message text associated with an error message.
logquery The internally-generated query text.
logquerypos The cursor index into the internally-generated query text.
logcontext The context in which this message gets generated.
logdebug Query string with full detail for debugging.
logcursorpos The cursor index into the query string.
logfunction The function in which this message is generated.
logfile The log file in which this message is generated.
logline The line in the log file in which this message is generated.
logstack Full text of the stack trace associated with this message.

参考

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

上一篇:横向滑动的 ListView


下一篇:RMAN备份与恢复之UNDO表空间丢失