一篇将PostgreSQL 日志问题说的非常详细附带分析解决方案的文章（翻译）

原创

于 2025-09-22 06:00:30 发布 · 611 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库

❝
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

PostgreSQL 的LOG的丰富度是非常值得圈点的，相比其他的数据库系统的日志，PostgreSQL的日志可以说是种类齐全丰富。今天我们就翻译一篇国外的文章，将如果通过日志调优的事情来说说。

现代PostgreSQL实例会为数据库和查询行为的几乎每一个方面生成强大而全面的日志。虽然PostgreSQL日志是查找和调试关键错误的首选工具，但它们也是应用程序性能监控的重要手段。

今天，我们将配置PostgreSQL的日志功能——从基础开始：了解需要记录什么、如何记录所需内容，作为你努力的回报，我们还将介绍如何利用这些日志监控和提升性能。PostgreSQL官方日志文档非常出色，因此如需最新、最全面的配置说明，请务必参考这些文档。本文会在文档基础上做一些延伸解读，提供一些实用建议和配置。一如既往，实际效果可能因环境而异。

让我们进入正题，讨论以下内容：

配置日志级别、记录SQL语句、日志轮换

用于性能监控的日志记录

提取和解析日志以获取可用信息

WAL说明：本文仅涵盖服务器的消息和错误日志，不涉及事务预写日志（WAL）。尽管WAL也是一种日志，但其核心目的是记录所有数据和模式变更，用于备份、灾难恢复和复制流。

启用PostgreSQL日志记录首先，开箱即用的PostgreSQL默认仅将日志输出到终端。若要将日志发送到文件，需启用日志收集器。

logging_collector = on

你希望日志使用什么文件格式？

日志消息的格式由 log_destination参数决定，该参数可设置为以下一个或多个值：stderr（标准错误）、csvlog（CSV 格式日志）、jsonloglog（JSON 格式日志）和 syslog（系统日志）。stderr是默认值。若要使用多个日志目标，请用逗号分隔这些值：

-- setting multiple log destinations
log_destination = 'stderr,json'

如果 logging_collector = 'on'（日志收集器启用），那么 stderr（标准错误）、csvlog（CSV 格式日志）和 jsonlog（JSON 格式日志）的输出将写入 log_directory（日志目录）参数指定的目录下的文件中。其中，csv 和 json 格式的日志需要启用日志收集器才能正常工作。

日志采用多种文件格式写入的原因有很多。许多托管型和全托管系统中，日志会以不同格式提供，以满足各类工具的使用需求。例如，在 Crunchy Bridge 中，我们通过 CLI（命令行界面）查看 syslog 目标日志时会显示实时日志和日志尾部内容；内部日志则普遍使用 jsonlog 格式；而 syslog 格式常见于服务器场景，适用于将日志传送到外部日志主机的需求。

你希望日志记录的详细程度如何？服务器生成的所有日志消息都会对应以下某一个严重级别：

PANIC（恐慌）：严重错误——系统必须关闭并恢复。

FATAL（致命）：导致当前会话终止的错误。

LOG（普通）：服务器事件（如检查点）。

ERROR（错误）：导致当前操作中断但不会终止会话的错误。

WARNING（警告）：潜在问题（如已弃用功能、可能存在的数据问题）。

NOTICE（通知）：重要提示（非关键问题，例如“表不存在”）。

INFO（信息）：低优先级信息（如自动清理、配置重新加载）。

DEBUG1-5（调试1-5）：从基础调试到最详细的调试信息。

日志消息的格式大致如下：

-- background worker crash
ERROR:  background worker "logical replication launcher" crashed

--disk i/o
ERROR:  could not fsync file "pg_wal/0000000100000000000000A3": Input/output error

--out of disk space for temp files
ERROR:  could not create temporary file: No space left on device

--vacuum warning
WARNING:  relation "public.large_table" contains more than "autovacuum_vacuum_threshold" dead tuples

