理解postgresql.conf参数-log

我们读不懂二进制日志(WAL),但我们可以读懂适合人类阅读的log,通过log可以知道PostgreSQL究竟做了什么。

在哪里查看日志

PostgreSQL缺省的配置下,我们做一些操作:

[postgres@cos64d ~]$ /opt/PostgreSQL/9.3.4/bin/pg_ctl -D /opt/PostgreSQL/9.3.4/data/ start
server starting
[postgres@cos64d ~]$ LOG:  database system was shut down at 2014-05-29 15:40:03 CST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

执行一条命令,在当前终端(控制台1)将显示3行日志,在另外一个终端(控制台2)中连接到相同的示例,并执行一条错误的语句,在控制台2会输出如下信息:

postgres=# select 1/0;
ERROR:  division by zero

这里控制台2有一个ERROR,在控制台1会有新的输出:

[postgres@cos64d ~]$ ERROR:  division by zero
STATEMENT:  select 1/0;

这就是PostgreSQL最基本的日志输出模式。

使用pg_ctl启动实例时支持日志的重定向,可以通过加入-l参数来指定重定向的目标文件:

[postgres@cos64d ~]$ /opt/PostgreSQL/9.3.4/bin/pg_ctl -D /opt/PostgreSQL/9.3.4/data/ -l /opt/PostgreSQL/9.3.4/data/server.log start

查看-l参数指定的日志文件

[root@cos64d ~]# cat /opt/PostgreSQL/9.3.4/data/server.log 
LOG: database system was shut down at 2014-06-25 15:32:28 CST 
LOG: autovacuum launcher started 
LOG: database system is ready to accept connections

log_destination

选项:
stderr – 默认标准输出STDERR,可以重定向到文件
syslog – 记录到系统日志 (据我所知每个(类)UNIX系统都有一个系统守护日志进程)
csvlog – 保存到.csv文件,但他有一点复杂,稍后讨论
我们已经知道什么stderr。当使用syslog,PostgreSQL和所有其他消息被发送到syslog守护进程,然后可以与他们做一些配置。如果设置为syslog,以下两个参数是很重要的:
syslog_facility syslog_ident
syslog_facility指定系统日志设备,syslog_ident指定日志的标识。
在很多系统上,syslog不是非常可靠,特别是在大型日志信息的情况下;它可能在你最需要那些信息的时候截断或者丢弃它们。还有,在 Linux上 syslog 会把每个消息刷新到磁盘上,导致很低下的性能。所以并不推荐将log_destination配置为syslog。但是如果希望将PostgreSQL的日志发送到远程服务器上去,syslog是一个非常好的选择。
现在还是回来看看csv日志。
设置log_destination=stderr,开启logging_collector=on,重新启动PostgreSQL服务后PostgreSQL的日志将完全由PostgreSQL来管理。
缺省配置下,日志保存在$PGDATA/pg_log目录、命名为postgresql-%Y-%m-%d_%H%M%S.log的文件,它看起来像:
ce4f93aee062bf5883b15ed3407beff0
当然可以给日志文件改名为csv,但这不是正确的方法。将log_destination设置为csvlog,重新启动,会看到两个文件名一样但后缀名不一样的日志文件。
f7edc47f43dbd6b297fd0e5750af4d8f
通常.log文件是空的,但会记录一些与PostgreSQL核心相关的关键错误的日志,csv文件则包含了大量有用的信息。

日志轮换参数

log_rotation_age(integer)
一个独立日志文件的最大生存期。

log_rotation_size(integer)
一个独立的日志文件的最大尺寸。
达到这两个参数的最大值,将会创建一个新的日志文件。

log_truncate_on_rotation
在打开了logging_collector的时候,这个选项将导致PostgreSQL覆盖而不是附加到任何同名的现有日志文件上。不过,覆盖只是发生在基于时间滚动而创建的新文件上,而不是在服务器启动的时候或者以尺寸为基础的滚动上。这样可以很简单的做到保持一周的日志,例如设置log_filename=’ postgresql-%a.log’,log_rotation_age=’1d’,生成的日志文件名将会是postgresql-Mon.log,postgresql-Tue.log….. postgresql-Sun.log,这样,在下周周一会自动重写postgresql-Mon.log。

ERROR甚至FATAL都会记录到日志中,但是一些导致PostgreSQL无法启动的错误,一般情况会直接输出到终端,而不是记录日志。如下面的例子,将PostgreSQL的监听端口修改为80会导致PG无法启动,同时终端中会输出错误消息:
234d7945b8ee622f9c126e42707a4481

什么时候记录日志

client_min_messages(enum)
这个选项控制哪些信息发送到客户端。有效的数值是DEBUG5,DEBUG4,DEBUG3,DEBUG2,DEBUG1,LOG,NOTICE,WARNING,ERROR,FATAL和PANIC。每个级别包含所有它后面的级别,级别越靠后,发送的信息越少,缺省为NOTICE。需要注意的是这里的LOG和log_min_messages 里的级别不同。

log_min_messages(enum)
控制写到服务器日志里的信息的详细程度。有效值是DEBUG5, DEBUG4, DEBUG3,DEBUG2,DEBUG1, INFO,NOTICE,WARNING, ERROR,LOG,FATAL, and PANIC。每个级别都包含它后面的级别。越靠后的数值 发往服务器日志的信息越少,缺省是WARNING。
当设置为DEBUG5,执行“select * from pg_class order by relname limit 3”,会得到以下的日志信息:
bf0ee0646f98ab023f2c519aa4eaab3f

