Re: Why log_statement may not work for a particular database?

Hello Андрей,

Can you check your parameter file to make sure "logging_collector" is ON?

[image: image.png]

On Fri, Jun 23, 2023 at 3:38 AM Андрей Платонов <poluandrey(at)gmail(dot)com>
wrote:

> Hi!
> I have a strange behavior with my logging setting of PostgreSQL 10.21
> version. Statement logging from one of the databases does not get into
> the log(stderr).
>
>
> Databases
> ```
> mydatabase=# \l+
> List
> of databases
> Name | Owner | Encoding | Collate | Ctype | Access
> privileges | Size | Tablespace | Description
>
> -----------+----------+-----------+---------+-------+------------------------+---------+------------+--------------------------------------------
> demo2 | postgres | SQL_ASCII | C | C | =Tc/postgres
> +| 8063 kB | pg_default |
> | | | | |
> postgres=CTc/postgres +| | |
> | | | | |
> demo2=CTc/postgres | | |
> postgres | postgres | SQL_ASCII | C | C |
> | 8487 kB | pg_default | default administrative connection
> database
> template0 | postgres | SQL_ASCII | C | C | =c/postgres
> +| 7663 kB | pg_default | unmodifiable empty database
> | | | | |
> postgres=CTc/postgres | | |
> template1 | postgres | SQL_ASCII | C | C |
> postgres=CTc/postgres +| 8039 kB | pg_default | default template for
> new databases
> | | | | | =c/postgres
> | | |
> mydatabase | postgres | SQL_ASCII | C | C | =Tc/postgres
> +| 1494 GB | pg_default |
> ```
>
> Log settings in a configuration file
> ```
> log_destination = 'stderr'
> log_min_messages = info # values in order of decreasing detail:
> # log
> log_min_error_statement = info # values in order of decreasing
> detail:
> # log
> log_min_duration_statement = 0 # -1 is disabled, 0 logs all
> statements
> # and their durations, > 0 logs
> only
> log_checkpoints = on
> log_connections = off
> log_disconnections = off
> log_duration = on
> log_line_prefix = '%m [%p] %d ' # special values:
> log_lock_waits = on # log lock waits >=
> deadlock_timeout
> log_statement = 'mod' # none, ddl, mod, all
> # -1 disables, 0 logs all temp
> files
> log_timezone = 'UTC'
> log_parser_stats = off
> log_planner_stats = off
> log_executor_stats = off
> log_statement_stats = off
> ```
>
> As I understand settings above should lead to the fact that I should
> see statement logging for all databases into `stderr` (in fact into
> journald), but in fact I have statement logs only for databases -
> `demo2` and `postgres` not for `mydatabase` and I can't figure out
> what wrong and where can be a mistake?!
>
> I also checked the settings of the `mydatabase`
> ```
> mydatabase=# SELECT name, setting FROM pg_settings WHERE name LIKE '%log%';
> name | setting
> ------------------------------------+--------------------------------
> log_autovacuum_min_duration | -1
> log_checkpoints | on
> log_connections | off
> log_destination | stderr
> log_directory | log
> log_disconnections | off
> log_duration | on
> 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 | %m [%p] %d
> log_lock_waits | on
> log_min_duration_statement | 0
> log_min_error_statement | panic
> log_min_messages | panic
> log_parser_stats | off
> log_planner_stats | off
> log_replication_commands | off
> log_rotation_age | 1440
> log_rotation_size | 10240
> log_statement | mod
> log_statement_stats | off
> log_temp_files | -1
> log_timezone | UTC
> log_truncate_on_rotation | off
> logging_collector | off
> ```
>
> and trying to change the `log_statement` to `all` and `ddl` but it
> didn't lead to anything, I still saw statements in journald only for
> `postgres` and `demo2` databases
>
>
>