如何充分利用PostgreSQL日志

作为现代的RDBMS，PostgreSQL带有许多用于微调的参数。要考虑的领域之一是PostgreSQL应该如何记录其活动。在Postgres数据库管理中，日志记录经常被忽略，并且如果不忽略日志记录，通常会设置错误。发生这种情况是因为在大多数情况下，日志记录的目的尚不清楚。当然，我们都清楚为什么要记录日志，但有时候我们并不理解该如何使用日志。

不同的组织对日志记录使用要求都不一样，因此PostgreSQL日志记录的配置方式也将有所不同。金融服务公司需要在其数据库日志中捕获的内容将不同于处理关键健康信息的公司需要记录的内容。在某些情况下，它们也会很相似。

在本文中，我将介绍利用 PostgreSQL 日志获得最佳效果的一些基本实践。这篇文章不是一本严格的规则手册，非常欢迎读者在评论区中分享他们的想法。为了从中获得最大的价值，我请读者考虑一下如何使用PostgreSQL数据库服务器日志：

l 需要捕获特定信息以遵从法律

l 需要提供特定事件详细信息的安全审计

l 记录查询及其参数的性能故障排除

l 日常操作支持，其中有一组指标要被监视

话不多说，让我们开始吧。

不要手动更改postgresql.conf

对 postgresql.conf 文件的任何更改都应该使用类似 Puppet、 Ansible 或 Chef 这样的配置管理系统。这样可以确保更改是可追溯的，并且可以在必要时安全地回滚到以前的版本。当您修改日志记录参数时，这一点也适用。

``

DBA通常会创建postgresql.conf文件的多个副本，每个副本的参数略有不同，每个副本都有不同的用途。如果不容易出错，则手动管理不同的配置文件是一项繁琐的任务。另一方面，可以让一个组态管理系统根据传递给它的参数来重命名和使用 postgresql.conf 文件的不同版本。此参数将指示当前版本的用途。当解决了这次需要后，可以通过更改相同的输入参数来恢复原来的配置文件。

例如，如果希望记录在PostgreSQL实例上运行的所有语句，则可以使用参数值带有“ log_statement = all”的配置文件。当不需要记录所有语句时——可能是在一次故障排除的调试之后——可以恢复以前的配置文件。

对PostgreSQL使用单独的日志文件

我建议在正常操作期间启用 PostgreSQL 的本地日志记录收集器。要启用 PostgreSQL 本地日志记录，请将以下参数设置为 on：

logging_collector = on

这样做有两个原因：

首先，在繁忙的系统中，操作系统可能无法在syslog中始终记录PostgreSQL消息（假设是基于*nix系统的安装），并且经常删除消息。使用本地PostgreSQL日志记录时，会有一个单独的守护程序来负责记录事件。当PostgreSQL繁忙时，该进程将延迟日志文件的写入，让查询线程先完成。这可能会阻塞整个系统，直到日志被写入。因此，在日志中记录较少的冗长消息(我们将在后面看到)并使用较短的日志行前缀是有用的。

其次，下文也将提到的，我们应该使用日志管理工具收集，解析，索引和分析日志。让PostgreSQL在syslog中记录其事件将意味着在日志管理部分中创建一个额外的过滤和模式匹配层，以过滤掉所有“噪声消息”。 大多数工具都可以方便地对日志文件进行解析和索引。

将Log Destination设置为stderr

我们一起看下“ log_destination”参数。它可以具有四个值：

log_destination = stderr | syslog | csv | eventlog

除非有充分的理由将日志事件以逗号分隔的格式保存或Windows中的事件日志保存，否则建议将此参数设置为stderr。这是因为对于CSV文件目标，自定义的“ log_line_prefix”参数值不会产生任何影响，但是可以使前缀包含有价值的信息。

但另一方面，CSV 日志可以很容易地导入到数据库表中，然后使用标准 SQL 进行查询。一些PostgreSQL用户发现它比处理原始日志文件更方便。稍后我们将看到，当前的日志管理解决方案可以本地解析PostgreSQL日志并自动给出有意义的意见。使用CSV的话，报告和可视化必须由用户手动完成。

最终，它取决于您的选择。如果您愿意创建自己的数据接收管道以将CSV日志加载到数据库表中，清理和转换数据以及创建适合您业务需求的自定义报告，那么请确保将“ log_destination”设置为CSV。

使用有意义的日志文件名

