PostgreSQL 开启日志记录慢 SQL 等关键信息

开启日志 SQL

alter system set logging_collector = on;
alter system set log_min_duration_statement = '250ms';
alter system set log_statement = mod;
alter system set log_line_prefix = '%m [%p] %q%u@%d/%a ';
alter system set log_rotation_age = '30d';
alter system set log_rotation_size = '50MB';
alter system set log_connections = on;
alter system set log_disconnections = on;

热加载

select pg_reload_conf();

注意

对于修改了logging_collector配置，需要重启PostgreSQL。

pg_ctl -D xxx restart

log_destination

• stderr：普通文本，输出到log格式文件中
• csvlog：CSV格式，输出到csv格式文件中
• jsonlog：JSON格式，输出到json格式文件中
• eventlog：只在Windows平台支持

stderr log/postgresql.log
csvlog log/postgresql.csv
jsonlog log/postgresql.json

logging_collector

日志聚合功能，默认日志是pg_ctl命令启动时指定的-l参数的日志文件。开启该参数后，日志被重定向到log_directory中。

log_directory

logging_collector开启后，日志输出的目录。

log_filename

日志文件名，默认：postgresql-%Y-%m-%d_%H%M%S.log。

支持strftime格式。

• %a：星期的英文单词的缩写：如星期一， 则返回Mon
• %A：星期的英文单词的全拼：如星期一，返回Monday
• %b：月份的英文单词的缩写：如一月， 则返回Jan
• %B：月份的引文单词的缩写：如一月， 则返回January
• %c：返回datetime的字符串表示，如03/08/15 23:01:26
• %d：返回的是当前时间是当前月的第几天
• %f：微秒，范围：[0,999999]
• %H：以24小时制表示当前小时
• %I：以12小时制表示当前小时
• %j：返回当天是当年的第几天，范围：[001,366]
• %m：返回月份，范围：[0,12]
• %M：返回分钟数，范围：[0,59]
• %P：返回是上午还是下午，AM/PM
• %S：返回秒数，范围：[0,61]（手册说明的）
• %U：返回当周是当年的第几周，以周日为第一天
• %W：返回当周是当年的第几周，以周一为第一天
• %w：当天在当周的天数，范围为[0, 6]，6表示星期天
• %x：日期的字符串表示：03/08/24
• %X：时间的字符串表示：23:22:08
• %y：两个数字表示的年份：24
• %Y：四个数字表示的年份：2024
• %z：与UTC时间的间隔 （如果是本地时间，返回空字符串）
• %Z：时区名称（如果是本地时间，返回空字符串）

log_filename = 'postgresql-%I.log' #最多保存12小时的日志，每小时一个文件
log_filename = 'postgresql-%H.log' #最多保存24小时的日志，每小时一个文件
log_filename = 'postgresql-%w.log' #最多保存一周的日志，每天一个文件
log_filename = 'postgresql-%d.log' #最多保存一个月的日志，每天一个文件
log_filename = 'postgresql-%j.log' #最多保存一年的日志，每天一个文件

log_min_duration_statement

SQL语句执行超过log_min_duration_statement设置的值后，记录在日志文件中。

备注：log_statement为none也能记录慢SQL。

log_statement

记录SQL语句类型，none/ddl/mod/all，默认none。

备注：如果设置为all会有很多日志，可能造成干扰。

• ddl：CREATE/ALTER/DROP
• mod：INSERT/UPDATE/DELETE/TRUNCATE/COPY FROM/PREPARE/EXECUTE/EXPLAIN ANALYZE`

log_line_prefix

日志格式。

log_lock_waits

记录会话被锁，等待时间超过deadlock_timeout。

log_connections

记录客户端连接日志。

connection received: host=127.0.0.1 port=51661
connection authorized: user=postgres database=postgres

log_disconnections

记录客户端断开日志。

disconnection: session time: 0:00:05.782 user=postgres database=postgres host=[local]

log_rotation_age

日志归档时间，超过时间后生成新的日志文件。默认1天。可用单位如下：

• us：微秒
• ms：毫秒
• s：秒
• min：分钟
• h：小时
• d：天

log_rotation_size

日志归档大小，超过大小后生成新的日志文件。默认10MB，可用单位如下：

• B
• kB
• MB
• GB
• TB

更多配置

show data_directory; -- 数据存储目录
show log_destination; -- 日志文件类型，stderr/csvlog/jsonlog/eventlog，默认：stderr
show logging_collector;
show log_directory; -- 日志存储目录，默认：数据存储目录下的 log 目录
show log_filename; -- 日志文件名称，默认：postgresql-%Y-%m-%d_%H%M%S.log
show log_min_duration_statement;
show log_statement; -- 记录 SQL 语句类型，none/ddl/mod/all，默认：none
show log_line_prefix;
show log_lock_waits;
show log_connections;
show log_disconnections;
show log_rotation_age; -- 日志归档时间，默认 1 天
show log_rotation_size; -- 日志归档大小，默认 10MB
show log_min_messages;
show log_truncate_on_rotation;
show log_duration; -- 记录每条 SQL 的耗时时间，生产环境不建议开启
show log_hostname;
show log_timezone;
show log_error_verbosity;
show log_autovacuum_min_duration;

相关 SQL

当前日志文件

备注：如果没有开启logging_collector，则返回null。

select pg_current_logfile();

日志文件详情

select * from pg_stat_file(pg_current_logfile());

输出

postgres=# select * from pg_stat_file(pg_current_logfile());
 size |         access         |      modification      |         change         | creation | isdir
------+------------------------+------------------------+------------------------+----------+-------
 3955 | 2024-06-13 15:32:58+08 | 2024-06-13 15:32:57+08 | 2024-06-13 15:32:57+08 |          | f
(1 row)

读取全量日志文件

select pg_read_file(pg_current_logfile());

读取最新日志 1000 字符

select pg_read_file(pg_current_logfile(), (select size from pg_stat_file(pg_current_logfile())) - 1000, 1000);

参考文档

https://www.postgresql.org/docs/16/runtime-config-logging.html