postgresql与pgBadger之间存在问题。
pgBadger生成的HTML报告为空(0个查询,0个事件,没有任何关于postgres工作的内容)。但是postgres日志不是空的。有事件等。
我在postgresql.conf中尝试了许多参数组合(,例如log_destination=syslog,logging_collector=off,log_line_prefix-或多或少的值),但仍然相同。
目前,该参数设置在postgresql.conf:中
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
client_min_messages = notice
log_min_messages = notice
log_min_error_statement = error
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_duration = off
log_line_prefix = '%t [%r] [%p]: [%l-1] user=%u,db=%d,e=%e '
log_statement = 'all'
lc_messages = 'en_US.UTF-8'
这就是我尝试使用pgBadger的方式,以及我得到的结果:
#pgbadger -p -l /var/lib/postgresql/9.3/main/pg_log/postgresql-2014-10-15_125036.log -f stderr -o /var/lib/postgresql/9.3/main/pg_log/report.html
或
#pgbadger -p -l /var/log/postgresql/postgresql-9.3-main.log -f stderr -o /var/log/postgresql/log_report.html
结果总是一样的:
/usr/src/pgbadger-6.2# pgbadger -p -l /var/lib/postgresql/9.3/main/pg_log/postgresql-2014-10-15_125036.log -f stderr -o /var/lib/postgresql/9.3/main/pg_log/report.html
[========================>] Parsed 12999 bytes of 12999 (100.00%), queries: 0, events: 0
LOG: Ok, generating html report...
我的合作伙伴:Debian 7,postgresql 9.3,pgbadger-6.2
有人对此有类似的问题吗?
确保postgresql.conf
中的log_line_prefix
为:
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u '
实际上我也遇到了同样的问题。你能按照这个文档来解决你的问题吗。感谢
http://www.antelink.com/blog/using-pgbadger-monitor-your-postgresql-activity.html
我认为您需要将log_statement更改为"none"。那么它可能会解决你的问题。
您应该使用-v选项进行详细模式。
pgbager-f stderr-v/Data directory/postgresql-Sat.log-o/Data directory/postgresql-reports.html
请根据您的PostgreSQL集群数据更改数据目录
您必须将在postgresql.conf
中找到的log_line_prefix
作为--prefix
标志传递给pgbager。对于AWS RDS,转到RDS的paramter group
、log_line_prefix
参数,以检索相同的内容。
建议在详细模式下运行您的pgbager,以了解发生了什么故障,如
pgbadger -v --prefix='<<Your log_line_prefix>>:' <<logFileName>>
例如:
-
假设默认格式-NON VERBOSE
$ pgbadger pgsql_log_Jul2018.txt [========================>] Parsed 131114431 bytes of 413111431 (100.00%), queries: 0, events: 0 LOG: Ok, generating html report...
正如您所看到的,即使您的日志文件中有数据,您也看不出为什么查询和事件为0。
-
假设默认格式-VERBOSE模式
$ pgbadger -v pgsql_log_Jul2018.txt DEBUG: Autodetected log format 'stderr' from pgsql_log_Jul2018.txt DEBUG: Output 'html' reports will be written to out.html DEBUG: Autodetected log format 'stderr' from pgsql_log_Jul2018.txt DEBUG: Autodetected log format 'stderr' from pgsql_log_Jul2018.txt DEBUG: pgBadger will use log format stderr to parse pgsql_log_Jul2018.txt. DEBUG: Starting to parse log file: pgsql_log_Jul2018.txt DEBUG: Starting reading file pgsql_log_Jul2018.txt... DEBUG: Start parsing at offset 0 of file pgsql_log_Jul2018.txt to 413111431 DEBUG: Unknown line format: 2018-07-15 16:00:07 UTC:192-198-10-132.local(23421):postgres@def_db:[12642]:LOG: duration: 2772.244 ms execute <unnamed>: SELECT * FROM device_table WHERE date >= '2018-06-15' DEBUG: Unknown line format: 2018-07-15 16:00:07 UTC:192-198-10-132.local(23421):postgres@def_db:[45279]:DETAIL: parameters: $1 = '251' ... DEBUG: Unknown line format: 2018-07-18 16:59:56 UTC:192-198-10-132.local(23421):postgres@def_db:[54342]:DETAIL: parameters: $1 = '21' [========================>] Parsed 131114431 bytes of 413111431 (100.00%), queries: 0, events: 0 DEBUG: the log statistics gathering took:16 wallclock secs ( 3.99 usr + 2.03 sys = 6.02 CPU) LOG: Ok, generating html report... DEBUG: building reports took: 0 wallclock secs ( 0.01 usr + 0.02 sys = 0.03 CPU) DEBUG: the total execution time took: 16 wallclock secs ( 4.00 usr + 2.05 sys = 6.05 CPU)
-
提供格式-VERBOSE模式
$ pgbadger -v --prefix='%t:%r:%u@%d:[%p]:' pgsql_log_Jul2018.txt DEBUG: Autodetected log format 'stderr' from pgsql_log_Jul2018.txt DEBUG: Output 'html' reports will be written to out.html DEBUG: Autodetected log format 'stderr' from pgsql_log_Jul2018.txt DEBUG: Autodetected log format 'stderr' from pgsql_log_Jul2018.txt DEBUG: pgBadger will use log format stderr to parse pgsql_log_Jul2018.txt. DEBUG: Starting to parse log file: pgsql_log_Jul2018.txt DEBUG: Starting reading file pgsql_log_Jul2018.txt... DEBUG: Start parsing at offset 0 of file pgsql_log_Jul2018.txt to 413111431 [========================>] Parsed 131114431 bytes of 413111431 (100.00%), queries: 55645, events: 34 DEBUG: the log statistics gathering took:51 wallclock secs (48.81 usr + 0.84 sys = 49.65 CPU) LOG: Ok, generating html report... DEBUG: building reports took: 14 wallclock secs (13.22 usr + 0.18 sys = 13.40 CPU) DEBUG: the total execution time took: 65 wallclock secs (62.03 usr + 1.02 sys = 63.05 CPU)
在您的情况下,由于您的postgresql.conf
具有
...
log_duration = off
log_line_prefix = '%t [%r] [%p]: [%l-1] user=%u,db=%d,e=%e '
log_statement = 'all'
...
尝试使用--prefix='%t [%r] [%p]: [%l-1] user=%u,db=%d,e=%e '
此外,请查看pgbager团队的这条评论——人们通常会混淆format
和prefix
,因此引用手册页是一项任务,而且很有帮助。