Change these: Logging

(An intermittent series on PostgreSQL parameters whose default settings you should change.)

Here’s my preferred logging configuration:

1
log_destination = 'csvlog'
2
logging_collector = on
3
log_directory = '/var/log/postgresql'
4
log_filename = 'postgresql-%Y%m%d-%H%M%S.log'
5
log_rotation_size = 1GB
6
log_rotation_age = 1d
7
log_min_duration_statement = 250ms
8
log_checkpoints = on
9
log_connections = on
10
log_disconnections = on
11
log_lock_waits = on
12
log_statement = 'ddl'
13
log_temp_files = 0
14
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.