postgresql csvlog 源码分析 -尊龙游戏旗舰厅官网

postgresql csvlog日志格式记录了非常多的信息，通过csv外部表，可以使用sql对日志进行分析。
文档中的例子：
http://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#runtime-config-logging-csvlog

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) ); to import a log file into this table, use the copy from command:copy postgres_log from '/full/path/to/logfile.csv' with csv;

那么csvlog每个字段的含义是什么呢？
有些字面比较好理解，有些不太好理解，不用担心，postgresql的代码非常简洁，不了解的字段就去看看代码吧：

src/include/utils/elog.h /** errordata holds the data accumulated during any one ereport() cycle.* any non-null pointers must point to palloc'd data.* (the const pointers are an exception; we assume they point at non-freeable* constant strings.)*/ typedef struct errordata {int elevel; /* error level */bool output_to_server; /* will report to server log? */bool output_to_client; /* will report to client? */bool show_funcname; /* true to force funcname inclusion */bool hide_stmt; /* true to prevent statement: inclusion */const char *filename; /* __file__ of ereport() call */int lineno; /* __line__ of ereport() call */const char *funcname; /* __func__ of ereport() call */const char *domain; /* message domain */const char *context_domain; /* message domain for context message */int sqlerrcode; /* encoded errstate */char *message; /* primary error message */char *detail; /* detail error message */char *detail_log; /* detail error message for server log only */char *hint; /* hint message */char *context; /* context message */char *schema_name; /* name of schema */char *table_name; /* name of table */char *column_name; /* name of column */char *datatype_name; /* name of datatype */char *constraint_name; /* name of constraint */int cursorpos; /* cursor index into query string */int internalpos; /* cursor index into internalquery */char *internalquery; /* text of internally-generated query */int saved_errno; /* errno at entry *//* context containing associated non-constant strings */struct memorycontextdata *assoc_context; } errordata;

