Postgresql 中文操作指南

25.3. Log File Maintenance #

最好将数据库服务器的日志输出保存在某处，而不是通过 /dev/null 直接丢弃。在诊断问题时，日志输出是无价的。

It is a good idea to save the database server’s log output somewhere, rather than just discarding it via /dev/null. The log output is invaluable when diagnosing problems.

Note

服务器日志可能包含敏感信息，无论它如何存储或存储在哪里，或者路由到的目的地是什么，都需要对其进行保护。例如，某些 DDL 语句可能包含明文密码或其他身份验证详细信息。ERROR 级别的日志语句可能显示应用程序的 SQL 源代码，还可能包含一些数据行的部分内容。记录数据、事件和相关信息是此功能的预期功能，因此这不属于泄漏或错误。请确保只有经过适当授权的人员才能看到服务器日志。

The server log can contain sensitive information and needs to be protected, no matter how or where it is stored, or the destination to which it is routed. For example, some DDL statements might contain plaintext passwords or other authentication details. Logged statements at the ERROR level might show the SQL source code for applications and might also contain some parts of data rows. Recording data, events and related information is the intended function of this facility, so this is not a leakage or a bug. Please ensure the server logs are visible only to appropriately authorized people.

日志输出往往很冗长（尤其是在更高的调试级别），因此您不会想要无限期地保存它。您需要 rotate 日志文件，以便在一段时间后启动新的日志文件并删除旧日志文件。

Log output tends to be voluminous (especially at higher debug levels) so you won’t want to save it indefinitely. You need to rotate the log files so that new log files are started and old ones removed after a reasonable period of time.

如果您只是将 postgres 的 stderr 指向文件，您将拥有日志输出，但截断日志文件的唯一方法是停止并重新启动服务器。如果您在开发环境中使用 PostgreSQL，这可能可以接受，但很少有生产服务器会发现这种行为可以接受。

If you simply direct the stderr of postgres into a file, you will have log output, but the only way to truncate the log file is to stop and restart the server. This might be acceptable if you are using PostgreSQL in a development environment, but few production servers would find this behavior acceptable.

一个更好的方法是将服务器的 stderr 输出发送到某种日志轮换程序。服务器内置日志轮换功能，您可以通过在 postgresql.conf 中将配置参数 logging_collector 设置为 true 来使用该功能。此程序的控制参数如 Section 20.8.1 中所述。您还可以使用这种方法以机器可读 CSV（逗号分隔值）格式捕获日志数据。

A better approach is to send the server’s stderr output to some type of log rotation program. There is a built-in log rotation facility, which you can use by setting the configuration parameter logging_collector to true in postgresql.conf. The control parameters for this program are described in Section 20.8.1. You can also use this approach to capture the log data in machine readable CSV (comma-separated values) format.

或者，如果您已经与其他服务器软件一起使用外部日志轮换程序，您可能更喜欢使用它。例如，Apache 发行版中包含的 rotatelogs 工具可以与 PostgreSQL 一起使用。执行此操作的一种方法是将服务器的 stderr 输出管道输入到所需的程序。如果您使用 pg_ctl 启动服务器，则 stderr 已重定向到 stdout，因此您只需要一个管道命令，例如：

Alternatively, you might prefer to use an external log rotation program if you have one that you are already using with other server software. For example, the rotatelogs tool included in the Apache distribution can be used with PostgreSQL. One way to do this is to pipe the server’s stderr output to the desired program. If you start the server with pg_ctl, then stderr is already redirected to stdout, so you just need a pipe command, for example:

pg_ctl start | rotatelogs /var/log/pgsql_log 86400

您可以通过将 logrotate 设置为收集 PostgreSQL 内置日志收集器产生的日志文件来组合这些方法。在此情况下，日志收集器定义日志文件的文件名和位置，而 logrotate 定期归档这些文件。在启动日志轮换时，logrotate 必须确保应用程序将进一步的输出发送到新文件。这通常是通过一个 postrotate_脚本来完成的，该脚本向应用程序发送一个 _SIGHUP 信号，然后应用程序重新打开日志文件。在 PostgreSQL 中，您可以运行带有 logrotate 选项的 pg_ctl。当服务器收到此命令时，会根据日志配置（请参见 Section 20.8.1），切换到新日志文件或重新打开现有日志文件。

