如何解决PostgreSQL日志信息占用磁盘过大的问题

当PostgreSQL启用日志时,若postgresql conf日志的相关参数还使用默认值的话磁盘很容易被撑爆 因此在启用了logging_collector

当PostgreSQL启用日志时,若postgresql.conf日志的相关参数还使用默认值的话磁盘很容易被撑爆.因此在启用了logging_collector参数时,需要对其它相关的参数进行调整.

系统默认参数如下

#log_destination = 'stderr' #日志格式,值为stderr, csvlog, syslog, and eventlog之一.
logging_collector = on #启用日志
#log_directory = 'log' #日志文件存储目录
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #日志文件命名方,默认为每秒一个文件(postgresql-2017-10-18_231548.log)
#log_file_mode = 0600 #日志文件权限
#log_truncate_on_rotation = off #是否截断日志文件

调整后的参数

log_destination = 'csvlog' #日志格式,值为stderr, csvlog, syslog, and eventlog之一.
logging_collector = on #启用日志
log_directory = 'log' #日志文件存储目录
log_filename = 'postgresql-%j.log' #日志文件命名方式,最多保存一年的日志.同时要打开log_truncate_on_rotation,否则日志以追加的方式显示在后面.
log_file_mode = 0600 #日志文件权限
log_truncate_on_rotation = on #是否截断日志文件

重点内容

log_destination = 'csvlog'
log_filename = 'postgresql-%j.log'
log_truncate_on_rotation = on

log_destination:建议设置为csvlog,以便将日志链接到PostgreSQL中查看.参看Error Reporting and Logging 19.8.4. Using CSV-Format Log Output

log_filename :设置日志文件名,需结合log_truncate_on_rotation = on使用.可根据自己的需要调整, 例如:

log_filename = 'postgresql-%I.log' #最多保存12小时的日志,每小时一个文件
log_filename = 'postgresql-%H.log' #最多保存24小时的日志,每小时一个文件
log_filename = 'postgresql-%w.log' #最多保存一周的日志,每天一个文件
log_filename = 'postgresql-%d.log' #最多保存一个月的日志,每天一个文件
log_filename = 'postgresql-%j.log' #最多保存一年的日志,每天一个文件

补充:PostgreSQL 日志系统 及 设置错误导致磁盘塞满案例

今天早上偶然看到QQ 群里面有一个人,在问问题,问题不重要,主要是没有人回答, 然后这个人马上就用非常让人难以接受的词汇,问候了群里面没有回答他的一干人等, 其实我有点可怜他, 问一个问题没有人回答,就如此,你是经历了什么,让你连5分钟的耐心都没有, 每个人都有自己的生活轨迹, 不回答你是很正常的,

终究 nothing is impossible , right?

正文

在众多的数据库中,POSTGRESQL 的日志的系统的丰富度和日志的详细的程度,都是可圈可点的,在网上不少同学都在问各种POSTGRESQL的问题,其实这些问题都可以在日志中找到答案,或者提交一些日志给问题的解决者,提高问题的解决速度和问题的定位的准确度。

首先我们先从日志的详细度来入手,log_min_messages 定义了日志的详细程度,其实我们在选择上可能会有一些纠结,纠结点在error warning notice 这三种,大部分人可能在选择error ,出错就报错误,warning 也有相关选择,实际上选择不同的日志的详细度也是有相关的一些考虑

1 如果你对PG本身不熟悉,测试系统可以开启notice ,这样便于你去查看一些你不理解,的东西并快速的进行学习,如果是生产系统初始阶段可以开启warning 对系统的初始时期的一些问题,可能是配置上,或者系统级别的一些问题进行更深的理解,如果是稳定运行一段时间的系统则可以将其调整到 error 方面,降低一些不必要的日志的写入,对性能和空间都有帮助。

这里建议大家可以使用warning 来作为常规的日志的详细度的使用。

2 如果有人问,在语句执行的时候,我的语句被莫名其名的kill 了我怎么查出来。下面的 log_min_error_statment 设置的选择项就与其有关了,