log_min_messages服务器设置用于确定哪些日志消息会被实际记录到配置的日志目标（或多个目标）中。所有严重级别等于或高于该配置级别的消息都会被发送。其默认值为 ERROR（错误），这通常是一个良好的设置。若需要更详细的调试信息，将级别调整为 WARNING（警告）可能也会有帮助。

log_min_messages='warning'

因此，WARNING（警告）级别会包含所有严重级别为警告、错误、普通、致命和恐慌的消息。一般来说，调试级别（DEBUG1-5）仅在开发环境或特定目的下使用。

记录SQL语句除了前文提到的日志严重级别筛选，还可以通过 log_statement参数控制需要记录的SQL查询。该参数支持以下取值：

none（无）——不记录任何内容。默认情况下，此设置不会记录SQL语句，但如果存在警告或错误，这些信息仍会根据 log_min_messages配置显示。

ddl（数据定义语言）——仅记录数据定义变更，因此会记录所有对表结构、列和索引的修改。

mod（数据修改）——记录数据修改操作，包括所有DDL（数据定义语言）以及插入、更新和删除操作。

all（全部）——记录所有SQL语句、查询和DDL（注意：生产环境中通常不建议使用此设置）。

在生产环境中，选择 ddl（数据定义语言）是一个不错的选择。

log_statement = 'ddl';

存在语法错误或在解析、规划阶段失败的语句不会被 log_statement参数覆盖。这些情况由 log_min_error_statement参数处理，建议将其设置为 ERROR（错误）或更低级别（如 WARNING、LOG等）以确保记录此类语句。

log_min_error_statement=ERROR

SQL错误会呈现如下格式，其中提示行（HINT line）会在相关时显示。如果您通过将 log_min_error_statement设置为 'error'来记录实际语句，该语句将显示在最后。

2025-05-09 14:02:37 UTC [28561] ERROR:  operator does not exist: integer == integer at character 33
2025-05-09 14:02:37 UTC [28561] HINT:  Perhaps you meant to use the standard operator "=".
2025-05-09 14:02:37 UTC [28561] STATEMENT:  SELECT * FROM users WHERE id == 42;

预处理语句与敏感数据的日志记录许多人的一个常见顾虑是确保信用卡号或个人身份信息（PII）等敏感数据不会被包含在已记录的查询数据中。log_parameter_max_length和 log_parameter_max_length_on_error这两个参数允许您分别将与预处理语句相关的查询日志和错误日志中记录的预处理语句绑定参数值的长度限制为指定的字节数。这一限制适用于通过 PREPARE/EXECUTE执行的显式命名预处理语句的绑定参数，以及使用扩展查询协议的应用程序数据库驱动所执行的“未命名”预处理语句的绑定参数。

这两个参数的默认值为 -1，表示会完整记录所有绑定参数。若将其设置为 0，则可完全禁用绑定参数的日志记录。

log_parameter_max_length = 0
log_parameter_max_length_on_error = 0

如果您仅需针对特定查询或事务进行此设置，也可以通过 SET SESSION和 SET LOCAL命令动态设置；或者，通过 ALTER USER为特定用户的所有查询设置，通过 ALTER DATABASE为特定数据库设置，甚至可以为特定数据库上特定用户的所有查询设置。

# set for an entire session
SET SESSION log_parameter_max_length = 0;
SET SESSION log_parameter_max_length_on_error = 0

# set for a transaction
BEGIN;
SET LOCAL log_parameter_max_length = 0;
SET LOCAL log_parameter_max_length_on_error = 0;
...
COMMIT;

# set for all queries run by user bob
ALTER ROLE bob SET log_parameter_max_length = 0;
ALTER ROLE bob SET log_parameter_max_length_on_error = 0;

# set for all traffic on database pii_db
ALTER DATABASE pii_db SET log_parameter_max_length = 0;
ALTER DATABASE pii_db SET flog_parameter_max_length_on_error = 0;

