08:00

Change these: Logging

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_directory and 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.

There is one comment.