怎么找淘宝客网站,网站自助制作,网店运营实务,网站头部怎样做有气势文章目录 1.postgresql与日志有关的参数2.开启日志3.指定日志目录4.設置文件名format5.設置日志文件產出模式6.設置日志记录格式7.日誌輪換7.1非截斷式輪換7.2 截斷式輪換 8.日誌記錄內容8.1 log_statement8.2 log_min_duration_statement 9 輸出範本 1.postgresql与日志有关的… 文章目录 1.postgresql与日志有关的参数2.开启日志3.指定日志目录4.設置文件名format5.設置日志文件產出模式6.設置日志记录格式7.日誌輪換7.1非截斷式輪換7.2 截斷式輪換 8.日誌記錄內容8.1 log_statement8.2 log_min_duration_statement 9 輸出範本 1.postgresql与日志有关的参数
postgres#select name,setting,unit,context from pg_settings where name like log_%;name | setting | unit | context
-----------------------------------------------------------------------------------------------------log_autovacuum_min_duration | 60000 | ms | sighuplog_checkpoints | on | | sighuplog_connections | off | | superuser-backendlog_destination | csvlog | | sighuplog_directory | /data/pg_log | | sighuplog_disconnections | off | | superuser-backendlog_duration | off | | superuserlog_error_verbosity | default | | superuserlog_executor_stats | off | | superuserlog_file_mode | 0600 | | sighuplog_filename | postgresql_log.%a | | sighuplog_hostname | off | | sighuplog_line_prefix | %t [%p] [%l-1] user%u db%d app%a client%h | | sighuplog_lock_waits | on | | superuserlog_min_duration_statement | 60000 | ms | superuserlog_min_error_statement | error | | superuserlog_min_messages | warning | | superuserlog_parser_stats | off | | superuserlog_planner_stats | off | | superuserlog_replication_commands | off | | superuserlog_rotation_age | 1440 | min | sighuplog_rotation_size | 10240 | kB | sighuplog_statement | none | | superuserlog_statement_stats | off | | superuserlog_temp_files | 0 | kB | superuserlog_timezone | Asia/Jakarta | | sighuplog_truncate_on_rotation | on | | sighuplogging_collector | on | | postmaster2.开启日志 name | setting | unit | context
----------------------------------------------logging_collector | on | | postmaster3.指定日志目录 name | setting | unit | context
--------------------------------------------log_directory | /data/pg_log | | sighup4.設置文件名format name | setting | unit | context
-------------------------------------------------------------log_filename | postgresql-%Y-%m-%d_%H%M%S.log | | sighup5.設置日志文件產出模式
可選模式 1.syslog 2.csvlog 3.sterr name | setting | unit | context
-----------------------------------------log_destination | csvlog | | sighup6.設置日志记录格式 name | setting | unit | context
-------------------------------------------------------------------------------log_line_prefix | %t [%p] [%l-1] user%u db%d app%a client%h | | sighup參數說明(此部分在postgresql.conf文件中有詳細說明)抄錄如下: log_line_prefix ‘%t [%p] [%l-1] user%u db%d app%a client%h’ # special values: # %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 milliseconds # %n timestamp with milliseconds (as a Unix epoch) # %i command tag # %e SQL state # %c session ID # %l session line number # %s session start timestamp # %v virtual transaction ID # %x transaction ID (0 if none) # %q stop here in non-session processes # %% ‘%’ # e.g. %u%%%d ’
7.日誌輪換
7.1非截斷式輪換 name | setting | unit | context
--------------------------------------------------log_rotation_age | 1440 | min | sighuplog_rotation_size | 10240 | kB | sighuplog_truncate_on_rotation | off | | sighup以上設置基本策略將是假如log time1440分鐘 or log size10240kb,當logfile.log_truncate_on_rotation off 將會按照 步驟4與步驟5中log_filename雨log_destination中設定的format產生新的logfile不會覆蓋舊的日誌隨著時間推移將會有撐滿disk空間的風險因此需要定時手工或設定排程刪除舊的日誌文件 輸出範本如下
postgresPCNIY-PROD01:/data/pg_log$ ls -alh|sort -k 9
-rw------- 1 postgres postgres 11M Jul 31 23:22 postgresql-2023-07-31_204013.csv
-rw------- 1 postgres postgres 11M Aug 1 02:03 postgresql-2023-07-31_232213.csv
-rw------- 1 postgres postgres 11M Aug 1 04:35 postgresql-2023-08-01_020327.csv
-rw------- 1 postgres postgres 11M Aug 1 07:08 postgresql-2023-08-01_043532.csv
-rw------- 1 postgres postgres 10M Aug 1 08:54 postgresql-2023-08-01_070832.csv
-rw------- 1 postgres postgres 11M Aug 1 10:25 postgresql-2023-08-01_085407.csv
-rw------- 1 postgres postgres 2.4M Aug 1 10:47 postgresql-2023-08-01_102541.csv7.2 截斷式輪換
如果logfile.log_truncate_on_rotation on則會覆蓋原來的日誌。 同時也需要注意該參數只是針對時間到期的切換如果是因為大小或者系統重啟發生切換時並不會覆蓋已有的檔。
log_truncate_on_rotation on # If on, an existing log file with the# same name as the new log file will be# truncated rather than appended to.# But such truncation only occurs on# time-driven rotation, not on restarts# or size-driven rotation. Default is# off, meaning append to existing files# in all cases.設置為ON後結合log_filename設置可以實現以周為單位的輪轉 name | setting | unit | context
------------------------------------------------log_filename | postgresql_log.%a | | sighup輸出將是如下
postgrespgd-prod01:/data/pg_log$ ls -alh|grep csv
-rw------- 1 postgres postgres 18M Jul 28 23:59 postgresql_log.Fri.csv
-rw------- 1 postgres postgres 12M Jul 31 23:58 postgresql_log.Mon.csv
-rw------- 1 postgres postgres 366K Jul 29 23:57 postgresql_log.Sat.csv
-rw------- 1 postgres postgres 319K Jul 30 23:57 postgresql_log.Sun.csv
-rw------- 1 postgres postgres 19M Jul 27 23:56 postgresql_log.Thu.csv
-rw------- 1 postgres postgres 10M Aug 1 10:56 postgresql_log.Tue.csv
-rw------- 1 postgres postgres 15M Jul 26 23:56 postgresql_log.Wed.csv8.日誌記錄內容
8.1 log_statement
可選的選項:none, ddl, mod, all name | setting | unit | context
-----------------------------------------------log_statement | ddl | | superuser8.2 log_min_duration_statement
此選項需要注意時間設置單為為millisecond,設定值太小將會產生過多的log,設置太大又有漏掉低效率的sql的可能 name | setting | unit | context
------------------------------------------------------log_min_duration_statement | 60000 | ms | superuser9 輸出範本
以上步驟產生輸出範本如下
2023-08-01 11:29:26.505 WIB,wmspci_app,pccwms502Zdb,73203,172.19.2.74:35852,
64c88352.11df3,1,SELECT,2023-08-01 11:00:18 WIB,54/7075354,845858776,LOG,00000,
duration: 1746278.542 ms execute unnamed:
select * from pro_sap_to_bom_part($1,$2,$3) as result,
parameters: $1 30, $2 , $3 ,,,,,,,,
