Using CSV-Format Log Output

时间:2023-01-06 22:49:59

Including csvlog in the log_destination list provides a convenient way to import log files into a database table. This option emits log lines in comma-separated-values (CSV) format, with these columns: time stamp with milliseconds, user name, database name, process ID, client host:port number, session ID, per-session line number, command tag, session start time, virtual transaction ID, regular transaction ID, error severity, SQLSTATE code, error message, error message detail, hint, internal query that led to the error (if any), character count of the error position therein, error context, user query that led to the error (if any and enabled by log_min_error_statement), character count of the error position therein, location of the error in the PostgreSQL source code (if log_error_verbosity is set to verbose), and application name. Here is a sample table definition for storing CSV-format log output:

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;

There are a few things you need to do to simplify importing CSV log files:

  1. Set log_filename and log_rotation_age to provide a consistent, predictable naming scheme for your log files. This lets you predict what the file name will be and know when an individual log file is complete and therefore ready to be imported.

  2. Set log_rotation_size to 0 to disable size-based log rotation, as it makes the log file name difficult to predict.

  3. Set log_truncate_on_rotation to on so that old log data isn't mixed with the new in the same file.

  4. The table definition above includes a primary key specification. This is useful to protect against accidentally importing the same information twice. The COPY command commits all of the data it imports at one time, so any error will cause the entire import to fail. If you import a partial log file and later import the file again when it is complete, the primary key violation will cause the import to fail. Wait until the log is complete and closed before importing. This procedure will also protect against accidentally importing a partial line that hasn't been completely written, which would also cause COPY to fail.

logging_collector (boolean)

This parameter enables the logging collector, which is a background process that captures log messages sent to stderr and redirects them into log files. This approach is often more useful than logging to syslog, since some types of messages might not appear in syslog output. (One common example is dynamic-linker failure messages; another is error messages produced by scripts such as archive_command.) This parameter can only be set at server start.

Note: It is possible to log to stderr without using the logging collector; the log messages will just go to wherever the server's stderr is directed. However, that method is only suitable for low log volumes, since it provides no convenient way to rotate log files. Also, on some platforms not using the logging collector can result in lost or garbled log output, because multiple processes writing concurrently to the same log file can overwrite each other's output.

Note: The logging collector is designed to never lose messages. This means that in case of extremely high load, server processes could be blocked while trying to send additional log messages when the collector has fallen behind. In contrast, syslog prefers to drop messages if it cannot write them, which means it may fail to log some messages in such cases but it will not block the rest of the system.

参考:

http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html

Using CSV-Format Log Output的更多相关文章

  1. [Javascript] Grouping and Nesting Console Log Output

    Organize your log output by grouping your logs into collapsable hierarchies using console.group(). ; ...

  2. CSV Format

    [CSV Format] The comma separated values format (CSV) has been used for exchanging and converting dat ...

  3. Getting console.log output with Selenium Python API bindings

    持久化存储 Getting console.log output from Chrome with Selenium Python API bindings - Stack Overflow http ...

  4. Java log code example

    Java log example Logrecord filter import java.util.logging.Filter; import java.util.logging.Level; i ...

  5. [转]Learn SQLite in 1 hour

    转载说明: 1.原文地址:http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/ 2.译文地址:http://www.oschina.net/quest ...

  6. Linux下script命令录制、回放和共享终端操作script -t 2> timing.log -a output.session # 开始录制

    Linux下script命令录制.回放和共享终端操作 [日期:2018-09-04] 来源:cnblogs.com/f-ck-need-u  作者:骏马金龙 [字体:大 中 小]   另一篇终端会话共 ...

  7. mongodb-导出数据到csv文件或json文件

    在mongodb的bin目录下, 有一个mongoexport, 可用于数据的导出 [wenbronk@localhost bin]$ ./mongoexport --help Usage: mong ...

  8. goaccess分析access.log

    接上一篇,开始学习goaccess使用~ 源码安装完成后,我的goaccess的配置文件goaccess.conf位于/usr/local/etc/ /usr/local/etc/goaccess/g ...

  9. nodejs:csv模块解析

    Nodejs最大的特点就是基于事件驱动和异步并发操作.大多数人知道nodejs是用于网络后台服务的新平台,可以很方便的提供后台服务:除了用于网络开发外,其实nodejs对于线下文件并发处理也是很方便的 ...

随机推荐

  1. spring源码学习之路---深入AOP(终)

    作者:zuoxiaolong8810(左潇龙),转载请注明出处,特别说明:本博文来自博主原博客,为保证新博客中博文的完整性,特复制到此留存,如需转载请注明新博客地址即可. 上一章和各位一起看了一下sp ...

  2. x2engine

    x2engine 各版本下载 https://bitnami.com/stack/x2crm/installer https://bitnami.com/redirect/to/36211/bitna ...

  3. 如何利用百度orc实现验证码自动识别

    在爬取网站的时候都遇到过验证码,那么我们有什么方法让程序自动的识别验证码呢?其实网上已有很多打码平台,但是这些都是需要money.但对于仅仅爬取点数据而接入打码平台实属浪费.所以百度免费orc正好可以 ...

  4. JSP内容复习

    JSP的3个编译指令,7个动作指令,9个内置对象 三个编译指令(通知servlet引擎的处理消息,只在JSP转换成Servlet时起作用) 1.编译指令 page page指令用户定义JSP页面中的全 ...

  5. Dynamics CRM 站点地图中URL的&号编码问题

    现在大多数人对站点地图的操作都通过工具来执行,但如果你使用原始的编辑XML方式,并且你的SubArea中对应的不是某个Entity而是一串URL时,要注意了你的URL中如果带有与符号即&,那就 ...

  6. 树形结构模型Django-MPTT

    什么是django-mptt? django-mptt是一个可复用的django app, 旨在让你自己的django项目模型使用MPTT更加简单.它负责将数据库表作为树型结构管理的详细信息,并提供用 ...

  7. 前后端分离djangorestframework—— 在线视频平台接入第三方加密防盗录视频

    加密视频 在以后的开发项目中,很可能有做在线视频的,而在线视频就有个问题,因为在线播放,就很有可能视频数据被抓包,如果这个在线视频平台有付费视频的话,这样就会有人做点倒卖视频的生意了,针对这个问题,目 ...

  8. Maven Gradle

    场景:随着项目越来越规范,对构建工具的要求越来越高,我们从Maven转到了Gradle. 转自:http://www.infoq.com/cn/news/2011/04/xxb-maven-6-gra ...

  9. UE4照片级渲染Demo

  10. HDU 6374(拼三角形 **)

    题意是在给定的线段长中挑选出能拼成三角形的最长的三条边,输出三角形的周长.先对所有边排序,从大到小,满足两较短边之和大于第三边就输出,若从未输出过就输出 -1 #include <iostrea ...