PostgreSQL 15 特性 - jsonlog 日志

发表于 分类于 本文字数： 4.7k 阅读时长 ≈ 4 分钟

PostgreSQL 15 新增了 jsonlog 日志选项，即可以将日志存储为 json 格式，下面是详细的提交信息。

commit dc686681e0799b12c40f44f85fc5bfd7fed4e57f
Author: Michael Paquier <michael@paquier.xyz>
Date:   Mon Jan 17 10:16:53 2022 +0900

    Introduce log_destination=jsonlog

    "jsonlog" is a new value that can be added to log_destination to provide
    logs in the JSON format, with its output written to a file, making it
    the third type of destination of this kind, after "stderr" and
    "csvlog".  The format is convenient to feed logs to other applications.
    There is also a plugin external to core that provided this feature using
    the hook in elog.c, but this had to overwrite the output of "stderr" to
    work, so being able to do both at the same time was not possible.  The
    files generated by this log format are suffixed with ".json", and use
    the same rotation policies as the other two formats depending on the
    backend configuration.

    This takes advantage of the refactoring work done previously in ac7c807,
    bed6ed3, 8b76f89 and 2d77d83 for the backend parts, and 72b76f7 for the
    TAP tests, making the addition of any new file-based format rather
    straight-forward.

    The documentation is updated to list all the keys and the values that
    can exist in this new format.  pg_current_logfile() also required a
    refresh for the new option.

    Author: Sehrope Sarkuni, Michael Paquier
    Reviewed-by: Nathan Bossart, Justin Pryzby
    Discussion: https://postgr.es/m/CAH7T-aqswBM6JWe4pDehi1uOiufqe06DJWaU5=X7dDLyqUExHg@mail.gmail.com

使用

PostgreSQL 新增的 jsonlog 日志与 csvlog 日志类似，使用时我们需要将 logging_collector 设置为 on，当然 log_destination 需要设置为 jsonlog。

1
2
ALTER SYSTEM SET logging_collector TO on;
ALTER SYSTEM SET log_destination TO jsonlog;

重启数据库之后，我们可以看得到 log 目录下新建了以 json 结尾的的文件。

1
2
3
4
$ ls -l $PGDATA/log
total 8
-rw------- 1 px px 2480 Jan 17 22:33 postgresql-2022-01-17_222830.json
-rw------- 1 px px  173 Jan 17 22:28 postgresql-2022-01-17_222830.log

我们可以结合 jq 工具来查看日志文件内容，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$ cat $PGDATA/log/postgresql-2022-01-17_222830.json | jq
[...]
{
  "timestamp": "2022-01-17 22:28:30.529 CST",
  "pid": 1936315,
  "session_id": "61e57d0e.1d8bbb",
  "line_num": 1,
  "session_start": "2022-01-17 22:28:30 CST",
  "txid": 0,
  "error_severity": "LOG",
  "message": "database system was shut down at 2022-01-17 22:28:30 CST",
  "backend_type": "startup",
  "query_id": 0
}
{
  "timestamp": "2022-01-17 22:28:30.532 CST",
  "pid": 1936311,
  "session_id": "61e57d0e.1d8bb7",
  "line_num": 5,
  "session_start": "2022-01-17 22:28:30 CST",
  "txid": 0,
  "error_severity": "LOG",
  "message": "database system is ready to accept connections",
  "backend_type": "postmaster",
  "query_id": 0
}

我们可以结合 jq 的命令选项来快速查找日志信息，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$ jq 'select(.error_severity == "ERROR")' $PGDATA/log/postgresql-2022-01-18_121035.json
{
  "timestamp": "2022-01-18 12:17:48.640 CST",
  "user": "px",
  "dbname": "postgres",
  "pid": 1944761,
  "remote_host": "[local]",
  "session_id": "61e63dbd.1dacb9",
  "line_num": 1,
  "ps": "DROP TABLE",
  "session_start": "2022-01-18 12:10:37 CST",
  "vxid": "3/4",
  "txid": 0,
  "error_severity": "ERROR",
  "state_code": "42P01",
  "message": "table \"t\" does not exist",
  "statement": "drop table t;",
  "application_name": "psql",
  "backend_type": "client backend",
  "query_id": 0
}

上面的命令通过 jq 的 select 来查看日志级别为 ERROR 的日志信息。您还可以定制更为详细、精准的过滤条件。

说明

PostgreSQL 中 jsonlog 可以包含以下字段：

键 类型 说明
timestamp string 以毫秒为单位的时间戳
user string 用户名
dbname string 数据库名
pid number 进程 ID
remote_host string 客户端主机
remote_port number 客户端端口
session_id string 会话 ID
line_num number 每个会话的行号
ps string 当前 ps 的显示内容
session_start string 会话开始时间
vxid string 虚拟事务 ID
txid string 常规事务 ID
error_severity string 错误严重程度（日志级别）
state_code string SQLSTATE 代码
message string 错误信息
detail string 错误详细信息
hint string 错误提示信息
internal_query string 导致错误的内部查询
internal_position number 光标索引到内部查询错误位置
context string 错误上下文
statement string 客户端提供的查询字符串
cursor_position string 光标索引到查询字符串错误位置
func_name string 错误定位函数名称
file_name string 错误位置的文件名
file_line_num number 错误位置的文件行号
application_name string 客户端应用程序名称
backend_type string 后端进程类型
leader_pid number 活动并行工作者的领导者进程 ID
query_id number 查询 ID

参考

[1] https://www.postgresql.org/docs/devel/runtime-config-logging.html
[2] https://www.postgresql.org/docs/devel/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-JSONLOG
[3] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dc686681e0799b12c40f44f85fc5bfd7fed4e57f

笑林广记 - 送父上学

一人问：“公子与封君孰乐？”
答曰：“做封君虽乐，齿已衰矣。惟公子年少最乐。”
其人急趋而去，追问其故，答曰：“买了书，好送家父去上学。”