当PostgreSQL日志文件保存在本地时，遵循一种命名格式似乎没有必要。对于非CSV格式的日志，大多数情况下，默认的文件命名格式“ postgresql-％Y-％m-％d_％H％M％S.log”，已经足够了。

当您将日志文件从多个服务器保存到中心位置(如专用日志服务器、挂载的 NFS 卷或 S3 bucket)时，命名就变得非常重要。我建议在这种情况下使用两个参数：

log_directory

log_filename

要将来自多个实例的日志文件存储在一个地方，首先，为每个实例创建一个单独的目录层次结构。可能类似于以下内容：

/<Application_Name>/<Environment_Name>/<Instance_Name>

每个 PostgreSQL 实例的“ log_directory”都可以指向它指定的文件夹。

每个实例可以使用相同的“ log_filename”参数值。默认值是类似这样的： 

postgresql_2020-08-25_2359.log

要使用更有意义的名称，请将“ log_filename”设置为如下所示：

log_filename =“ postgresql_％A-％d-％B_％H％M”

日志文件将被命名为：

postgresql_Saturday-23-August_2230

使用有意义的日志行前缀

PostgreSQL 日志行前缀可以包含除了实际消息本身之外最有价值的信息。Postgres 文档列出了日志事件前缀配置的几个转义字符。在运行时，这些转义序列将替换为各种状态值。像pgBadger这样的应用程序就没有特定的日志行前缀。

我建议在前缀中包含以下信息：

l 事件的时间(不含毫秒)：％t

l 远程客户端名称或IP地址：％h

l 用户名：％u

l 访问数据库：％d

l 应用名称：％a

l 进程ID：％p

l 终止非会话进程输出：％q

l 每个会话或进程的日志行号，从1开始：%l

要了解前缀中每个字段的含义，我们可以在字段之前添加一个小的文字字符串。因此，进程ID值前面可以加上文字“ PID =”，数据库名称前面可以加上“ db =”等。下面是一个例子：

log_line_prefix = 'time=%t, pid=%p %q db=%d, usr=%u, client=%h , app=%a, line=%l '

根据事件的来源，日志行前缀将显示不同的值。后台进程和用户进程都将在日志文件中记录其消息。对于系统进程，我指定了％q，它将抑制进程ID（％p）之后的任何文本。任何其他会话都将显示数据库名称，用户名，客户端地址，应用程序名称以及每个事件的编号行。

此外，我在日志行前缀的末尾添加了一个空格。该空格将日志事件前缀与实际事件消息分开。它不必是空格字符——可以使用双冒号(: :)、连字符(-)或其他有意义的分隔符。

还有，将“ log_hostname”参数设置为1：

log_hostname = 1

如果没有这个，则只显示客户端 IP 地址。在生产系统中，这通常是代理、负载均衡器或连接池的地址。除非您完全了解这些系统的IP地址，否则记录下来它们的主机名还是有必要的。但是，DNS查找还将增加日志守护进程的额外时间以写入文件。

应该与“ log_line_prefix”一起设置的另一个参数是“ log_timezone”。将此设置为服务器的本地时区将确保已记录的事件易于从其时间戳进行跟踪，而不是先转换为本地时间。在下面的代码段中，我们将log_timzeone设置为澳大利亚东部标准时区：

log_timezone = 'Australia/Sydney'

只配置Log Connections

两个参数控制PostgreSQL如何记录客户端连接和断开连接。默认情况下，这两个参数都是关闭的。根据组织的安全要求，您可能需要将其中之一设置为1，将另一个设置为0（除非您使用的是pgBadger之类的工具，稍后再介绍）。

log_connections = 1

log_disconnections = 0

将log_connections设置为1将记录所有授权的连接以及尝试的连接。这显然对安全审核很有好处：可以从日志中轻松识别暴力攻击。但是，启用此设置后，在繁忙的PostgreSQL环境中，如果有成千上万甚至数百个短暂的有效连接，日志文件可能会被淹没。

尽管如此，它还可以识别那些在其他情况下可能不明显的应用程序问题。来自许多不同的有效客户端地址的大量连接尝试可能表明，需要一个负载均衡器或连接池服务放在实例前面。从单个客户端地址进行大量连接尝试可能会发现有太多线程需要某种限制的应用程序。

仅记录DDL和DML操作

关于 Postgres 日志中应该记录什么有很多争论，即“ log_statement”参数的值应该是什么。它可以有三个值：

log_statement = 'off' | 'ddl' | 'mod' | 'all'

将其设置为“ all”以捕获服务器上运行的每个 SQL 语句可能很诱人，但在现实中这并不总是一个好主意。

