PostgreSQL 日志配置和sql跟踪

1 需求

跟踪sql

2 配置

2.1 方案1 修改 postgresql.conf

原生的postgresql 自带的日志系统

# 启动日志收集， 这是一个后台进程，抓取发送到stderr的日志消息，并会将他们重定向到日志文件。
logging_collector = on    

# 日志输出路径，可以是自定义绝对路径或相对于数据目录 PGDATA 的相对路径
log_directory = 'log'

# 文件名，可以带上格式字符串
# log_filename = 'postgresql-%a.log' # 按照星期命名
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 按照日期命名

# 当生成新的文件时，文件名已存在，则覆盖同名旧文件名
log_truncate_on_rotation  = on

#设置日志记录内容，log_statement：none, ddl, mod, and all 默认是 none 
# None表示不记录
# ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP语句，
# mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等
# all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句，但会对数据库性能产生较大影响，生产环境不建议配置此值
log_statement = mod

# 以上设置完成之后，可以记录到 update、drop 等，但是并不会记录 select 语句，加上 log_min_duration_statement = 0 才可以记录到select
log_min_duration_statement=0

备注，第一次需要直接修改文件，因为被注释了，不能在数据库里通过指令操作，后续可以通过下面指令来修改。

-- 1 修改
alter system set log_statement= 'none'; 
-- 2 重启
SELECT pg_reload_conf();
-- 3 验证
show log_statement; 
show log_min_duration_statement; 

2.2 方案2 扩展pg_stat_statements

参考文章 https://blog.youkuaiyun.com/inteligent7/article/details/120206580

问题在于还是要先修改postgresql.conf，但是查询日志可以通过表的方式来查询，不需要查看txt文档了。

step1 配置postgresql.conf

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all #抓取所有sql

注： 如果不做上述操作，如下操作会报错：pg_stat_statements must be loaded via shared_preload_libraries

step2 安装扩展

创建扩展对象pg_stat_statements
该对象用于存储sql执行相关执行信息，也可以理解为一张表。

create extension pg_stat_statements;
select * from pg_stat_statements;

step3 重启pg

step4 使用

select pg_stat_statements_reset() ;

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;

重置

-- 重置查询日志，相当于清空表pg_stat_statements
select pg_stat_statements_reset() ;

备注：高版本的postgresql自带pg_stat_statements扩展，只直接安装即可

3 备注

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

log_rotation_age = 1440				#minute,多长时间创建新的文件记录日志。0 表示禁扩展。
log_rotation_size = 10240			#kb,文件多大后创建新的文件记录日志。0 表示禁扩展。
log_truncate_on_rotation = on 		#可重用同名日志文件

-- 查询配置信息
SELECT name,setting,vartype,boot_val,reset_val FROM pg_settings 
where name in('logging_collector','log_destination','log_directory','log_filename');

4 效果

方案1

方案2
select * from pg_stat_statements;