一直好奇在PG中, 當(dāng)輸出錯誤日志時, 如何能夠附帶錯誤信息所在的源代碼文件名以及發(fā)生錯誤的代碼行數(shù).
postgres.conf中, log信息冗余級別為"default(默認(rèn))", terse: 表示更加簡單的日志信息, verbose: 表示更加冗余的日志信息(即: 附帶"文件名和行數(shù))
1#log_error_verbosity = default # terse, default, or verbose messages
修改為下面的"verbose"即可.
1log_error_verbosity = verbose # terse, default, or verbose messages
修改后, 重啟實例后生效, 結(jié)果如下, 可以看到日志信息附帶了"文件名"和"行數(shù)"信息.
2020-03-02 09:34:41.800 CST [9019] LOG: 00000: listening on IPv6 address "::1", port 7433
2020-03-02 09:34:41.800 CST [9019] LOCATION: StreamServerPort, pqcomm.c:593
2020-03-02 09:34:41.800 CST [9019] LOG: 00000: listening on IPv4 address "127.0.0.1", port 7433
2020-03-02 09:34:41.800 CST [9019] LOCATION: StreamServerPort, pqcomm.c:593
2020-03-02 09:34:41.801 CST [9019] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.7433"
2020-03-02 09:34:41.801 CST [9019] LOCATION: StreamServerPort, pqcomm.c:587
2020-03-02 09:34:41.814 CST [9020] LOG: 00000: database system was shut down at 2020-03-02 09:34:24 CST
2020-03-02 09:34:41.814 CST [9020] LOCATION: StartupXLOG, xlog.c:6291
2020-03-02 09:34:41.819 CST [9019] LOG: 00000: database system is ready to accept connections
2020-03-02 09:34:41.819 CST [9019] LOCATION: reaper, postmaster.c:2938
備注: 以上verbose方法應(yīng)該只能在debug模式下生效, 因為release下根本就沒有文件名和行數(shù)相關(guān)的信息.
補充：PostgreSQL數(shù)據(jù)庫之運行日志
PostgreSQL有三種日志：
1、pg_wal（WAL 日志，即重做日志） 內(nèi)容一般不具有可讀性強制開啟
2、pg_log（數(shù)據(jù)庫運行日志） 內(nèi)容可讀 默認(rèn)關(guān)閉的，需要設(shè)置參數(shù)啟動
3、pg_clog（事務(wù)提交日志，記錄的是事務(wù)的元數(shù)據(jù)） 內(nèi)容一般不具有可讀性 強制開啟
PostgreSQL運行日志可以實現(xiàn)日志輸出記錄，默認(rèn)是沒有啟動記錄。這個日志一般是記錄服務(wù)器與DB的狀態(tài)，比如各種Error信息，定位慢查詢SQL，數(shù)據(jù)庫的啟動關(guān)閉信息，發(fā)生checkpoint過于頻繁等的告警信息，諸如此類。
PostgreSQL常用日志參數(shù)
項目默認(rèn)值設(shè)定值說明
logging_collectoroffon日志收集功能是否啟動
log_destinationstderrcsvlog日志收集存儲方式
log_directorylogpg_log日志收集存儲路徑
log_filenamepostgresql-%Y-%m-%d.logpostgresql-%Y-%m-%d_%H%M%S.log日志文件命名格式
log_timezoneRPCRPC日志時區(qū)
log_rotation_age14407d單個日志文件生存周期，默認(rèn)1天
log_rotation_size10240100MB單個日志文件大小
log_truncate_on_retationoffofflog_rotation_age觸發(fā)切換下一個日志，存在則附加，否則將覆蓋
log_min_messageswarningwarning日志輸出級別
log_min_duration_statement-13000-1表示不可用，0將記錄所有SQL語句和它們的耗時，>0只記錄那些耗時超過（或等于）這個值（ms）的SQL語句。
log_checkpointsoffon記錄Checkpoint信息
log_connectionsoffon是否記錄連接日志
log_disconnectionsoffon是否記錄連接斷開日志
log_durationoffoff記錄每條SQL語句執(zhí)行完成消耗的時間
log_line_prefix%m[%p]%e: %t [%p]: [%l-1] user = %u,db = %d,remote = %r app = %a日志輸出格式；
log_lock_waitsoffon控制當(dāng)一個會話等待時間超過deadlock_timeout而被鎖時是否產(chǎn)生一個日志信息?？膳袛嗍欠翊嬖阪i等待問題
log_statementnoneddl控制記錄哪些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 遠程主機與端口
%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 命令標(biāo)簽
%e = SQL state SQL語句狀態(tài)
日志設(shè)置方式
啟動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ù)庫中長時間的鎖
1ALTER SYSTEM SET log_lock_waits = on;
記錄DDL操作
1ALTER SYSTEM SET log_statement = 'ddl';
參數(shù)查詢
1select 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();
CSV日志導(dǎo)入到數(shù)據(jù)庫分析
:# 創(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日志加載
1\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
SQL統(tǒng)計信息
pg_stat_statements統(tǒng)計了SQL的很多信息，方便我們分析SQL的性能。
:# 參數(shù)配置
1alter system set shared_preload_libraries = pg_stat_statements;
:# 重啟服務(wù)&Reload參數(shù)
1pg_ctl restart -m fast -D /pgdata/11.2/data
:# 創(chuàng)建擴展表
1CREATE 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é)果一直都在，重啟也不會清零。通過如下命令可以手工清零。
1select pg_stat_statements_reset() ;
日志保留周期
通常我們會對日志進行定期保留以保證不會撐爆磁盤容量，此時需要考慮日志保留周期。
核心參數(shù)：
項目默認(rèn)值設(shè)定值說明
log_truncate_on_retationoffofflog_rotation_age觸發(fā)切換下一個日志，存在則附加，否則將覆蓋
log_rotation_age14407d單個日志文件生存周期，默認(rèn)1天
log_rotation_size10240100MB單個日志文件大小
按照每分鐘創(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
文章來源：腳本之家
來源地址：https://www.jb51.net/article/204277.htm
申請創(chuàng)業(yè)報道，分享創(chuàng)業(yè)好點子。點擊此處，共同探討創(chuàng)業(yè)新機遇！