You can combine these approaches by setting up logrotate to collect log files produced by PostgreSQL built-in logging collector. In this case, the logging collector defines the names and location of the log files, while logrotate periodically archives these files. When initiating log rotation, logrotate must ensure that the application sends further output to the new file. This is commonly done with a postrotate script that sends a SIGHUP signal to the application, which then reopens the log file. In PostgreSQL, you can run pg_ctl with the logrotate option instead. When the server receives this command, the server either switches to a new log file or reopens the existing file, depending on the logging configuration (see Section 20.8.1).

Note

使用静态日志文件名时，如果达到最大打开文件限制或出现文件表溢出，服务器可能无法重新打开日志文件。在这种情况下，日志消息会发送到旧日志文件，直到成功进行日志轮换。如果配置日志轮换来压缩日志文件并将其删除，则服务器可能会丢失此时间段内记录的消息。要避免此问题，您可以将日志收集器配置为动态分配日志文件名，并使用 prerotate 脚本忽略打开的日志文件。

When using static log file names, the server might fail to reopen the log file if the max open file limit is reached or a file table overflow occurs. In this case, log messages are sent to the old log file until a successful log rotation. If logrotate is configured to compress the log file and delete it, the server may lose the messages logged in this time frame. To avoid this issue, you can configure the logging collector to dynamically assign log file names and use a prerotate script to ignore open log files.

管理日志输出的另一个生产级方法是将其发送到 syslog 并让 syslog 处理文件轮换。要执行此操作，请在 postgresql.conf 中将配置参数 log_destination 设置为 syslog（仅记录到 syslog）。然后您可以随时向 syslog 守护程序发送 SIGHUP 信号以强制它开始写入新的日志文件。如果您想要自动执行日志轮换，可以将 logrotate 程序配置为使用 syslog 的日志文件。

Another production-grade approach to managing log output is to send it to syslog and let syslog deal with file rotation. To do this, set the configuration parameter log_destination to syslog (to log to syslog only) in postgresql.conf. Then you can send a SIGHUP signal to the syslog daemon whenever you want to force it to start writing a new log file. If you want to automate log rotation, the logrotate program can be configured to work with log files from syslog.

然而，在许多系统上，syslog 的可靠性不是很好，尤其是在日志消息很大的情况下；它可能在你最需要它们的时候截断或丢弃消息。此外，在 Linux 上，syslog 将刷新每个消息到磁盘，从而产生较差的性能。（您可以在 syslog 配置文件的文件名开头使用“-”来禁用同步。）

On many systems, however, syslog is not very reliable, particularly with large log messages; it might truncate or drop messages just when you need them the most. Also, on Linux, syslog will flush each message to disk, yielding poor performance. (You can use a “-” at the start of the file name in the syslog configuration file to disable syncing.)

请注意，上面描述的所有解决方案都会在可配置的时间间隔内启动新日志文件，但它们不会处理不再有用的旧日志文件的删除。您可能需要设置一个批处理作业来定期删除旧日志文件。另一种可能性是配置轮换程序，以便旧日志文件被循环覆盖。

Note that all the solutions described above take care of starting new log files at configurable intervals, but they do not handle deletion of old, no-longer-useful log files. You will probably want to set up a batch job to periodically delete old log files. Another possibility is to configure the rotation program so that old log files are overwritten cyclically.

pgBadger 是一个执行复杂日志文件分析的外部项目。 check_postgres 提供 Nagios 警报，当日志文件中出现重要消息时，以及检测许多其他异常情况时。

pgBadger is an external project that does sophisticated log file analysis. check_postgres provides Nagios alerts when important messages appear in the log files, as well as detection of many other extraordinary conditions.