PostgreSQL数据库 LOGS 最佳实践

首页 » PostgreSQL/GaussDB » PostgreSQL数据库 LOGS 最佳实践

PostgreSQL数据库 LOGS 最佳实践

2024/12/17
PostgreSQL/GaussDB
3 views
没有评论

日志是数据库用于诊断解决问题的途径，在生产环境中开启过多可能会导致性能问题，过少可能缺失日志，配置时有必要微调默认的日志配置参数，对应多个log_xxx参数，要达到理想的平衡，需要深入了解各种 PostgreSQL logging 指令是关键。定制日志记录设置以精确满足您的监控和故障排除需求。或何时可以动态的开启。

LOG参数

postgres=# SHOW server_version;
server_version
----------------
17rc1

PostgreSQL 日志存储在哪里？

默认情况下，PostgreSQL 服务器将其日志输出到标准错误流。还提供了一个日志记录收集器进程，该进程负责捕获将日志发送到 Standard Error 并路由到日志文件。默认情况下，PostgreSQL 日志（即发送到服务器的标准错误流）不由日志记录处理 collector 进程。或相对于 PostgreSQL 数据目录，您可以通过输入以下查询来找到该目录。

SHOW log_destination;
SHOW logging_collector;
SELECT pg_current_logfile();
SHOW data_directory;

PostgreSQL 日志的类型

- 启动和关闭日志
- SQL查询或DML、DDL日志，如果开启了log_statement = ‘all’。
- 查询时长日志，如果开启了log_duration=on
- 错误日志，如pk重复、解析错误
- 连接和断开连接日志，如同oracle的监听日志
- 检查点和Write-Ahead Logs（WAL）

自定义日志文件

设置日志名与模式

PostgreSQL 允许通过选项自定义日志文件名。支持的转义在%Open Group 的 strftime 规范.

log_filename = 'postgresql-%Y-%m-%d.log'

日志中可能包含敏感内容，如密码，可以修改日志文件的创建模式权限。只能通过切换到数据库软件用户或使用 root 权限。这是一个很好的安全措施。

log_file_mode = 0600

设置日志保留周期

log_rotation_age：这指定了日志文件的最长存在时间 ,默认值为 24 小时.
log_rotation_size：此值确定日志文件的最大大小在它被轮换以支持新的日志文件之前。默认值为 10M.
log_truncate_on_rotation：此选项仅在基于时间的旋转时适用应用于日志文件。它用于覆盖相同的名称，而不是附加到文件中。

日志标记格式

log_line_prefix 此项用于日志行的开头. 请参阅stderrlog_line_prefix转义序列的完整表可供您使用。

highgo=# show log_line_prefix;
    log_line_prefix
------------------------
 %m [%p] %q%u@%r/%d,%a
(1 row)

%a：应用程序名称（使用 PostgreSQL 连接字符串）。application_name
%p：PostgreSQL 实例的进程 ID。
%u：已连接的用户。
%d：数据库名称。
%m：事件的时间（以毫秒为单位）（或者如果您更喜欢 UNIX 纪元）。%n
%q：将仅会话变量与在所有上下文。
%i：标识已执行的 SQL 查询的命令标签。
%e：相关PostgreSQL 错误代码.
%c：当前会话的 ID。

为了使日志更易于阅读和解析，您可以在每个变量前加上 key 中,结束引号前使用空格（或其他字符）将日志前缀与实际日志消息分开，如下所示：

log_line_prefix = 'time=%m pid=%p error=%e sess_id=%c %qtag=%i usr=%u db=%d app=%a '

log_timezone设置控制用于记录时间戳的时区信息.

结构化日志记录

pg一直支持csv格式的日志，在15以后可以使用json结构化日志,方便程序的读取，如同oracle是用xml格式。

log_destination = 'stderr,jsonlog'

log_statement SQL日志记录级别

none（默认）：不记录 SQL 查询。
ddl：仅记录数据定义语言（DDL）语句，例如、和修改数据库架构的语句。CREATEALTERDROP
mod：除了 DDL 语句之外，此值还会记录 Data Modification 语言（DML）语句，例如、和 .INSERTUPDATEDELETE
all：捕获对服务器进行的所有 SQL 查询，但失败的查询除外在执行阶段之前，由于解析错误

记录过多可能会在繁忙的系统生成大量的数据，如果是出于审计的目的，建议安装如pgAudit扩展.

log_duration SQL 日志记录查询持续时间

