| 項目 | 默認(rèn)值 | 設(shè)定值 | 說明 |
|---|---|---|---|
| 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 | 日志時區(qū) |
| log_rotation_age | 1440 | 7d | 單個日志文件生存周期，默認(rèn)1天 |
| log_rotation_size | 10240 | 100MB | 單個日志文件大小 |
| log_truncate_on_retation | off | off | log_rotation_age觸發(fā)切換下一個日志，存在則附加，否則將覆蓋 |
| 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語句執(zhí)行完成消耗的時間 |
| log_line_prefix | %m[%p] | %e: %t [%p]: [%l-1] user = %u,db = %d,remote = %r app = %a | 日志輸出格式； |
| log_lock_waits | off | on | 控制當(dāng)一個會話等待時間超過deadlock_timeout而被鎖時是否產(chǎn)生一個日志信息?？膳袛嗍欠翊嬖阪i等待問題 |
| log_statement | none | ddl | 控制記錄哪些SQL語句?？蛇x值：none=>不記錄、ddl=>Create table之類的、mod=>DML語句、all=>記錄所有 |
log_line_prefix參數(shù)說明：
%a = application name 應(yīng)用名稱 %u = user name 用戶名稱 %d = database name 數(shù)據(jù)庫名稱 %r = remote host and port 遠(yuǎn)程主機(jī)與端口 %h = remote host 遠(yuǎn)程主機(jī) %p = process ID 進(jìn)程號 %t = timestamp without milliseconds 時間戳格式 %m = timestamp with millisecond 時間戳格式 %n = timestamp with milliseconds (as a Unix epoch) 時間戳格式 %i = command tag 命令標(biāo)簽 %e = SQL state SQL語句狀態(tài)
啟動pg_log配置與日志參數(shù)
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;
記錄執(zhí)行慢的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';
監(jiān)控數(shù)據(jù)庫中長時間的鎖
ALTER SYSTEM SET log_lock_waits = on;
記錄DDL操作
ALTER SYSTEM SET log_statement = 'ddl';
參數(shù)查詢
select name,setting,short_desc from pg_settings where name like 'log_%';
重啟服務(wù)Reload參數(shù)
// 重啟服務(wù) pg_ctl restart -m fast -D /pgdata/11.2/data // Reload參數(shù) select pg_reload_conf();
:# 創(chuàng)建數(shù)據(jù)表
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;
:# 數(shù)據(jù)檢索測試
// 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
pg_stat_statements統(tǒng)計了SQL的很多信息，方便我們分析SQL的性能。
:# 參數(shù)配置
alter system set shared_preload_libraries = pg_stat_statements;
:# 重啟服務(wù)Reload參數(shù)
pg_ctl restart -m fast -D /pgdata/11.2/data
:# 創(chuàng)建擴(kuò)展表
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;
※ 統(tǒng)計結(jié)果一直都在，重啟也不會清零。通過如下命令可以手工清零。
select pg_stat_statements_reset() ;
通常我們會對日志進(jìn)行定期保留以保證不會撐爆磁盤容量，此時需要考慮日志保留周期。
核心參數(shù)：
| 項目 | 默認(rèn)值 | 設(shè)定值 | 說明 |
|---|---|---|---|
| log_truncate_on_retation | off | off | log_rotation_age觸發(fā)切換下一個日志，存在則附加，否則將覆蓋 |
| log_rotation_age | 1440 | 7d | 單個日志文件生存周期，默認(rèn)1天 |
| log_rotation_size | 10240 | 100MB | 單個日志文件大小 |
按照每分鐘創(chuàng)建文件，保留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
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
標(biāo)簽：辛集 杭州 寶雞 榆林 昭通 海西 濮陽 溫州
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《PostgreSQL 打印日志信息所在的源文件和行數(shù)的實例》，本文關(guān)鍵詞 PostgreSQL,打印,日志,信息,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
