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_age14407d单个⽇志⽂件⽣存周期，默认1天log_rotation_size10240100MB单个⽇志⽂件⼤⼩
log_truncate_on_retation off off log_rotation_age触发切换下⼀个⽇志，存在则附加，否则将覆盖
log_min_messages warning warning⽇志输出级别
log_min_duration_statement-13000-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参数说明：
%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-1123:18:17.334+08|2019-12-1123: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-1123:18:33.559+08| pgbench | pgbench | pgbench | duration: 36.286 ms
2019-12-1123:18:33.573+08| pgbench | pgbench | pgbench | duration: 13.944 ms
2019-12-1123:18:33.581+08| pgbench | pgbench | pgbench | duration: 7.953 ms
2019-12-1123: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 LIMIT10;
※ 统计结果⼀直都在，重启也不会清零。通过如下命令可以⼿⼯清零。零点在线
select pg_stat_statements_reset();
⽇志保留周期
通常我们会对⽇志进⾏定期保留以保证不会撑爆磁盘容量，此时需要考虑⽇志保留周期。
核⼼参数：
项⽬默认值设定值说明
log_truncate_on_retation off off log_rotation_age触发切换下⼀个⽇志，存在则附加，否则将覆盖log_rotation_age14407d单个⽇志⽂件⽣存周期，默认1天
log_rotation_size10240100MB单个⽇志⽂件⼤⼩
按照每分钟创建⽂件，保留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
