作为现代的RDBMS，PostgreSQL带有许多用于微调的参数。要考虑的领域之一是PostgreSQL应该如何记录其活动。在Postgres数据库管理中，日志记录经常被忽略，并且如果不忽略日志记录，通常会设置错误。发生这种情况是因为在大多数情况下，日志记录的目的尚不清楚。当然，记录日志的根本原因是众所周知的，但是有时缺乏对如何使用日志的理解。
每个组织的日志记录要求都是唯一的，因此PostgreSQL日志记录的配置方式也将有所不同。金融服务公司需要在其数据库日志中捕获的内容将不同于处理关键健康信息的公司需要记录的内容。在某些情况下，它们也可以相似。
在本文中，我将介绍一些基本的实践，以充分利用PostgreSQL日志。
为了从中获得最大的价值，我请读者考虑一下他们想如何使用PostgreSQL数据库服务器日志：
- 需要遵守特定信息的法律合规原因
- 需要特定事件详细信息的安全审核
- 记录查询及其参数的性能故障排除
- 日常运营支持，其中将监视一组数量的指标
- 话虽如此，让我们开始吧。
不要手动更改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繁忙时，此过程将推迟对日志文件的写入，以使查询线程完成。这可能会阻塞整个系统，直到写入日志事件为止。因此，在日志中记录较少的详细消息（如我们将在后面看到）并使用缩短的日志行前缀很有用。
其次-稍后我们将要看到-应该使用Log Management实用程序来收集，解析，索引和分析日志。让PostgreSQL在syslog中记录其事件将意味着在Log Management部分中创建一个额外的过滤和模式匹配层，以过滤掉所有“噪声消息”。大多数工具可以轻松地为事件分析和索引专用日志文件。
将日志目标设置为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存储桶）时，命名很重要。我建议在这种情况下使用两个参数：
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这样的应用程序需要特定的日志行前缀。
我建议在前缀中包含以下信息：
- 事件的时间（以毫秒为单位）：％t
- 远程客户端名称或IP地址：％h
- 用户名：％u
- 已访问数据库：％d
- 应用名称：％a
- 进程ID：％p
- 终止非会话进程输出：％q
- 每个会话或进程的日志行号，从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'
仅日志连接
两个参数控制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查询进行故障排除或寻找未经授权的数据访问时，也可以将其临时设置为“全部”。像pgBadger这样的应用程序也希望您将其设置为“ all”。
记录“警告”消息并向上
如果“ 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_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”参数设置为最高基准值。现在，任何运行时间超过最高基线值的查询都将成为微调的候选对象。
将错误消息的详细程度保留为默认值
“ log_error_verbosity”参数可以具有三个可能的值：
log_error_verbosity = terse | standard | verbose
此参数控制PostgreSQL将为日志文件中记录的每个事件记录的信息量。除非调试数据库应用程序，否则最好将此参数保持为“默认”。当您需要捕获文件或函数名称以及在那里产生错误的行号时，详细模式将非常有用。将此设置为“ 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报告，其中包含以下方面的详细统计信息：
- 最频繁等待的查询。
- 查询生成最大的临时文件或最大的临时文件
- 运行速度最慢的查询
- 平均查询时间
- 最常运行的查询
- 查询中最常见的错误
- 运行查询的用户和应用程序
- 检查点统计信息。
- 自动清理和自动分析统计信息。
- 锁定统计
- 错误事件（紧急，致命，错误和警告）。
- 连接和会话配置文件（按数据库，用户，应用程序）
- 会话配置文件
- 查询配置文件（查询类型，按数据库/应用程序查询）
- I / O统计
- etc.
如前所述，必须启用某些与日志相关的配置参数以捕获所有日志事件，以便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堆栈。
最后
如果配置正确，PostgreSQL服务器日志可以成为信息的金矿。诀窍是确定要记录的日志和要记录的日志，更重要的是，在需要时测试日志是否可以传递正确的信息。这将是一个反复试验的问题，但是我今天在这里所讨论的应该是一个相当不错的开始。正如我在一开始所说的，我很高兴听到您配置PostgreSQL日志以获得最佳结果的经历。
作者：Sadequl Hussain
文章来源：https://www.2ndquadrant.com/en/blog/how-to-get-the-best-out-of-postgresql-logs/