通常如果不是开发人员,这些信息用处并不大,所以一般不将log_min_messages设置为DEBUG,默认的设置WARNING可以满足大多数的应用场景了。

log_min_error_statement(enum)

控制是否在服务器日志里输出那些导致错误条件的 SQL 语句。所有导致一个特定级别(或者更高级别)错误的 SQL 语句都要被记录。有效的值有DEBUG5, DEBUG4,DEBUG3, DEBUG2,DEBUG1,INFO,NOTICE,WARNING,ERROR,LOG,FATAL,和PANIC。缺省是ERROR,表示所有导致错误、致命错误、恐慌的SQL语句都将被记录。
通常将log_min_messages的值 log_min_error_statement设置为相同级别,所有日志消息日志语句都应该记录下来。当然,完全可以设置为不同的值,但我只是习惯将他们设置为相同的值。基于细节,如果不设置为相同的日志级别,可以在日志中得到所有警告记录,但没有对应的操作和查询,定位和修复问题依然很困难。

记录操作和查询有三个重要的参数

log_statement
它的值由ddl,mod,all。ddl记录所有数据定义命令,比如CREATE,ALTER, 和DROP 语句。mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE,DELETE,TRUNCATE,COPY FROM。如果所包含的命令类型吻合,那么PREPARE,EXECUTE,和EXPLAIN ANALYZE语句也同样被记录。

log_duration
log_min_duration_statement

记录每个已完成语句的持续时间。默认值是off。设置为on时该选项与log_min_duration_statement的不同之处在于log_min_duration_statement强制记录查询文本。因此,如果log_duration为 on并且log_min_duration_statement大于零将记录所有持续时间,但是仅记录那些超过阈值的语句。这可以用于在高负载情况下搜集统计信息。如果log_duration注意到log_min_duration_statement已经记录时间,它不会重复记录。

使用这些设置,可以轻松地设置PostgreSQL得到我们希望得到的日志记录用于审计。另外由于performance-analysis原因,慢查询的条件因情况而异,一般的web应用程序的查询运行了100 ms可能已经很长,但数据仓库的查询运行几个小时也被视为正常。

日志的格式

log_line_prefix
对log_destination设置为STDERR的情况,日志输出的格式也很重要。缺省情况日志的输出非常简单,这并不代表PostgreSQL不能记录详细的日志。PostgreSQL的日志格式非常的灵活,通过合理配置log_line_prefix的值可以得到多种组合的日志格式。它有非常多的参数:
bad71728f6b09070606d0df31cf0448a

Application name的详细介绍,可以从这里了解:
http://www.depesz.com/2009/12/29/waiting-for-8-5-application-name-reporting/
SQLSTATE error code的详细列表可以在文档中查询:
http://www.postgresql.org/docs/current/interactive/errcodes-appendix.html
Virtual transaction ID:虚拟事务ID,它的值类似“2/4”——它不是真正有用的,除非需要调试PostgreSQL代码。
最后注意一般在log_line_prefix的一开始添加时间戳%t,或者使用%m,它的时间精度更高。%t的输出“2014-06-26 13:27:21”,%m输出“2014-06-26 13:29:19.583”。
将log_line_prefix的值设置为“%m %u %d %p %r ”,得到的日志格式如下:
2b9b691a9acaa0cb5f289c9aecbad4b0

还有一些重要的日志开关,例如log_lock_waits设置为on时,可以得到下面的日志:
3750906fe806b3295a1e48c14e0092da

这些日志对于分析问题都是很有帮助的。

csvlog

配置为csvlog可以便捷的向数据库中导入日志文件。创建如下表:

CREATE TABLE postgres_log
(
log_time TIMESTAMP(3) WITH TIME zone, --时间
user_name text, --用户名
database_name text, --数据库名
process_id INTEGER, --进程Id
connection_from text, --客户端主机
session_id text, --会话Id
session_line_num BIGINT, --每个会话的行号
command_tag text, --命令标签
session_start_time TIMESTAMP WITH TIME zone, --会话开始时间
virtual_transaction_id text, --虚拟事务Id
transaction_id BIGINT, --事务Id
error_severity text, --错误严重性
sql_state_code text,-- SQLSTATE代码
message text,--错误信息
detail text,--错误详细信息
hint text,--建议
internal_query text,--导致错误的内部查询
internal_query_pos INTEGER,--错误查询的字符位置
context text, --错误范围
query text,--导致错误的用户查询
query_pos INTEGER,--错误位置的字符统计
location text, --PostgreSQL源代码中报错的位置
application_name text, --应用程序名
PRIMARY KEY (session_id, session_line_num)
);

使用COPY将csv日志导入到表中

COPY postgres_log FROM ’/full/path/to/logfile.csv’ WITH CSV;

原文地址

UNDERSTANDING POSTGRESQL.CONF : LOG*

http://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/

参考文档

其他的参数参考文档《postgresql-9.3-US.pdf》18.8. Error Reporting and Logging。

还没有评论,快来抢沙发!

发表评论

  • 😉
  • 😐
  • 😡
  • 😈
  • 🙂
  • 😯
  • 🙁
  • 🙄
  • 😛
  • 😳
  • 😮
  • emoji-mrgree
  • 😆
  • 💡
  • 😀
  • 👿
  • 😥
  • 😎
  • ➡
  • 😕
  • ❓
  • ❗
  • 68 queries in 0.406 seconds