【趙渝強老師】PostgreSQL的執行日誌檔案

赵渝强老师發表於2024-12-04
SQL

PostgreSQL資料庫的物理儲存結構主要是指硬碟上儲存的檔案，包括：資料檔案、日誌檔案、引數檔案、控制檔案、WAL預寫日誌檔案等等。而PostgreSQL的日誌檔案又分為執行日誌、WAL預寫日誌、事務日誌和伺服器日誌。

下面重點討論一下PostgreSQL的執行日誌檔案。影片講解如下：
https://www.bilibili.com/video/BV1cBWoemEr8/?aid=113014833480...

在預設的情況下，執行日誌沒有開啟。透過檢視主postgresql.conf檔案的配置可以看到相關的引數設定，開啟後會自動生成該日誌檔案。執行時日誌一般是記錄資料庫伺服器與資料庫的狀態，比如各種錯誤資訊、定位慢查詢SQL、資料庫的啟動關閉資訊、發生檢查點過於頻繁等的告警資訊等等。該日誌有.csv格式和.log格式，建議使用.csv格式。因為.csv格式一般會按大小和時間自動切割。pg_log是可以被清理刪除、壓縮打包或者轉移，同時不影響資料庫的正常執行。當有遇到資料庫無法啟動或者更改引數沒有生效時，第一步就可以檢視執行時日誌。下面展示了主引數檔案postgresql.conf中關於執行日誌的配置引數。

#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off                # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'log'                  # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
#log_truncate_on_rotation = off         # 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.

一個推薦的PostgreSQL執行日誌的引數配置如下：

logging_collector = on
log_destination = 'csvlog'
log_directory = 'logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.csv'
log_truncate_on_rotation = on
log_connections = on
log_disconnections = on
log_statement = ddl
log_min_duration_statement = 60s
log_checkpoints = on
log_lock_waits = on
deadlock_timeout = 1s

這裡的引數設定還將死鎖的超時時間設定為了1秒鐘。當重啟PostgreSQL完成後，嘗試建立一張簡單的表，例如：

postgres=# \c scott 
scott=# create table testaudit as select * from emp;

檢視logs目錄下生成的日誌檔案，如下：

[postgres@mydb logs]$ pwd
/home/postgres/training/pgsql/data/logs
[postgres@mydb logs]$ ll
total 8
... postgresql-2024-10-06_084043.csv
... postgresql-2024-10-06_084043.csv.csv

檢視日誌檔案postgresql-2024-10-06_084043.csv.csv的內容，將得到如下的日誌資訊：

...
2024-10-06 08:41:31.165 CST,"postgres","scott",87330,"[local]",
6455a22f.15522,3,"idle",2024-10-06 08:41:19 CST,4/4,0,LOG,00000,
"statement: create table testaudit as select * from emp;",,,,,,,,,
"psql","client backend"
...

相關文章