bring only long queries log to psql

All we need is an easy explanation of the problem, so here it is.

If I SET client_min_messages TO log; then trivial command like select 1 will display lots of info.
like

localhost:5433 [email protected]=# 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 |
+----------+

If I SET client_min_messages TO NOTICE; then I cannot see the log directly.
So it’s possible to config to only show the log of queries that execution time duration larger than a threshold(200 ms for example).

The following is my current config.


 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';

return:

+-------------------------------------+------------------------------------------------+
|                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                                       |
+-------------------------------------+------------------------------------------------+

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can’t do exactly what you want (other than by recompiling the code) because the logging options just are not that configurable.

With stock PostgreSQL, the closest to what you want would probably be to set auto_explain.log_level to WARNING or NOTICE, and set auto_explain.log_min_duration to 200. This would give you the query, duration and the plan for queries of that duration sent to psql. Apparently you don’t want to see the plan or query, only the duration, but this is probably as close as you can get. I say this is as close as you can get because there is no setting that lets you change the log level of the message generated by log_min_duration_statement so that you could elevate it, which seems to be what you really want to do (except that it sends the query text to)

Using WARNING will send it both to the client and to the log file, while using NOTICE will send it just to the client–assuming the other relevant parameters are at their defaults.

But your overall setting just don’t make much sense to me. Turning on log_statement_stats just isn’t very useful very often, and IME when it is useful it is always in conjunction with SET client_min_messages TO log; If you don’t want to see those stats immediately in the client, just turn them off. And the opposite side of that coin, log_statement=all can be useful, but not in conjunction with SET client_min_messages TO log;. The command I just ran is in my scrollback buffer, I don’t need it puked back to me in real time as well.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply