6 April 2018
(An intermittent series on PostgreSQL parameters whose default settings you should change.)
Here’s my preferred logging configuration:
log_destination = 'csvlog' logging_collector = on log_directory = '/var/log/postgresql' log_filename = 'postgresql-%Y%m%d-%H%M%S.log' log_rotation_size = 1GB log_rotation_age = 1d log_min_duration_statement = 250ms log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_statement = 'ddl' log_temp_files = 0 log_autovacuum_min_duration = 1000
Of course, you can adjust these to taste. Set
log_filename as appropriate for your own directory structure and retention policy. (As listed, the log files will build up forever, so some sort of compress-and-delete strategy is required here.) You can set
log_min_duration_statement as appropriate to control log volume; if you can get away with
log_min_duration_statement = 0, great.
The others should be set as listed on any system; they don’t take up that much in terms of log volume, and the information provided is very helpful.