Postgres日志查询和命令标记到CSV
Postgres日志查询和命令标记到CSV
提问于 2017-07-19 16:48:11
我正在尝试将执行的查询记录到csv文件中。
我的主要目标是记录命令标记和查询。为此，我在postgresql.conf中启用了日志记录，我的信任如下：
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr,csvlog' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a-%H-%M.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 10 # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 10240 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# This is only relevant when logging to eventlog (win32):
#event_source = 'PostgreSQL'
# - When to Log -
#client_min_messages = log # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#log_min_messages = info # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = info # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
log_min_duration_statement = 0 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
log_connections = on
log_disconnections = on
#log_duration = off
log_error_verbosity = verbose # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = 'ts="%t"/>' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
log_statement = 'all' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'UTC'所以，我做的主要事情是：
- 将csvlog添加到log_destination中；
- 启用logging_collector；
- 将log_min_duration_statement设置为0(以记录所有已执行的查询)；
- 启用log_connections和log_disconnections；
- 将log_error_verbosity设置为详细(以获取csv日志中的最大字段)
- 将log_statement设置为all
但是，当我运行一个简单的查询时，比如：
SELECT * FROM tests WHERE id='70';我得到：
2017-07-19 16:42:09.369 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,37,"idle",2017-07-19 16:33:37 UTC,2/20,0,LOG,00000,"statement: SELECT * FROM tests WHERE id='70';",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:914","pgAdmin III - Query Tool"
2017-07-19 16:42:09.369 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,38,"SELECT",2017-07-19 16:33:37 UTC,2/0,0,LOG,00000,"duration: 0.000 ms",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:1142","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,39,"idle",2017-07-19 16:33:37 UTC,2/21,0,LOG,00000,"statement: SELECT format_type(oid,-1) as typname FROM pg_type WHERE oid = 23",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:914","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,40,"SELECT",2017-07-19 16:33:37 UTC,2/0,0,LOG,00000,"duration: 0.000 ms",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:1142","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,41,"idle",2017-07-19 16:33:37 UTC,2/22,0,LOG,00000,"statement: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=23",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:914","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,42,"SELECT",2017-07-19 16:33:37 UTC,2/0,0,LOG,00000,"duration: 0.000 ms",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:1142","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,43,"idle",2017-07-19 16:33:37 UTC,2/23,0,LOG,00000,"statement: SELECT format_type(oid,44) as typname FROM pg_type WHERE oid = 1043",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:914","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,44,"SELECT",2017-07-19 16:33:37 UTC,2/0,0,LOG,00000,"duration: 0.000 ms",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:1142","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,45,"idle",2017-07-19 16:33:37 UTC,2/24,0,LOG,00000,"statement: SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1043",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:914","pgAdmin III - Query Tool"
2017-07-19 16:42:09.416 UTC,"postgres","postgres",11956,"::1:22927",596f89e1.2eb4,46,"SELECT",2017-07-19 16:33:37 UTC,2/0,0,LOG,00000,"duration: 0.000 ms",,,,,,,,"exec_simple_query, src\backend\tcop\postgres.c:1142","pgAdmin III - Query Tool"为什么第一行，即执行查询，说命令标签“空闲”，难道不应该说“选择”吗？是否有任何方法可以让日志行指示正确的命令标记和正确的列中的查询？为了获取该信息，我将log_error_verbosity设置为详细。有办法实现我想要的吗？
我的postgres版本是9.4。
谢谢。
回答 2
Database Administration用户
发布于 2017-07-22 08:31:45
log_line_prefix只存在于文本文件日志中，而不在csv日志中，这就是为什么在我的问题中它不存在的原因。
无论是哪种方式，我都设法“解决”了我的问题，我开始使用日志记录配置，并设法使它正常工作。我仍然不知道为什么一件如此基本的东西没有正确地实现。
简单地说，我想要做的是配置postgres，这样它就可以记录文件中的所有查询和相应的类型，如下所示
"SELECT * FROM test","SELECT"
"INSERT INTO test (id, time) VALUES ('123','1-1-2010')","INSERT"
...现在，在检查有关postgres日志记录的正式文档时，我注意到csv格式拥有我所需的所有信息(https://www.postgresql.org/docs/9.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG)。
从理论上讲，登录到csv文件将提供许多对我有用的信息，返回代码、查询、命令标记等.实际上，大部分列都不见了.一点用处都没有。我开始使用日志配置，并设法获得可以使用的输出。
通过设置下一个变量：
log_min_duration_statement = 0
log_statement = 'none'我设法用查询的实际命令标记来创建一个输出，而不是“空闲”这个词。有趣的是，如果我将设置log_statement更改为“none”，那么所有的命令标记都被设置为“空闲”.正如我已经说过的，字段丢失了，例如，我没有疑问.我不得不使用regex从message字段获取它，但是它应该出现在相应的列中，但它不是。
顺便说一句，我当时没有证件。
Database Administration用户
发布于 2018-03-03 10:51:50
查询在log_min_duration_statement和log_statement日志条目上被特别禁止，因为它已经包含在消息中。也许这并不理想，但请记住，对于文本日志记录情况，消息本身也必须是合理的。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/180263
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号