Why log_statement may not work for a particular database?

Поиск
Список
Период
Сортировка
От Андрей Платонов
Тема Why log_statement may not work for a particular database?
Дата
Msg-id CAAVNPJJ5YsrKKd2VAhfcXiZa7uAyDZFSv+gCs=z6ft45ovBmLw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Why log_statement may not work for a particular database?  (jacob ndinkwa <jndinkwa@gmail.com>)
Re: Why log_statement may not work for a particular database?  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-admin
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



В списке pgsql-admin по дате отправления:

Предыдущее
От: J T
Дата:
Сообщение: Re: GENERATE AS
Следующее
От: jacob ndinkwa
Дата:
Сообщение: Re: Why log_statement may not work for a particular database?