Thread: Why log_statement may not work for a particular database?
Hello Андрей,
Can you check your parameter file to make sure "logging_collector" is ON?
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