postgresql - 减少 postgresql 日志的冗长程度

Question

每次调用参数化查询时，我都会在日志文件中得到太多输出。例如，当将 3 个用户插入到表中时，我得到以下日志输出:

2013-10-29 06:01:43 EDT LOG:  duration: 0.000 ms  parse <unnamed>: INSERT INTO users (login,role,password) VALUES
     ($1,$2,$3)
    ,($4,$5,$6)
    ,($7,$8,$9)
2013-10-29 06:01:43 EDT LOG:  duration: 0.000 ms  bind <unnamed>: INSERT INTO users (login,role,password) VALUES
     ($1,$2,$3)
    ,($4,$5,$6)
    ,($7,$8,$9)
2013-10-29 06:01:43 EDT DETAIL:  parameters: $1 = 'guest', $2 = 'user', $3 = '123', $4 = 'admin', $5 = 'admin', $6 = '123', $7 = 'mark', $8 = 'power user', $9 = '123'
2013-10-29 06:01:43 EDT LOG:  execute <unnamed>: INSERT INTO users (login,role,password) VALUES
     ($1,$2,$3)
    ,($4,$5,$6)
    ,($7,$8,$9)
2013-10-29 06:01:43 EDT DETAIL:  parameters: $1 = 'guest', $2 = 'user', $3 = '123', $4 = 'admin', $5 = 'admin', $6 = '123', $7 = 'mark', $8 = 'power user', $9 = '123'
2013-10-29 06:01:43 EDT LOG:  duration: 4.000 ms

请注意，整个查询出现了三次 - 解析、绑定(bind)和执行。并且完整的参数集出现了两次 - 用于绑定(bind)和执行。

请注意，这种额外的冗长仅在运行参数化查询时出现。

这是我的配置:

C:\Program Files\PostgreSQL\9.2\data>findstr log_ postgresql.conf
# "postgres -c log_connections=on".  Some parameters can be changed at run time
log_destination = 'stderr'              # Valid values are combinations of
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0600                    # creation mode for log files,
#log_truncate_on_rotation = off         # If on, an existing log file with the
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
log_min_messages = notice               # values in order of decreasing detail:
log_min_error_statement = error # values in order of decreasing detail:
log_min_duration_statement = 0  # -1 is disabled, 0 logs all statements
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default          # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%t '                 # special values:
#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
log_timezone = 'US/Eastern'
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and

C:\Program Files\PostgreSQL\9.2\data>

所以，我的问题是如何在不影响其他查询的情况下减少参数化查询日志的冗长程度？理想情况下，我希望只记录一次查询 SQL 及其参数。

Answer 1

我认为开箱即用是不可能的。您可以编写日志记录 Hook 和过滤日志条目。