繁忙的生产系统大多时候都在执行SELECT语句，有时候一小时就可以会执行数千条。该实例可能运行得很好，没有任何性能问题。在这种情况下，将此参数设置为“ all”将不必要地加重日志记录守护程序的负担，因为它必须将所有这些语句写入文件。

但是，您需要捕获的是任何数据损坏或数据库结构中导致某种类型问题的更改。不必要的或未经授权的数据库更改会导致应用程序问题比选择数据更多。这就是为什么我建议将此参数设置为“ mod”的原因。使用此设置，PostgreSQL将所有DDL和DML更改记录到日志文件中。

如果您的PostgreSQL实例适度繁忙（每小时数十次查询），请随时将此参数设置为“ all”。在对运行缓慢的SELECT查询进行故障排除或寻找未经授权的数据访问时，也可以将其临时设置为“all。一些应用程序，例如pgBadger，也希望您将其设置为“ all”。

记录“Warning”及其以上级别的消息

如果“ log_statement”参数决定将记录哪种类型的语句，那以下两个参数决定了消息的详细程度：

log_min_messages

log_min_error_statement

每个PostgreSQL事件都有一个关联的消息级别。消息级别可以从详细的DEBUG到简洁的PANIC。级别越低，消息越详细。“ log_min_messages”参数的默认值为“ WARNING”。我建议将其保持在此级别，除非您希望记录更多信息性的消息。

参数“ log _ min _ error _ statement”控制将记录哪些 SQL 语句抛出错误。与“ log _ min _ message”一样，严重程度等于或高于“ log _ min _ error _ statement”中指定值的任何 SQL 语句都将被记录。默认值为“ ERROR”，我建议使用默认值。

将Log Duration参数设为默认值

我们一起看下这两个参数：

log_duration

log_min_duration_statement

“ log_duration”参数为布尔值。设置为1时，将记录每个完成语句的持续时间。如果设置为0，则不会记录语句持续时间。这是默认值，除非您要对性能问题进行故障排除，否则我建议将其保持为0。计算和记录语句持续时间会使数据库引擎进行额外的工作（无论大小如何），我们假设有成百上千个查询时，节省的时间可能非常多。

最后，我们有“ log_min_duration_statement”参数。设置此参数（不使用任何单位，以毫秒为单位）时，将记录任何等于或大于参数值的语句的持续时间。将此参数值设置为0将记录所有已完成语句的持续时间。将此设置为-1将禁用语句持续时间记录。这是默认值，我建议保持默认值。

唯一要将此参数设置为0的情况是，您要为频繁运行的查询创建性能基准。但是请记住，如果设置了参数“ log_statement”，则记录的语句将不会在显示持续时间的日志消息中重复。因此，您需要将日志文件加载到数据库表中，然后从日志行前缀中加入Process ID和Session ID值，以标识相关语句及其持续时间。

无论采用哪种方式，一旦为每个经常运行的查询都建立了基准，就可以将“ log_min_duration_statement”参数设置为最高基准值。现在，任何运行时间超过最高基线值的查询都将成为微调的候选对象。

将Error Message Verbosity保留为默认值

“ log_error_verbosity”参数可以具有三个可能的值：

log_error_verbosity = terse | standard | verbose

此参数控制PostgreSQL将为日志文件中记录的每个事件记录的信息量。除非调试数据库应用程序，否则最好将此参数保持为“默认”。当您需要捕获文件或函数名称以及在那里产生错误的行号时，“verbose”模式将非常有用。将该参数设置为“ terse”将禁止记录查询，这可能用处不大。

查找适合您使用情况的日志轮换策略

我建议基于日志文件的大小或期限（而不是两者）创建日志轮换策略。两个PostgreSQL配置参数指示如何归档旧日志和创建新日志：

log_rotation_age = <number of minutes>

log_rotation_size = <number of kilobytes> 

“ log_rotration_age”的默认值为24小时，而“ log_rotation_size”的默认值为10兆字节。

通常，使用文件大小轮换策略并不总是保证归档日志文件中的最后一条日志消息完全包含在该文件中。

如果“ log _ rotation _ age”保持其默认值24小时，每个文件都可以很容易地识别和单独检查，因为每个文件将包含一天的事件。但是，这也不能保证每个文件都是最近24小时内独立的日志单位。有时，执行缓慢的查询可能需要超过24小时才能完成；当关闭旧文件并生成新文件时，可能会发生事件。在夜间批处理作业中可能会出现这种情况，导致在一个文件中记录查询的某些部分，而在另一个文件中记录其余部分。

