一直好奇在PG中, 当输出错误日志时, 如何能够附带错误信息所在的源代码文件名以及发生错误的代码行数.
postgres.conf中, log信息冗余级别为"default(默认)", terse: 表示更加简单的日志信息, verbose: 表示更加冗余的日志信息(即: 附带"文件名和行数)
|
1
|
#log_error_verbosity = default # terse, default, or verbose messages |
修改为下面的"verbose"即可.
|
1
|
log_error_verbosity = verbose # terse, default, or verbose messages |
修改后, 重启实例后生效, 结果如下, 可以看到日志信息附带了"文件名"和"行数"信息.
|
1
2
3
4
5
6
7
8
9
10
|
2020-03-02 09:34:41.800 CST [9019] LOG: 00000: listening on IPv6 address "::1", port 74332020-03-02 09:34:41.800 CST [9019] LOCATION: StreamServerPort, pqcomm.c:5932020-03-02 09:34:41.800 CST [9019] LOG: 00000: listening on IPv4 address "127.0.0.1", port 74332020-03-02 09:34:41.800 CST [9019] LOCATION: StreamServerPort, pqcomm.c:5932020-03-02 09:34:41.801 CST [9019] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.7433"2020-03-02 09:34:41.801 CST [9019] LOCATION: StreamServerPort, pqcomm.c:5872020-03-02 09:34:41.814 CST [9020] LOG: 00000: database system was shut down at 2020-03-02 09:34:24 CST2020-03-02 09:34:41.814 CST [9020] LOCATION: StartupXLOG, xlog.c:62912020-03-02 09:34:41.819 CST [9019] LOG: 00000: database system is ready to accept connections2020-03-02 09:34:41.819 CST [9019] LOCATION: reaper, postmaster.c:2938 |
备注: 以上verbose方法应该只能在debug模式下生效, 因为release下根本就没有文件名和行数相关的信息.
补充：PostgreSQL数据库之运行日志
PostgreSQL有三种日志：
1、pg_wal（WAL 日志，即重做日志） 内容一般不具有可读性强制开启
2、pg_log（数据库运行日志） 内容可读 默认关闭的，需要设置参数启动
3、pg_clog（事务提交日志，记录的是事务的元数据） 内容一般不具有可读性 强制开启
PostgreSQL运行日志可以实现日志输出记录，默认是没有启动记录。这个日志一般是记录服务器与DB的状态，比如各种Error信息，定位慢查询SQL，数据库的启动关闭信息，发生checkpoint过于频繁等的告警信息，诸如此类。
PostgreSQL常用日志参数
| 项目 | 默认值 | 设定值 | 说明 |
|---|---|---|---|
| logging_collector | off | on | 日志收集功能是否启动 |
| log_destination | stderr | csvlog | 日志收集存储方式 |
| log_directory | log | pg_log | 日志收集存储路径 |
| log_filename | postgresql-%Y-%m-%d.log | postgresql-%Y-%m-%d_%H%M%S.log | 日志文件命名格式 |
| log_timezone | RPC | RPC | 日志时区 |
| log_rotation_age | 1440 | 7d | 单个日志文件生存周期，默认1天 |
| log_rotation_size | 10240 | 100MB | 单个日志文件大小 |
| log_truncate_on_retation | off | off | log_rotation_age触发切换下一个日志，存在则附加，否则将覆盖 |
| log_min_messages | warning | warning | 日志输出级别 |
| log_min_duration_statement | -1 | 3000 | -1表示不可用，0将记录所有SQL语句和它们的耗时，>0只记录那些耗时超过（或等于）这个值（ms）的SQL语句。 |
| log_checkpoints | off | on | 记录Checkpoint信息 |
| log_connections | off | on | 是否记录连接日志 |
| log_disconnections | off | on | 是否记录连接断开日志 |
| log_duration | off | off | 记录每条SQL语句执行完成消耗的时间 |
| log_line_prefix | %m[%p] | %e: %t [%p]: [%l-1] user = %u,db = %d,remote = %r app = %a | 日志输出格式； |
| log_lock_waits | off | on | 控制当一个会话等待时间超过deadlock_timeout而被锁时是否产生一个日志信息。可判断是否存在锁等待问题 |
| log_statement | none | ddl | 控制记录哪些SQL语句。可选值：none=>不记录、ddl=>Create table之类的、mod=>DML语句、all=>记录所有 |
log_line_prefix参数说明：
|
1
2
3
4
5
6
7
8
9
10
11
|
%a = application name 应用名称%u = user name 用户名称%d = database name 数据库名称%r = remote host and port 远程主机与端口%h = remote host 远程主机%p = process ID 进程号%t = timestamp without milliseconds 时间戳格式%m = timestamp with millisecond 时间戳格式%n = timestamp with milliseconds (as a Unix epoch) 时间戳格式%i = command tag 命令标签%e = SQL state SQL语句状态 |
日志设置方式
启动pg_log配置与日志参数
|
1
2
3
4
5
6
7
|
ALTER SYSTEM SET log_destination = 'csvlog';ALTER SYSTEM SET logging_collector = on;ALTER SYSTEM SET log_directory = 'pg_log';ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';ALTER SYSTEM SET log_rotation_age = '7d';ALTER SYSTEM SET log_rotation_size = '100MB';ALTER SYSTEM SET log_min_messages = warning; |
记录执行慢的SQL语句
|
1
2
3
4
5
6
|
ALTER SYSTEM SET log_min_duration_statement = 3000;ALTER SYSTEM SET log_checkpoints = on;ALTER SYSTEM SET log_connections = on;ALTER SYSTEM SET log_disconnections = on;ALTER SYSTEM SET log_duration = off;ALTER SYSTEM SET log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db = %d,remote = %r app = %a'; |
监控数据库中长时间的锁
|
1
|
ALTER SYSTEM SET log_lock_waits = on; |
记录DDL操作
|
1
|
ALTER SYSTEM SET log_statement = 'ddl'; |
参数查询
|
1
|
select name,setting,short_desc from pg_settings where name like 'log_%'; |
重启服务&Reload参数
|
1
2
3
4
|
// 重启服务pg_ctl restart -m fast -D /pgdata/11.2/data// Reload参数select pg_reload_conf(); |
CSV日志导入到数据库分析
:# 创建数据表
|
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
27
|
CREATE TABLE postgres_log( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text, PRIMARY KEY (session_id, session_line_num)); |
:# CSV日志加载
|
1
|
\copy postgres_log from ‘<CSV日志路径>' with csv; |
:# 数据检索测试
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
// csv日志区间范围select min(log_time),max(log_time) from postgres_log; min | max----------------------------+--------------------------- 2019-12-11 23:18:17.334+08 | 2019-12-11 23:30:49.04+08(1 行记录)// 模糊检索字段信息select log_time,database_name,user_name,application_name,message from postgres_log where message like '%duration%'; log_time | database_name | user_name | application_name | message----------------------------+---------------+-----------+------------------+----------------------- 2019-12-11 23:18:33.559+08 | pgbench | pgbench | pgbench | duration: 36.286 ms 2019-12-11 23:18:33.573+08 | pgbench | pgbench | pgbench | duration: 13.944 ms 2019-12-11 23:18:33.581+08 | pgbench | pgbench | pgbench | duration: 7.953 ms 2019-12-11 23:18:34.561+08 | pgbench | pgbench | pgbench | duration: 976.103 ms |
SQL统计信息
pg_stat_statements统计了SQL的很多信息，方便我们分析SQL的性能。
:# 参数配置
|
1
|
alter system set shared_preload_libraries = pg_stat_statements; |
:# 重启服务&Reload参数
|
1
|
pg_ctl restart -m fast -D /pgdata/11.2/data |
:# 创建扩展表
|
1
|
CREATE EXTENSION pg_stat_statements; |
:# 查询TOP10
|
1
2
3
4
|
SELECT query, calls, total_time, (total_time/calls) as average ,rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY average DESC LIMIT 10; |
※ 统计结果一直都在，重启也不会清零。通过如下命令可以手工清零。
|
1
|
select pg_stat_statements_reset() ; |
日志保留周期
通常我们会对日志进行定期保留以保证不会撑爆磁盘容量，此时需要考虑日志保留周期。
核心参数：
| 项目 | 默认值 | 设定值 | 说明 |
|---|---|---|---|
| log_truncate_on_retation | off | off | log_rotation_age触发切换下一个日志，存在则附加，否则将覆盖 |
| log_rotation_age | 1440 | 7d | 单个日志文件生存周期，默认1天 |
| log_rotation_size | 10240 | 100MB | 单个日志文件大小 |
按照每分钟创建文件，保留1小时：
|
1
2
3
4
5
6
7
|
log_destination = 'csvlog'logging_collector = onlog_directory = 'log'log_filename = 'postgresql-%M.log'log_truncate_on_rotation = onlog_rotation_age = 1min log_rotation_size = 100MB |
每小时一个文件，保留一天：
|
1
2
3
4
5
6
7
|
log_destination = 'csvlog'logging_collector = onlog_directory = 'log'log_filename = 'postgresql-%H.log'log_truncate_on_rotation = onlog_rotation_age = 1hour log_rotation_size = 100MB |
每天一个文件，保留一个月：
|
1
2
3
4
5
6
7
|
log_destination = 'csvlog'logging_collector = onlog_directory = 'log'log_filename = 'postgresql-%d.log'log_truncate_on_rotation = onlog_rotation_age = 1day log_rotation_size = 100MB |
每个月一个文件，保留一年：
|
1
2
3
4
5
6
7
|
log_destination = 'csvlog'logging_collector = onlog_directory = 'log'log_filename = 'postgresql-%m.log'log_truncate_on_rotation = onlog_rotation_age = 1month log_rotation_size = 100MB |
每天一个文件，保留一年：
|
1
2
3
4
5
6
7
|
log_destination = 'csvlog'logging_collector = onlog_directory = 'log'log_filename = 'postgresql-%m-%d.log'log_truncate_on_rotation = onlog_rotation_age = 1day log_rotation_size = 100MB |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/liuhhaiffeng/article/details/104605519
