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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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



[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux