I have often thought that PostgreSQL logging although very flexible is also unwieldy. PostgreSQL has so many logging options, it is difficult if not impossible to find the "right" logging setting without a lot of trial and error.
Let's just break it down. Below is all of the logging options available to DBAs when 8.3 hits store shelves.
#log_destination = 'stderr'
# This is used when logging to stderr:
#logging_collector = off
Why do we have both of the above? Let's just have:
Which would be either, stderr, syslog, cvslog, eventlog, or file.
# These are only used if logging_collector is on:
#log_directory = 'pg_log'
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
#log_truncate_on_rotation = off
#log_rotation_age = 1d
#log_rotation_size = 10MB
This is a tough one because we need to be able to rotate logs and things like logrotate can loose information when rotating. However, logrotate can also execute arbitrary commands so perhaps logrotate can be instructed to execute a psql query that will cause logging to pause during rotation? Once rotation is complete logrotate can execute another query to tell postgresql to start logging again.
For those who are not running a logrotate capable machine, use eventlog or syslog and the problem goes away entirely.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
No problem here.
#client_min_messages = notice
O.k. why do I care what my client sets its min_messages to. I am the server. If you want to be able to make it configurable, fine. Use ALTER USER, but leave it out of my conf. It is just noise.
#log_min_messages = notice
#log_error_verbosity = default
#log_min_error_statement = error
O.k. I really only need one of these. The log_min_messages should just take:
The verbosity should be inclusive as should the logging of the statement causing the offending warning, error, debug2 etc...
#log_min_duration_statement = -1
You can take log_min_duration_statement from my cold dead fingers.
#silent_mode = off
The silent_mode is useless noise in this context.
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
I don't actually have a problem with the above, but it seems they should be pushed out of the general logging section.
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
Yeah all of these should be pushed into some level within log_min_messages. Checkpoints are obviously something to be logged and likely at the NOTICE or INFO level. As far as log_connection/disconnections... if you want that as an option, fine but we only need one option... log_connection. The disconnection should be inclusive. Then we come to log_duration which is obviously useful and I am not sure exactly where to push it in log_min_messages. Maybe something like DEBUG and higher automatically provide duration and offer STATEMENT and STATEMENT_DURATION?
#log_hostname = off
O.k. log_hostname is something people probably ask for. I personally always leave it off due to performance problems. It almost seems like one of those options that is there for the sake of having it, not because anyone in their right mind would use it.
#log_line_prefix = ''
Ahhh log_line_prefix, I like this one. Don't touch it.
#log_lock_waits = off
What? Why in the world did we do this? Push it into DEBUG and above and call it good.
#log_statement = 'none'
Another option that really belongs in log_min_messages. Add STATEMENT_DML, STATEMENT_MOD, where STATEMENT (see previous mention) is a implicit ALL.
#log_temp_files = -1
Yeah... DEBUG or above.
#log_timezone = unknown
Uhm... log_line_prefix anyone? Or perhaps part of log_connections?
Well that's it for at least the next hour.