默认值：off，该参数控制每个查询的持续时间（即 is，运行所需的时间）与查询一起记录。将此参数log_duration设置为on 时，运行每个查询所需的时间将包含在日志输出中替换为查询文本。时间以毫秒为单位。主要用例是帮助提高性能调整和故障排除。识别查询运行时间最长的查询,然后采取适当的优化。建议您仅在需要时使用它，并在标准操作期间将其关闭以避免填充增加存储空间或使日志难以阅读。

在做sysbench压测时，如果开启log_duration参数，可以会产生大量的日志文件，还可能会出现sys cpu相对较高(10%+)， vmstat列出现blocker ，但是存储层iops并不高。perf 可以看到pg logger进程的占比较高。

postgres=# set log_duration=on;
SET
postgres=# select pg_sleep(5);
 pg_sleep
----------
t
日志记录
2024-12-17 17:15:54.663 CST [73976] psql postgres postgres LOG:  00000: duration: 5007.298 ms

postgres=# set log_statement='all';
SET
postgres=# select pg_sleep(5);
 pg_sleep
----------


日志记录
2024-12-17 17:15:54.663 CST  ...     select pg_sleep(5);
2024-12-17 17:15:59.663 CST [73976] psql postgres postgres LOG:  00000: duration: 5007.298 ms

记录运行缓慢的查询

除了上面的log_statement+log_duration, 另一种方法是关闭log_duration 并启用log_min_duration_statement，log_min_duration_statement它接受一个整数值（以毫秒为单位），并为执行时间超过指定的值。也就是常用的慢SQL. 用于仅记录运行缓慢的查询，以便进行调查和优化他们相应地。它还减轻了 PostgreSQL 服务器的压力，因为将记录的条目量将大大减少。

PostgreSQL 中的消息严重性级别

PostgreSQL 使用以下日志级别来指示它生成的每条日志记录的严重性：

DEBUG5、、、、：这些级别适用于记录对故障排除有用的详细信息，其中位置最详细，也最不详细。DEBUG4DEBUG3DEBUG2DEBUG1DEBUG5DEBUG1
INFO：报告用户隐式请求的信息。
NOTICE：报告可能对用户有帮助的详细信息。
WARNING：警告潜在问题，例如在 transaction 块。COMMIT
ERROR：报告中止当前命令的错误。
LOG：报告常规数据库活动。
FATAL：报告在其他会话时中止当前会话的错误保持活动状态。
PANIC：报告中止所有现有数据库会话的严重错误。

建议保持默认log_min_messages = warning，当然还可以控制日志明细，如log_error_verbose，建议保持默认，注意同一个错误可能会提示多行。

记录连接和断开连接

以下设置控制 PostgreSQL 记录客户端连接的方式，以及断开连接。默认情况下，两者都处于关闭状态：

log_connections = off
log_disconnections = off

从安全角度来看，如果您想记录哪个连接过数据库，可以启用连接日志log_connections = 1，导致日志条目增加。

创建自定义日志消息

您可以自定义日志内容，如同oracle中的dbms_system.ksdwrt(‘message’);

CREATE OR REPLACE FUNCTION custom_logs_func()
RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
    RAISE LOG 'This is an informational message';
    RAISE WARNING 'Something unexpected happened';
    RAISE EXCEPTION 'An unexpected error';
END;
$$;

CREATE OR REPLACE TRIGGER UPDATE_LAST_EDITED_TIME BEFORE UPDATE ON ALBUM
FOR EACH ROW EXECUTE FUNCTION custom_logs_func();

后面每次执行函数custom_logs_func()都会触发写日志.

使用 PgBadger 分析日志文件

PgBadger是命令行日志分析工具获取 PostgreSQL 日志文件。它旨在解析和分析 PostgreSQL 日志文件，提取有价值的洞察并生成有关数据库的活动、性能和使用模式。它可以产生全面的 HTML 报告，其中包含大量统计数据，例如：

解析和处理的日志条目总数。
最慢的查询及其执行时间。
一段时间内的连接数。
最常见的错误消息及其出现次数。
事务的提交和回滚统计信息。
临时文件使用率最高的表。
查询和会话持续时间的直方图。
热门查询中涉及的用户和应用程序..
最耗时的 prepare/bind 查询
autovacuum 进程的数量及其执行时间。
…

更多信息可以参考pgBadger 网站。样例

References
https://betterstack.com/community/guides/logging/how-to-start-logging-with-postgresql/

打赏

postgresql log

目前这篇文章还没有评论(Rss)

我要评论

下一篇：聊聊翰高数据库Highgo用户(实例和数据库)、模式兼容性(oracle、postgresql)、与PostgreSQL版本对应

ANBOB™