| 项目 | 默认值 | 设定值 | 说明 |
|---|---|---|---|
| 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语句状态
启动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();
:# 创建数据表
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
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() ;
通常我们会对日志进行定期保留以保证不会撑爆磁盘容量，此时需要考虑日志保留周期。
核心参数：
| 项目 | 默认值 | 设定值 | 说明 |
|---|---|---|---|
| log_truncate_on_retation | off | off | log_rotation_age触发切换下一个日志，存在则附加，否则将覆盖 |
| log_rotation_age | 1440 | 7d | 单个日志文件生存周期，默认1天 |
| log_rotation_size | 10240 | 100MB | 单个日志文件大小 |
按照每分钟创建文件，保留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
以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。如有错误或未考虑完全的地方，望不吝赐教。
