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

admin PostgreSQL 2022-02-10 16:18:56 PostgreSQL 日志信息磁盘过大"

当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的日志，（此系统每秒插入上百条数据），在关闭后，问题解决。