如果我SET client_min_messages TO log;
那么简单的命令select 1
会显示很多信息。喜欢
localhost:5433 admin@test=# select 1;
LOG: statement: select 1;
LOG: QUERY STATISTICS
DETAIL: ! system usage stats:
! 0.000137 s user, 0.000036 s system, 0.000171 s elapsed
! [3.429744 s user, 0.904205 s system total]
! 38900 kB max resident size
! 0/0 [2720/7200] filesystem blocks in/out
! 0/0 [979/2849] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [766/40] voluntary/involuntary context switches
+----------+
| ?column? |
+----------+
| 1 |
+----------+
如果我 SET client_min_messages TO NOTICE;
那么我不能直接看到日志。
因此,可以配置为仅显示执行时间大于阈值(例如 200 毫秒)的查询日志。
以下是我目前的配置。
select name, setting from pg_settings where name ilike '%log%';
| name | setting |
+------------------------------------+------------------------------------------------+
| auto_explain.log_analyze | on |
| auto_explain.log_buffers | off |
| auto_explain.log_format | text |
| auto_explain.log_level | log |
| auto_explain.log_min_duration | 300 |
| auto_explain.log_nested_statements | on |
| auto_explain.log_settings | off |
| auto_explain.log_timing | on |
| auto_explain.log_triggers | off |
| auto_explain.log_verbose | off |
| auto_explain.log_wal | off |
| log_autovacuum_min_duration | 0 |
| log_checkpoints | on |
| log_connections | on |
| log_destination | jsonlog |
| log_directory | log |
| log_disconnections | on |
| log_duration | off |
| log_error_verbosity | default |
| log_executor_stats | off |
| log_file_mode | 0600 |
| log_filename | postgresql-%Y-%m-%d_%H%M%S.log |
| log_hostname | off |
| log_line_prefix | %t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h |
| log_lock_waits | on |
| log_min_duration_sample | -1 |
| log_min_duration_statement | 200 |
| log_min_error_statement | error |
| log_min_messages | warning |
| log_parameter_max_length | -1 |
| log_parameter_max_length_on_error | 0 |
| log_parser_stats | off |
| log_planner_stats | off |
| log_recovery_conflict_waits | off |
| log_replication_commands | off |
| log_rotation_age | 1440 |
| log_rotation_size | 10240 |
| log_startup_progress_interval | 10000 |
| log_statement | all |
| log_statement_sample_rate | 1 |
| log_statement_stats | on |
| log_temp_files | 0 |
| log_timezone | Asia/Calcutta |
| log_transaction_sample_rate | 0 |
| log_truncate_on_rotation | off |
| logging_collector | off |
| logical_decoding_work_mem | 65536 |
| max_logical_replication_workers | 4 |
| syslog_facility | local0 |
| syslog_ident | postgres |
| syslog_sequence_numbers | on |
| syslog_split_messages | on |
| wal_log_hints | off |
+------------------------------------+------------------------------------------------+
select name, setting from pg_settings where name ilike '%messages%';
+-------------------------+---------+
| name | setting |
+-------------------------+---------+
| client_min_messages | notice |
| lc_messages | C |
| log_min_messages | warning |
| syslog_split_messages | on |
| trace_recovery_messages | log |
+-------------------------+---------+
SELECT name, setting FROM pg_settings WHERE source <> 'default';
返回:
+-------------------------------------+------------------------------------------------+
| name | setting |
+-------------------------------------+------------------------------------------------+
| application_name | psql |
| auto_explain.log_analyze | on |
| auto_explain.log_min_duration | 300 |
| auto_explain.log_nested_statements | on |
| client_encoding | UTF8 |
| client_min_messages | notice |
| cluster_name | 15/main |
| config_file | /etc/postgresql/15/main/postgresql.conf |
| data_checksums | off |
| data_directory | /var/lib/postgresql/15/main |
| DateStyle | ISO, MDY |
| default_text_search_config | pg_catalog.english |
| dynamic_shared_memory_type | posix |
| external_pid_file | /var/run/postgresql/15-main.pid |
| hba_file | /etc/postgresql/15/main/pg_hba.conf |
| ident_file | /etc/postgresql/15/main/pg_ident.conf |
| idle_in_transaction_session_timeout | 2400000 |
| lc_collate | C.UTF-8 |
| lc_ctype | C.UTF-8 |
| lc_messages | C |
| lc_monetary | C.UTF-8 |
| lc_numeric | C.UTF-8 |
| lc_time | C.UTF-8 |
| log_autovacuum_min_duration | 0 |
| log_checkpoints | on |
| log_connections | on |
| log_destination | jsonlog |
| log_disconnections | on |
| log_line_prefix | %t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h |
| log_lock_waits | on |
| log_min_duration_statement | 200 |
| log_statement | all |
| log_statement_stats | on |
| log_temp_files | 0 |
| log_timezone | Asia/Calcutta |
| max_connections | 100 |
| max_stack_depth | 2048 |
| max_wal_size | 1024 |
| min_wal_size | 80 |
| port | 5433 |
| search_path | regress_rls_schema, collate_tests, test |
| server_encoding | UTF8 |
| shared_buffers | 16384 |
| shared_memory_size | 143 |
| shared_memory_size_in_huge_pages | 72 |
| ssl | on |
| ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem |
| ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key |
| TimeZone | Asia/Calcutta |
| track_commit_timestamp | on |
| track_functions | all |
| track_io_timing | on |
| track_wal_io_timing | on |
| transaction_deferrable | off |
| transaction_isolation | read committed |
| transaction_read_only | off |
| unix_socket_directories | /var/run/postgresql |
| wal_buffers | 512 |
| wal_segment_size | 16777216 |
+-------------------------------------+------------------------------------------------+
你不能完全按照你的意愿去做(除了重新编译代码),因为日志记录选项不是那么可配置的。
使用股票 PostgreSQL,最接近您想要的可能是将 auto_explain.log_level 设置为 WARNING 或 NOTICE,并将 auto_explain.log_min_duration 设置为 200。这将为您提供发送到 psql 的查询、持续时间和该持续时间的查询计划. 显然,您不想看到计划或查询,只希望看到持续时间,但这可能是您可以得到的最接近的。我说这是尽可能接近,因为没有设置可以让您更改 log_min_duration_statement 生成的消息的日志级别,以便您可以提升它,这似乎是您真正想要做的(除了它发送查询文本)
使用 WARNING 会将其发送到客户端和日志文件,而使用 NOTICE 将仅将其发送到客户端——假设其他相关参数为默认值。
但是你的整体设置对我来说没有多大意义。
SET client_min_messages TO log;
打开log_statement_stats并不是很常用,而且 IME 在有用时总是与 而硬币的另一面,log_statement=all 可能很有用,但不能与SET client_min_messages TO log;
. 我刚刚运行的命令在我的回滚缓冲区中,我也不需要它实时返回给我。