我们的建议是找到一个适合您的用例的日志轮换周期。检查两个连续的最低活动周期之间的时间差（例如，从一个星期六到下一个星期六）。然后，您可以将“ log_rotation_age”值设置为该时差，然后在其中一个时间段内重新启动PostgreSQL服务。这样，当下一个间歇期发生时，PostgreSQL将轮换当前日志文件。但是，如果您需要在这两个时间段之间重新启动服务，则日志轮换将再次出现偏差。然后，您将不得不重复此过程。但是，就像PostgreSQL中的许多其他内容一样，反复的尝试将决定最佳价值。另外，如果您使用的是日志管理实用程序，则日志轮转的期限或大小将无关紧要，因为日志管理器代理将在添加文件时从文件中提取每个事件。

使用像pgBadger这样的工具

pgBadger是一个功能强大的PostgreSQL日志分析器，可以让我们从Postgres日志文件中得到非常有用的信息。它是用 Perl 编写的开源工具，在运行它的机器上占用的空间非常小。该工具从命令行运行，并带有大量选项。它将一个或多个日志作为输入，并且可以生成HTML报告，其中包含以下方面的详细统计信息：

l 最常见等待的查询。

l 查询生成最多的或最大的临时文件

l 运行速度最慢的查询

l 平均查询持续时间

l 最常运行的查询

l 查询中最常见的错误

l 运行查询的用户和应用程序

l 检查点统计信息。

l 自动清理和自动分析统计信息。

l 锁定统计

l 错误事件（panic，fatal，error和warning）。

l 连接和会话配置文件（按数据库，用户，应用程序）

l 会话配置文件

l 查询配置文件（查询类型，按数据库/应用程序查询）

l I O统计

等等

如前所述，必须启用某些与日志相关的配置参数以捕获所有日志事件，以便pgBadger可以有效地分析那些日志。由于这会产生带有许多事件的大型日志文件，因此pgBadger仅应用于创建基准或对性能问题进行故障排除。一旦生成了详细的日志，就可以将配置参数更改回其原始值。对于连续的日志分析，最好使用专用的日志管理应用程序。

如果您更习惯在命令提示符下使用系统视图，则可以使用pg_stat_statements。实际上，应该在任何PostgreSQL安装中启用此功能。

pg_stat_statements是PostgreSQL扩展，现在的安装程序会默认安装。要启用它，“ shared_preload_libraries”配置参数应将pg_stat_statements作为其值之一。然后可以使用“ CREATE EXTENSION”命令像其他任何扩展程序一样安装它。该扩展创建了pg_stat_statement视图，该视图提供了有价值的查询相关信息。

使用日志管理应用程序获得洞察力

市场上有许多日志管理工具，大多数组织使用一个或多个。无论使用什么工具，我都建议使用它来收集和管理PostgreSQL日志。

原因如下：

使用自动化工具解析、分析和过滤日志文件中的干扰要容易得多。有时，根据事件的持续时间以及日志旋转的时间或大小，事件可能跨越多个日志文件。拥有一个日志管理器使得将这些信息作为一个整体来显示变得更加简单。

今天的日志管理解决方案通常内置了解析 PostgreSQL 日志的能力。有些还带有仪表板，可以显示从这些日志中提取的最常见的指标。

大多数现代日志管理应用程序还提供强大的搜索、过滤、模式匹配、事件相关和 AI 支持的趋势分析功能。通过这些工具，平常我们难以发现的东西都变得更显而易见。

最后，即使原始文件被意外或恶意删除，使用日志管理器存储PostgreSQL日志也将保留事件以供后来的人使用。

尽管使用日志管理应用程序具有明显的优势，但是许多组织在其日志可以存放的位置上受到限制。是基于 SAAS 的解决方案的典型情况，其中日志通常保存在组织的地理边界之外——这可能不符合法规要求。

在这种情况下，我建议选择可能具有本地数据中心的供应商，或者使用托管在组织网络中的自管理日志管理器，例如ELK stack。

最后的话

如果配置得当，PostgreSQL 服务器日志可以成为信息的金矿。诀窍是确定要记录什么和记录多少，更重要的是，测试这些日志是否可以在需要时提供正确的信息。这将是一个反复试验的问题，但是我今天在这里所讨论的应该是一个相当不错的开始。就像我刚开始所说的那样，我非常乐意听到您为获得最佳结果而配置 PostgreSQL 日志的经验。

请点击文章底部“阅读原文”查看原文内容