怎么在PostgreSQL中打印日志信息的源文件

今天就跟大家聊聊有關怎么在PostgreSQL中打印日志信息的源文件，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

postgres.conf中, log信息冗余級別為"default(默認)", terse: 表示更加簡單的日志信息, verbose: 表示更加冗余的日志信息(即: 附帶"文件名和行數)

#log_error_verbosity = default # terse, default, or verbose messages

修改為下面的"verbose"即可.

log_error_verbosity = verbose # terse, default, or verbose messages

修改后, 重啟實例后生效, 結果如下, 可以看到日志信息附帶了"文件名"和"行數"信息.

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方法應該只能在debug模式下生效, 因為release下根本就沒有文件名和行數相關的信息.

補充：PostgreSQL數據庫之運行日志

PostgreSQL有三種日志：

1、pg_wal（WAL 日志，即重做日志） 內容一般不具有可讀性強制開啟

2、pg_log（數據庫運行日志） 內容可讀 默認關閉的，需要設置參數啟動

3、pg_clog（事務提交日志，記錄的是事務的元數據） 內容一般不具有可讀性 強制開啟

PostgreSQL運行日志可以實現日志輸出記錄，默認是沒有啟動記錄。這個日志一般是記錄服務器與DB的狀態，比如各種Error信息，定位慢查詢SQL，數據庫的啟動關閉信息，發生checkpoint過于頻繁等的告警信息，諸如此類。

PostgreSQL常用日志參數

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();

CSV日志導入到數據庫分析

:# 創建數據表

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

SQL統計信息

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() ;

日志保留周期

通常我們會對日志進行定期保留以保證不會撐爆磁盤容量，此時需要考慮日志保留周期。

核心參數：

按照每分鐘創建文件，保留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

看完上述內容，你們對怎么在PostgreSQL中打印日志信息的源文件有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。