Postgresql警告日志的配置

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

2.开启日志

       name        | setting | unit |  context   
-------------------+---------+------+------------logging_collector | on      |      | postmaster

3.指定日志目录

     name      |   setting    | unit | context 
---------------+--------------+------+---------log_directory | /data/pg_log |      | sighup

4.設置文件名format

     name     |            setting             | unit | context 
--------------+--------------------------------+------+---------log_filename | postgresql-%Y-%m-%d_%H%M%S.log |      | sighup

5.設置日志文件產出模式

可選模式：
1.syslog
2.csvlog
3.sterr

      name       | setting | unit | context 
-----------------+---------+------+---------log_destination | csvlog  |      | sighup

6.設置日志记录格式

      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 time>=1440分鐘 or log size>=10240kb,當logfile.log_truncate_on_rotation = off 將會按照
步驟4與步驟5中log_filename雨log_destination中設定的format產生新的logfile，不會覆蓋舊的日誌，隨著時間推移，將會有撐滿disk空間的風險，因此需要定時手工或設定排程刪除舊的日誌文件
輸出範本如下：

postgres@PCNIY-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.csv

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

輸出將是如下：

postgres@pgd-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.csv

8.日誌記錄內容

8.1 log_statement

可選的選項:none, ddl, mod, all

        name         | setting | unit |  context  
---------------------+---------+------+-----------log_statement       | ddl    |      | superuser

8.2 log_min_duration_statement

此選項需要注意時間設置，單為為millisecond,設定值太小將會產生過多的log,設置太大又有漏掉低效率的sql的可能

            name            | setting | unit |  context  
----------------------------+---------+------+-----------log_min_duration_statement | 60000   | ms   | superuser

9 輸出範本

以上步驟產生輸出範本如下：

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 = ''",,,,,,,,""