Unable to disable SQL statement logging in PostgreSQL 9.1

Question

I am running PostgreSQL 9.1 on Windows Server 2008 R2. I enabled SQL statement logging to the stderr log by modifying my postgresql.conf file, but now I can't seem to disable the logging without turning off all logging. Server was sent SIGHUP signals via pg_ctl reload, and the service has been restarted after each attempt to fix this. Rebooting the entire machine did not seem to help either.

Specific lines from my current postgresql.conf that should have accomplished this, but aren't:

log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.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
#log_duration = off
#log_error_verbosity = default
log_hostname = on
log_line_prefix = '%t %a %u %r '
#log_lock_waits = off
log_statement = 'none' 
#track_activities = on
#track_counts = on
#track_functions = none 
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'

I've checked the manual; it seems all I need to do is ensure that the log_statement line in the config file is set appropriately.

However, nothing I do seems to have any effect on what is actually being logged. If I turn off the logging_collector, the text log files are no longer generated. This would indicate that PostgreSQL is at least pointed to the correct postgres.conf file, and that PostgreSQL is reading it to determine its configuration.

What do I do to turn of SQL statement logging?

• Jack Douglas: whats the setting of log_min_duration_statement?

Answer 1

There are a number of relevant settings missing in your list. Check the chapter "When to log" in the manual, in particular these settings:

log_min_messages (enum)
log_min_error_statement (enum)
log_min_duration_statement (integer) -- as @Jack already hinted in his comment.

Answer 2

Could you have ALTER USER someuser SET log_statement = 'all'; or ALTER DATABASE somedb SET log_statement = 'all'?

These settings will override the default set in postgresql.conf for the DB/user they apply to.

If this is the case you'll find that you don't get statements logged if you connect to a newly created database using a newly created user.

If this proves to be the case, remove with:

ALTER USER someuser RESET log_statement;
ALTER DATABASE somedb RESET log_statement;