Обсуждение: PostgreSQL 9.2 Logging
I'm trying to understand how 9.2.4 logs common tasks and info in CentOS 6.4 Linux. It appears everything is stored in /var/lib/pgsql9/data/pg_log/postgresql-%a.log My issue is the logging information is fairly missing / light. I only see auth failures and nothing more. I tried to perform my 1st VACUUM command on my database and I was hoping to see something in the logs showing it PASSED / FAILED or even was manually initiated by a superuser role but I see nothing... I don't need to log every single verbose thing this database server does but I would expect something like a VACUUM command would be warranted to be logged, no? #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - When to Log - ^^^^ Not sure what to change here... # - What to Log - #debug_print_parse = off #debug_print_rewritten = off #debug_print_plan = off #debug_pretty_print = on #log_checkpoints = off #log_connections = off #log_disconnections = off Do you PG veterans have any log change recommendations / changes I can make which would help me understand what my system is doing? I don't want to log every little thing possible and choke my disk with informative logging but just basic 'what's going on' logs would be awesome. Thanks for any help! -- Carlos Mennens
My issue is the logging information is fairly missing / light. I only
see auth failures and nothing more. I tried to perform my 1st VACUUM
command on my database and I was hoping to see something in the logs
showing it PASSED / FAILED or even was manually initiated by a
superuser role but I see nothing...
AFAIK, there's no separate flags written in logs for any utility commands like succeeded or not.
Albeit, you can log the command executed and the time consumed.
I don't need to log every single verbose thing this database server
does but I would expect something like a VACUUM command would be
warranted to be logged, no?
Do you PG veterans have any log change recommendations / changes I can
make which would help me understand what my system is doing? I don't
want to log every little thing possible and choke my disk with
informative logging but just basic 'what's going on' logs would be
awesome.
You can control the logging model with many parameters in postgresql.conf file, however,
log_min_duration_statement will help you log only those statements which are taking some X seconds.
For example: If I want to log only statement which are taking more than a second, I might not bother what are
those statement then I would set like:
postgres=# show log_min_duration_statement ;
log_min_duration_statement
----------------------------
1s
(1 row)
This will log every statement equal or greater than the values passed to it. Now in my logs:
2013-08-13 03:07:01.146 IST [14152]: [9-1] db=,user= LOG: parameter "log_min_duration_statement" changed to "1s"
2013-08-13 03:08:03.297 IST [26559]: [1-1] db=d1,user=postgres LOG: duration: 2159.281 ms statement: vacuum VERBOSE ANALYZE ;
On Wed, Aug 21, 2013 at 9:55 PM, carlosinfl . <carlos.mennens@gmail.com> wrote: > #debug_print_parse = off > #debug_print_rewritten = off > #debug_print_plan = off > #debug_pretty_print = on > #log_checkpoints = off > #log_connections = off > #log_disconnections = off > The debug_* will log, well, debug information (e.g., the query being rewritten and the query tree). The log_*connections can be useful to see who is using the system. I would also evaluate: #log_min_messages = warning Also #log_statement = 'none' that can be used to log all commands (query and utility), only ddl (e.g., alter table), and so on. Hope this helps. Luca
On Wed, Aug 21, 2013 at 12:55 PM, carlosinfl . <carlos.mennens@gmail.com> wrote: > I'm trying to understand how 9.2.4 logs common tasks and info in > CentOS 6.4 Linux. It appears everything is stored in > /var/lib/pgsql9/data/pg_log/postgresql-%a.log > > My issue is the logging information is fairly missing / light. I only > see auth failures and nothing more. I tried to perform my 1st VACUUM > command on my database and I was hoping to see something in the logs > showing it PASSED / FAILED or even was manually initiated by a > superuser role but I see nothing... I'm not sure why that is of interest. If you invoked the command from psql interactively, you get the results on your screen. If you do it in a script, you can arrange to log the results from that script. > I don't need to log every single verbose thing this database server > does but I would expect something like a VACUUM command would be > warranted to be logged, no? Fast running manually invoked vacuums seem like one of the least interesting things to log to me. I can see wanting to log slow vacuums, but why just vacuum and not all slow statements? (Which you can already do) But it does seem like maybe an option of 'util' for log_statement would be reasonable to have. Cheers, Jeff