地 址：北京市密云区66号
电 话：13347307821
网址：www.lbwcode.com
邮 箱：60864013@qq.com
一直好奇在(zai)PG中,打印的实 当输出错误日志时, 如何能够附带错误信息所在的源代码文件名以及发生错误的代码行数.
postgres.conf中, log信息冗余级别为(wei)"default(默认)", terse: 表示更加简单的日志信息, verbose: 表示更加冗余的日志信息(即: 附带"文件名和行数)
1#log_error_verbosity = default # terse, default, or verbose messages
修(xiu)改为下(xia)面的"verbose"即可.
1log_error_verbosity = verbose # terse, default, or verbose messages
修改后, 重启实(shi)例后生效, 结果如下, 可以看到日志信息附带了"文件名"和"行数"信息.
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方法(fa)应该只(zhi)能在debug模式下生效, 因为release下根本就没有文件名和(he)行数(shu)相(xiang)关的信息.
补充：PostgreSQL数据库之运行日志(zhi)
PostgreSQL有三种日志：
1、pg_wal（WAL 日志，日志即重做(zuo)日志） 内容一般不具有可读性强制开启
2、信息行数pg_log（数据库运行日志） 内容可读 默认关闭的(de)源文，需要(yao)设置参数启动
3、打印的(de)实pg_clog（事务提交(jiao)日志，日志记录的信息行数是事务的(de)元数据） 内容一般不具有可读性 强(qiang)制(zhi)开(kai)启
PostgreSQL运行日志可以实(shi)现(xian)日志输出记录，默认是源文没有启动记录。这个日(ri)志一般是打印的(de)实记录服务器与DB的状(zhuang)态，比如各种Error信息，日志定位慢查询SQL，信息行数数据库的源文启动关闭信息，发生checkpoint过于频繁等的打印的(de)实告警信息，诸如此类。日志
PostgreSQL常用日(ri)志参数
项目默认值设定值说明
logging_collectoroffon日志收集功能是信息行数否启动
log_destinationstderrcsvlog日志收集存储方(fang)式
log_directorylogpg_log日志收集存储路(lu)径
log_filenamepostgresql-%Y-%m-%d.logpostgresql-%Y-%m-%d_%H%M%S.log日志文件命名格式
log_timezoneRPCRPC日志时区(qu)
log_rotation_age14407d单个日志文件(jian)生存周期(qi)，默认(ren)1天
log_rotation_size10240100MB单个日志文件大小
log_truncate_on_retationoffofflog_rotation_age触发切换下一个日志(zhi)，存在则附加，否则将覆盖
log_checkpointsoffon记录Checkpoint信息
log_connectionsoffon是否记录连接日志
log_disconnectionsoffon是否记录连接断开日志
log_durationoffoff记录每(mei)条SQL语句执行完成消耗的时间
log_lock_waitsoffon控制当(dang)一个会话等待时间超过deadlock_timeout而被锁时是否产生一个日志信息。可(ke)判断是否存在锁等待问题(ti)
log_statementnoneddl控制(zhi)记录哪些SQL语句。可选值：none=>不记录、ddl=>Create table之类的、mod=>DML语句、all=>记录所有
log_line_prefix参数说明：
%a = application name 应(ying)用名称
%u = user name 用户名称
%d = database name 数(shu)据库名称
%r = remote host and port 远程(cheng)主机与端口
%h = remote host 远程主机
%p = process ID 进(jin)程(cheng)号
%t = timestamp without milliseconds 时间戳格式
%m = timestamp with millisecond 时间戳格式
%n = timestamp with milliseconds (as a Unix epoch) 时(shi)间戳格式
%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;
记录执行(xing)慢的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';
监控数据(ju)库中(zhong)长时间(jian)的(de)锁
1ALTER SYSTEM SET log_lock_waits = on;
记录DDL操作
1ALTER SYSTEM SET log_statement = 'ddl';
参数查询
1select name,setting,short_desc from pg_settings where name like 'log_%';
重(zhong)启服务&Reload参数
// 重(zhong)启服务
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日志加载
1\copy postgres_log from ‘<csv日(ri)志路径>' with csv;
:# 数据检索测试
// csv日志(zhi)区间范围
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 行(xing)记录)
// 模糊检索字段信息
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的性能。
:# 参数配置
1alter system set shared_preload_libraries = pg_stat_statements;
:# 重启服务&Reload参数
1pg_ctl restart -m fast -D /pgdata/11.2/data
:# 创建扩展表
1CREATE 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;
※ 统计结果一直都在，重(zhong)启也不会清零。通过如下(xia)命令可以手工清零。
1select pg_stat_statements_reset() ;
日志保留周期
通常我们会对(dui)日志进行定期保留以保证不会撑爆磁盘容(rong)量，此(ci)时需(xu)要考虑日(ri)志保(bao)留周期。
核心参(can)数：
项目默认值设定值说明
log_truncate_on_retationoffofflog_rotation_age触发切(qie)换下(xia)一个日志，存在(zai)则附加，否则将覆盖
log_rotation_age14407d单个日志文(wen)件(jian)生存周期(qi)，默认1天
log_rotation_size10240100MB单个日志文件大小
按照每分钟创建文(wen)件，保留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
文章来源：脚本之家
来源地址：https://www.jb51.net/article/204277.htm
