PostgreSQL数据库的物理存储结构主要是指硬盘上存储的文件，包括：数据文件、日志文件、参数文件、控制文件、WAL预写日志文件等等。而PostgreSQL的日志文件又分为运行日志、WAL预写日志、事务日志和服务器日志。
下面重点讨论一下PostgreSQL的运行日志文件。视频讲解如下：
在默认的情况下，运行日志没有开启。通过查看主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" ...
