Postgresql

PostgreSQL 不記錄

  • January 8, 2021

我正在使用9.4.4ArchLinuxPostgreSQL在配置文件中啟用了日誌記錄:

$ sudo egrep -v "^[[:blank:]]*($|#|//|/\*| \*|\*/)" /var/lib/postgres/data/postgresql.conf

max_connections = 1024          # (change requires restart)
shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option
logging_collector = on      # Enable capturing of stderr and csvlog
log_directory = '/tmp'      # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0644            # creation mode for log files,
log_error_verbosity = verbose       # terse, default, or verbose messages
log_statement = 'all'           # none, ddl, mod, all
log_timezone = 'Asia/Jakarta'
datestyle = 'iso, dmy'
timezone = 'Asia/Jakarta'
lc_messages = 'en_CA.UTF-8'         # locale for system error message
lc_monetary = 'en_CA.UTF-8'         # locale for monetary formatting
lc_numeric = 'en_CA.UTF-8'          # locale for number formatting
lc_time = 'en_CA.UTF-8'             # locale for time formatting
default_text_search_config = 'pg_catalog.english'

然後重新啟動或重新載入服務:

sudo systemctl restart postgresql
sudo systemctl reload postgresql

載入的設置:

postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE '%log%';
           name             |            setting             
-----------------------------+--------------------------------
log_autovacuum_min_duration | -1
log_checkpoints             | off
log_connections             | off
log_destination             | stderr
log_directory               | /tmp
log_disconnections          | off
log_duration                | off
log_error_verbosity         | verbose
log_executor_stats          | off
log_file_mode               | 0644
log_filename                | postgresql-%Y-%m-%d_%H%M%S.log
log_hostname                | off
log_line_prefix             | 
log_lock_waits              | off
log_min_duration_statement  | -1
log_min_error_statement     | error
log_min_messages            | warning
log_parser_stats            | off
log_planner_stats           | off
log_rotation_age            | 1440
log_rotation_size           | 10240
log_statement               | all
log_statement_stats         | off
log_temp_files              | -1
log_timezone                | Asia/Jakarta
log_truncate_on_rotation    | off
logging_collector           | on
syslog_facility             | local0
syslog_ident                | postgres
wal_log_hints               | off
(30 rows)

/tmp許可:

$ ll /tmp/ 
total 11656
drwxrwxrwt 22 root root       580 Jun 29 10:05 ./
drwxr-xr-x 17 root root      4096 Jun 10 08:04 ../
...

/tmp用法:

$ df | grep /tmp
tmpfs            8159996    31204   8128792   1% /tmp

做一些查詢psql,但/tmp/postgresql-*仍然沒有出現。我該怎麼做才能找到阻止創建日誌的部分?

編輯 1

的結果journalctl -u postgresql

Oct 08 09:13:53 xxx postgres[19861]: LOG:  00000: redirecting log output to logging collector process
Oct 08 09:13:53 xxx postgres[19861]: HINT:  Future log output will appear in directory "/tmp".
Oct 08 09:13:53 xxx postgres[19861]: LOCATION:  SysLogger_Start, syslogger.c:645
Oct 08 09:13:55 xxx systemd[1]: Started PostgreSQL database server.

日誌收集器嘗試擷取stderr並將其寫入文件。但看起來 systemd 贏得了重定向戰爭,並將輸出發送到日誌。您可以通過以下方式檢查 systemd 日誌:

journalctl -u postgresql

systemd 配置位於usr/lib/systemd/system/postgresql.service. 我嘗試了各種設置StandardError,但似乎沒有一個允許正常 Postgres 日誌記錄工作。

您的設置是log_destination=stderr 並且 log_collector 將 stderr 重定向到 /dev/null (硬編碼,因為它們的父子管道重定向非常老套)。您將需要設置log_destination=csvlogsyslog兩者兼而有之。

引用自:https://dba.stackexchange.com/questions/105384