# set for all queries run by bob on the pii_db
ALTER ROLE bob IN DATABASE SET og_parameter_max_length = 0;
ALTER ROLE bob IN DATABASE SET log_parameter_max_length_on_error = 0;

日志条目的格式化默认情况下，PostgreSQL的日志条目看起来像这样：

2025-05-19 13:49:04.908 EDT [3108283] ERROR: column "asdfklasdf" does not exist at character 8

我们这里建议日志的格式可以调整成

log_line_prefix = '%m [%p]%q %u@%d '

格式化日志条目如果设置了日志前缀，请务必保留进程ID（%p）——这在故障排查特定进程（如查找或终止进程）时非常有用。%u会添加用户名，%d会添加数据库名，这在单实例中使用多个数据库（而非仅PostgreSQL默认数据库）时十分实用。

有关所有有效的printf风格%转义序列的完整列表，请参阅 log_line_prefix文档。

log_error_verbosity设置用于控制日志消息本身的详细程度：

terse（简洁）：通过SQL状态错误码缩短错误信息，并生成简短日志；

default（默认）：包含错误信息和提示信息；

verbose（详细）：包含额外的错误上下文（如源文件和函数名）。生产环境中不建议使用此模式，但开发环境中可能是一个实用的设置。

log_error_verbosity = 'default'

审计日志除了服务器和查询日志外，您还可以使用PGAudit扩展来审计用户行为。PGAudit并非PostgreSQL本身自带的核心扩展，但所有主流操作系统发行版的软件仓库中均提供了其安装包。

使用PGAudit扩展需要满足以下条件：将pgaudit添加到shared_preload_libraries配置中；在需要审计的每个数据库中创建该扩展；并将pgaudit.log参数设置为非none值。

-- add to preloaded libraries
shared_preload_libraries = 'pgaudit'

-- add extension
CREATE EXTENSION pgaudit

-- enable the pgaudit.log
pgaudit.log = ddl

审计日志会记录更细粒度的数据，例如执行操作的用户、操作发生的时间以及具体的变更内容。这使得能够跟踪特定的用户操作，包括插入、更新、删除和管理命令。pgAudit日志的可能取值包括：read（读取）、write（写入）、role（角色）、ddl（数据定义语言）、misc（其他）。

ALTER ROLE audited_user SET pgaudit.log = 'read, write, ddl';

审计日志看起来像这样，采用逗号分隔（CSV）格式。

2025-05-09 12:34:56.789 UTC [12345] myuser@mydb LOG:  AUDIT: SESSION,1,SELECT,pg_catalog.pg_stat_activity,SELECT * FROM pg_stat_activity;

如果你注意到常规日志和审计日志存在重叠，那你的观察是正确的。pgAudit除了PostgreSQL内置的日志功能外，还提供详细的审计日志记录（包括会话级信息、角色和变更）。如果您仅需记录DDL语句，且对pgAudit提供的额外审计功能不感兴趣，那么设置log_statement = 'ddl'可能就足够了。

对于Crunchy Bridge用户，我们会审计除应用程序用户角色外的所有内容。因此，默认情况下，您的主PostgreSQL角色和生成的用户角色都会被完整审计。

日志文件的命名与存储位置 log_filename设置用于通过 strftime时间格式转义模式指定日志文件名的格式。默认情况下，日志文件名包含“postgresql”和时间戳。

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

stderr日志文件将带有 .log后缀，csvlog文件将带有 .csv后缀，jsonlog文件将带有 .json后缀。

PostgreSQL为stderr、csvlog和jsonlog日志写入的日志文件存储在 log_directory参数指定的目录中。这可以是完整绝对路径，也可以是相对于 data_directory路径的相对路径。写入syslog的日志文件位置则由系统的syslog配置决定。

-- where on the host is the data directory
SHOW data_directory;
-- where on the host is the log directory
SHOW log_directory;
-- what do the log file names look like
SHOW log_filename;
-- exact location of the current log file
SELECT pg_current_logfile();

