PostgreSQL数据库运行日志
PostgreSQL运行日志可以实现日志输出记录，默认是没有启动记录。这个日志一般是记录服务器与DB的状态，比如各种Error信息，定位慢查询SQL，数据库的启动关闭信息，发生checkpoint过于频繁等的告警信息，诸如此类。
一、常用日志参数
| 项目 | 默认值 | 设定值 | 说明 |
|---|---|---|---|
| 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参数说明：
%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语句状态
二、日志设置方式
1、启动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;
2、记录执行慢的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';
3、监控数据库中长时间的锁
ALTER SYSTEM SET log_lock_waits = on;
4、记录DDL操作
ALTER SYSTEM SET log_statement = 'ddl';
5、参数查询
select name,setting,short_desc from pg_settings where name like 'log_%';
三、CSV日志导入到数据库分析
1、创建日志表
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)
);
2、CSV日志加载
\copy postgres_log from ‘' with csv;
3、数据检索测试
// 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
四、日志保留周期
通常我们会对日志进行定期保留以保证不会撑爆磁盘容量，此时需要考虑日志保留周期。
核心参数：
| 项目 | 默认值 | 设定值 | 说明 |
|---|---|---|---|
| log_truncate_on_retation | off | off | log_rotation_age触发切换下一个日志，存在则附加，否则将覆盖 |
| log_rotation_age | 1440 | 7d | 单个日志文件生存周期，默认1天 |
| log_rotation_size | 10240 | 100MB | 单个日志文件大小 |
1、按照每分钟创建文件，保留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
2、每小时一个文件，保留一天
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
3、每天一个文件，保留一个月
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
4、每个月一个文件，保留一年
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
5、每天一个文件，保留一年
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