怎么在PostgreSQL中打印日志信息的源文件

今天就跟大家聊聊有关怎么在PostgreSQL中打印日志信息的源文件，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

postgres.conf中, log信息冗余级别为"default(默认)", terse: 表示更加简单的日志信息, verbose: 表示更加冗余的日志信息(即: 附带"文件名和行数)

#log_error_verbosity = default # terse, default, or verbose messages

修改为下面的"verbose"即可.

log_error_verbosity = verbose # terse, default, or verbose messages

修改后, 重启实例后生效, 结果如下, 可以看到日志信息附带了"文件名"和"行数"信息.

2020-03-02 09:34:41.800 CST [9019] LOG: 00000: listening on IPv6 address "::1", port 7433
2020-03-02 09:34:41.800 CST [9019] LOCATION: StreamServerPort, pqcomm.c:593
2020-03-02 09:34:41.800 CST [9019] LOG: 00000: listening on IPv4 address "127.0.0.1", port 7433
2020-03-02 09:34:41.800 CST [9019] LOCATION: StreamServerPort, pqcomm.c:593
2020-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:587
2020-03-02 09:34:41.814 CST [9020] LOG: 00000: database system was shut down at 2020-03-02 09:34:24 CST
2020-03-02 09:34:41.814 CST [9020] LOCATION: StartupXLOG, xlog.c:6291
2020-03-02 09:34:41.819 CST [9019] LOG: 00000: database system is ready to accept connections
2020-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常用日志参数

log_line_prefix参数说明：

%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配置与日志参数

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语句

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';

监控数据库中长时间的锁

ALTER SYSTEM SET log_lock_waits = on;

记录DDL操作

ALTER SYSTEM SET log_statement = 'ddl';

参数查询

select name,setting,short_desc from pg_settings where name like 'log_%';

重启服务&Reload参数

// 重启服务
pg_ctl restart -m fast -D /pgdata/11.2/data
// Reload参数
select pg_reload_conf();

CSV日志导入到数据库分析

:# 创建数据表

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日志加载

\copy postgres_log from ‘<CSV日志路径>' with csv;

:# 数据检索测试

// 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的性能。

:# 参数配置

alter system set shared_preload_libraries = pg_stat_statements;

:# 重启服务&Reload参数

pg_ctl restart -m fast -D /pgdata/11.2/data

:# 创建扩展表

CREATE EXTENSION pg_stat_statements;

:# 查询TOP10

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;

※ 统计结果一直都在，重启也不会清零。通过如下命令可以手工清零。

select pg_stat_statements_reset() ;

日志保留周期

通常我们会对日志进行定期保留以保证不会撑爆磁盘容量，此时需要考虑日志保留周期。

核心参数：

按照每分钟创建文件，保留1小时：

log_destination = 'csvlog' 
logging_collector = on 
log_directory = 'log' 
log_filename = 'postgresql-%M.log' 
log_truncate_on_rotation = on 
log_rotation_age = 1min 
log_rotation_size = 100MB

每小时一个文件，保留一天：

log_destination = 'csvlog' 
logging_collector = on 
log_directory = 'log' 
log_filename = 'postgresql-%H.log' 
log_truncate_on_rotation = on 
log_rotation_age = 1hour 
log_rotation_size = 100MB

每天一个文件，保留一个月：

log_destination = 'csvlog' 
logging_collector = on 
log_directory = 'log' 
log_filename = 'postgresql-%d.log' 
log_truncate_on_rotation = on 
log_rotation_age = 1day 
log_rotation_size = 100MB

每个月一个文件，保留一年：

log_destination = 'csvlog' 
logging_collector = on 
log_directory = 'log' 
log_filename = 'postgresql-%m.log' 
log_truncate_on_rotation = on 
log_rotation_age = 1month 
log_rotation_size = 100MB

每天一个文件，保留一年：

log_destination = 'csvlog' 
logging_collector = on 
log_directory = 'log' 
log_filename = 'postgresql-%m-%d.log' 
log_truncate_on_rotation = on 
log_rotation_age = 1day 
log_rotation_size = 100MB

看完上述内容，你们对怎么在PostgreSQL中打印日志信息的源文件有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。