例如下面的错误

ERROR: current transaction is aborted, commands ignored until end of transaction block
STATEMENT: SELECT * FROM mytable WHERE id = 1 FOR UPDATE;

log_min_duration_statement 是对应慢查询的日志,当设置的值大于0 后,则超过对应设置数字秒数的SQL 语句将被记录。

这里需要考虑你的系统是OLAP OR OLTP 的情况,如果设置为 1秒,但你的系统里面的SQL 语句经常要大于1秒,则你的日志中将大量充斥这样的SQL 导致你的日志变得非常大。

说到这个MYSQL的DB会觉得PG的日志太乱了,MYSQL的日志大部分是分开的,这样有利于日志的查看和分析。这里其实也建议PG是否可以考虑将日志分开,至少分为 SLOW LOG ERROR LOG SYSTEM LOG 等等。

当然说完不足,害的说优点,让其他数据库DB们羡慕的应该就是下面的选项,你不会在任何一个数据库中,找到如此丰富选择配置

1 log_checkpoint 对当前的checkpoint的操作进行记录,通过这个信息可以有两点

1 有相关的监控系统可以读这些信息,生成图标,让这些信息成为一个趋势图来对系统进行分析,并修正系统

2 也可以手工写python程序来收集信息,直接出报告或诊断

2 log_connections 用户的登陆信息

3 log_disconnections 用户的断开的登陆的信息

4 log_error_verbosity 记录信息的详细程度,默认default

5 log_hostname 默认记录信息中带有客户端的IP地址,不带有对方的机器名

6 log_line_prefix 相当于对日志的打印的格式和信息的设置,有些监控系统对此是有要求的,请按照你安装的监控系统的要求配置此栏

7 log_lock_waits 记录语句执行中的锁等待时间

8 log_statement 对于什么语句进行记录,(这个与上面的无关,有语句审计的时候可能需要打开这个开关,进行语句的收集,不建议使用all 否则对于系统的负担太重,相当于在MYSQL中开启genernal log)

实际上很多人在操作POSTGERSQL开始的时候,是找不到日志的,因为默认PG的日志默认是不打开的,关键的参数在 logging_collector 默认是off,所以安装PG后的启动前的第一件事情就是要将这个设置变为ON ,好让PG从开始就开始记录日志。

另外日志的定期清理方面PG比其他的开源数据库要做到好多了,因为不少人都的自己写日志的rotate 和 clean up的脚本,PG 这里不需要,你只需要在 log_rotation_age中设置你要保留几天的日志,同时 log_truncate_on_rotation 设置为on 就可以了,这点是非常人性化的。或者你也可以根据日志的大小进行设置如何抛弃他。

说完这些,我们来看看实际当中会遇到什么问题,以一个案例

在搭建完PG后,系统上线前并无问题,在系统上线后第二天,有人反馈PG的日志将系统的磁盘空间大量的占用,并且7 分钟就产生一个日志文件,后续为了减少相关的日志的数量较快的增长,做了如下修改

log_rotation_size = 100MB

将日志的容量以及重置设置的更大

修改完毕后,不重新系统,直接加载后,日志的增长频率已经更改了。但日志的对磁盘空间的占用的问题还是没有解决。

打开日志,系统记录了大量如下的信息

罪魁祸首就是下面图中的log_statement_stats 这个设置,将他打开后,系统会根据每个SQL 产生一个语句的性能方面的统计信息,可以想象如果将他打开可以看到每条语句在执行中的状态, duration 等等信息,但这样就会产生大量的日志,经过统计次系统1秒产生1MB的日志,(此系统每秒插入上百条数据),在关闭后,问题解决。

所以看似一个日志的设置,如果不熟悉系统,也会造成类似的问题,并且在紧急的状态下,可能会用较长的时间来解决。实际上日志系统还有一些其他的细节,例如时区的问题,找机会可以在说说吧

以上为个人经验,希望能给大家一个参考,也希望大家多多支持好代码网。如有错误或未考虑完全的地方,望不吝赐教。