PostgreSQL 打印日志信息所在的源文件和行數(shù)的實(shí)例

%a = application name 應(yīng)用名稱
%u = user name 用戶名稱
%d = database name 數(shù)據(jù)庫(kù)名稱
%r = remote host and port 遠(yuǎn)程主機(jī)與端口
%h = remote host 遠(yuǎn)程主機(jī)
%p = process ID 進(jìn)程號(hào)
%t = timestamp without milliseconds 時(shí)間戳格式
%m = timestamp with millisecond 時(shí)間戳格式
%n = timestamp with milliseconds (as a Unix epoch) 時(shí)間戳格式
%i = command tag 命令標(biāo)簽
%e = SQL state SQL語(yǔ)句狀態(tài)

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;

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;

ALTER SYSTEM SET log_statement = 'ddl';

select name,setting,short_desc from pg_settings where name like 'log_%';

// 重啟服務(wù)
pg_ctl restart -m fast -D /pgdata/11.2/data
// Reload參數(shù)
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)
);

\copy postgres_log from ‘CSV日志路徑>' with csv;

// csv日志區(qū)間范圍
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

alter system set shared_preload_libraries = pg_stat_statements;

pg_ctl restart -m fast -D /pgdata/11.2/data

CREATE EXTENSION pg_stat_statements;

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_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