日志轮换现在我们已经配置了一些日志……但如果不设置轮换机制，日志会不断累积，最终填满你的磁盘。

设置每日轮换：

log_rotation_age = '1d'（日志每1天轮换一次）。

设置基于文件大小的轮换阈值（以防日志在达到1天期限前已超过10MB）：

log_rotation_size = '10MB'（当日志文件超过10MB时触发轮换）。

如果log_filename格式配置会导致日志文件名重复使用（例如postgresql-Mon.log会在每周一被重复使用），则log_truncate_on_rotation参数会使该日志文件在每次后续使用前被截断。如果未启用log_truncate_on_rotation，则现有日志会被追加新内容而非截断。

log_truncate_on_rotation = 'on'（启用日志轮换时的截断功能）。

如果使用的log_filename格式不会导致文件名自动重复（例如postgresql-%Y-%m-%d.log），建议使用Linux的logrotate等外部日志轮换工具，根据需要（可能在将旧日志归档到长期存储位置后）删除旧日志，以避免磁盘空间过度占用。

使用日志进行故障排除现在你已经配置了基本的日志记录，就可以用它来定位具体的系统问题了。通常使用PostgreSQL日志排查问题的流程大致如下：

发现问题：有人注意到系统出现异常——可能是运行缓慢、服务宕机、警报触发等。

检查指标：查看是否有CPU使用率激增、I/O负载过高等现象，确定问题发生的时间窗口（时间范围越具体越好）。

搜索日志：在对应时间窗口内搜索日志，查找错误、锁冲突或其他异常迹象。

定位问题：如果问题由特定进程引起，找到该进程的PID（进程ID），针对性处理。若是查询或锁的问题，尝试终止该进程；若是大任务拖慢整体性能，则逐步排查优化。

日志记录与性能瓶颈分析如果你已了解日志的基本配置、格式和用途，并能通过日志排查关键错误，接下来可以进一步利用日志优化查询性能。毕竟优秀的DBA不会只满足于“没有错误”，而是追求“更快、更高效”。

记录长时间运行的查询若需捕获执行时间超过一定阈值的查询信息，可通过 log_min_duration_statement参数配置。这是PostgreSQL的“慢查询日志”阈值，特别适用于调试长时间运行的查询。

当你开始优化查询性能时，记录最慢的查询是发现低效操作的有效方法。

log_min_duration_statement = '1s' -- 记录执行超过1秒的查询示例日志（一条执行超过1000秒的查询）：

LOG: duration: 2001.342 ms statement: SELECT count(*) from orders; 记录锁与锁等待通过启用 log_lock_waits，可以记录查询等待锁的情况。日志中的锁等待信息能有效反映进程间的资源竞争。启用此功能几乎不会产生性能开销，对生产数据库非常安全。Crunchy Bridge集群默认已启用此设置：

log_lock_waits = 'on' -- 启用锁等待日志记录当 log_lock_waits启用时，deadlock_timeout参数会作为锁等待的日志阈值。例如，若 deadlock_timeout = '1s'，任何等待锁超过1秒的查询都会被记录。

锁等待日志示例：

2024-05-16 14:45:12.345 UTC [45678] user@database LOG: process 45678 still waiting for ShareLock on transaction 123456 after 1000.001 ms 2024-05-16 14:45:12.345 UTC [45678] user@database DETAIL: Process holding the lock: 12345. Wait queue: 45678, 45670. 2024-05-16 14:45:12.345 UTC [45678] user@database STATEMENT: UPDATE orders SET status = 'shipped' WHERE id = 42; 从日志中可以看到：

锁等待进程的PID（45678）

持有锁的进程PID（12345）

所有等待该锁的进程PID列表（按请求顺序排列）

当前进程需要执行的查询（需要锁的操作）

记录临时文件 PostgreSQL的内存使用效率直接影响数据库的响应速度。若查询需要从磁盘（而非内存缓冲区）读取或排序数据，可能需要调大 work_mem或扩展内存容量。