Обсуждение: Why log_statement may not work for a particular database?

Поиск
Список
Период
Сортировка

Why log_statement may not work for a particular database?

От
Андрей Платонов
Дата:
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

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

От
jacob ndinkwa
Дата:
Hello Андрей,

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

image.png

On Fri, Jun 23, 2023 at 3:38 AM Андрей Платонов <poluandrey@gmail.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


Вложения

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

От
Jeff Janes
Дата:
On Fri, Jun 23, 2023 at 3:38 AM Андрей Платонов <poluandrey@gmail.com> wrote:

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),

How does it get from stderr to journald?  Maybe the messages for other databases are getting filtered out after PostgreSQL generates them.  Maybe you could bypass journald for now while you investigate, just to rule out non-PostgreSQL related possibilities.  Or "set client_min_messages TO log" so that your client gets sent copies of log messages directly.
 

I also checked the settings of the `mydatabase`
```
mydatabase=# SELECT name, setting FROM pg_settings WHERE name LIKE '%log%';

This only checks the settings of a particular connection.  Did you use this same connection to issue statements that should have been logged?  Maybe a stupid question, but how do you know anything loggable is happening?  Maybe you don't see messages because nobody does stuff in that database which would generate them.

Cheers,

Jeff