write_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) {stringinfodata buf;bool print_stmt = false;/* static counter for line numbers */static long log_line_number = 0;/* has counter been reset in current process? */static int log_my_pid = 0;/** this is one of the few places where we'd rather not inherit a static* variable's value from the postmaster. but since we will, reset it when* myprocpid changes.*/if (log_my_pid != myprocpid){log_line_number = 0;log_my_pid = myprocpid;formatted_start_time[0] = '\0';}log_line_number ;initstringinfo(&buf);// 从这里开始，每个字段什么意思都可以看到，每个字段都用appendstringinfochar(&buf, ',');隔开来了。 /** timestamp with milliseconds** check if the timestamp is already calculated for the syslog message,* and use it if so. otherwise, get the current timestamp. this is done* to put same timestamp in both syslog and csvlog messages.*/if (formatted_log_time[0] == '\0')setup_formatted_log_time();appendstringinfostring(&buf, formatted_log_time);appendstringinfochar(&buf, ',');/* username */if (myprocport)appendcsvliteral(&buf, myprocport->user_name);appendstringinfochar(&buf, ',');/* database name */if (myprocport)appendcsvliteral(&buf, myprocport->database_name);appendstringinfochar(&buf, ',');/* process id */if (myprocpid != 0)appendstringinfo(&buf, "%d", myprocpid);appendstringinfochar(&buf, ',');/* remote host and port */if (myprocport && myprocport->remote_host){appendstringinfochar(&buf, '"');appendstringinfostring(&buf, myprocport->remote_host);if (myprocport->remote_port && myprocport->remote_port[0] != '\0'){appendstringinfochar(&buf, ':');appendstringinfostring(&buf, myprocport->remote_port);}appendstringinfochar(&buf, '"');}appendstringinfochar(&buf, ',');/* session id */ // session id 是两个字段组成的分别是后台进程的启动时间和pid，所以是唯一的appendstringinfo(&buf, "%lx.%x", (long) mystarttime, myprocpid);appendstringinfochar(&buf, ',');/* line number */appendstringinfo(&buf, "%ld", log_line_number);appendstringinfochar(&buf, ',');/* ps display */if (myprocport){stringinfodata msgbuf;const char *psdisp;int displen;initstringinfo(&msgbuf);psdisp = get_ps_display(&displen);appendbinarystringinfo(&msgbuf, psdisp, displen);appendcsvliteral(&buf, msgbuf.data);pfree(msgbuf.data);}appendstringinfochar(&buf, ',');/* session start timestamp */if (formatted_start_time[0] == '\0')setup_formatted_start_time();appendstringinfostring(&buf, formatted_start_time);appendstringinfochar(&buf, ',');/* virtual transaction id *//* keep vxid format in sync with lockfuncs.c */if (myproc != null && myproc->backendid != invalidbackendid)appendstringinfo(&buf, "%d/%u", myproc->backendid, myproc->lxid);appendstringinfochar(&buf, ',');/* transaction id */appendstringinfo(&buf, "%u", gettoptransactionidifany());appendstringinfochar(&buf, ',');/* error severity */appendstringinfostring(&buf, error_severity(edata->elevel));appendstringinfochar(&buf, ',');/* sql state code */appendstringinfostring(&buf, unpack_sql_state(edata->sqlerrcode));appendstringinfochar(&buf, ',');/* errmessage */appendcsvliteral(&buf, edata->message);appendstringinfochar(&buf, ',');/* errdetail or errdetail_log */ // 是否输出代码位置if (edata->detail_log)appendcsvliteral(&buf, edata->detail_log);elseappendcsvliteral(&buf, edata->detail);appendstringinfochar(&buf, ',');/* errhint */appendcsvliteral(&buf, edata->hint);appendstringinfochar(&buf, ',');/* internal query */appendcsvliteral(&buf, edata->internalquery);appendstringinfochar(&buf, ',');/* if printed internal query, print internal pos too */if (edata->internalpos > 0 && edata->internalquery != null)appendstringinfo(&buf, "%d", edata->internalpos);appendstringinfochar(&buf, ',');/* errcontext */if (!edata->hide_ctx)appendcsvliteral(&buf, edata->context);appendstringinfochar(&buf, ',');/* user query --- only reported if not disabled by the caller */if (is_log_level_output(edata->elevel, log_min_error_statement) &&debug_query_string != null &&!edata->hide_stmt)print_stmt = true;if (print_stmt)appendcsvliteral(&buf, debug_query_string);appendstringinfochar(&buf, ',');if (print_stmt && edata->cursorpos > 0)appendstringinfo(&buf, "%d", edata->cursorpos);appendstringinfochar(&buf, ',');/* file error location */if (log_error_verbosity >= pgerror_verbose){stringinfodata msgbuf;initstringinfo(&msgbuf);if (edata->funcname && edata->filename)appendstringinfo(&msgbuf, "%s, %s:%d",edata->funcname, edata->filename,edata->lineno);else if (edata->filename)appendstringinfo(&msgbuf, "%s:%d",edata->filename, edata->lineno);appendcsvliteral(&buf, msgbuf.data);pfree(msgbuf.data);}appendstringinfochar(&buf, ',');/* application name */if (application_name)appendcsvliteral(&buf, application_name);appendstringinfochar(&buf, '\n');/* if in the syslogger process, try to write messages direct to file */if (am_syslogger)write_syslogger_file(buf.data, buf.len, log_destination_csvlog);elsewrite_pipe_chunks(buf.data, buf.len, log_destination_csvlog);pfree(buf.data); }

另外需要提一下,如果写日志的是syslogger则直接写文件，如果是其他进程，则把日志发到pipe管道。
如果开启了sql审计日志，小事务高并发会受到较大的影响，优化可以从这里的代码入手哦。

总结

以上是尊龙游戏旗舰厅官网为你收集整理的postgresql csvlog 源码分析的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得尊龙游戏旗舰厅官网网站内容还不错，欢迎将尊龙游戏旗舰厅官网推荐给好友。