Archive for July, 2013

5 July, 2013

postgresql.conf: rudimentary log settings, annotated

by gorthx

One of the first changes I make in a new Postgres install is to enable activity logging. This way, if I encounter some problems down the the road, I’ve already got some data collected that may help me troubleshoot.

Here are my basic settings:

# enable logging, of course:
log_destination = 'stderr'
logging_collector = on

# target directory, on its own mount point if you can do it
log_directory = '/pg_log'

# I just use the default here
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# log_line_prefix in pgbadger-compatible format
# timestamp [pid]: line_number user application remote_host_&_port database %
log_line_prefix = '%t [%p]: [%l-1] %u %a %r %d %%'

# so I know when people are tinkering
log_statement = 'ddl'

# pre-emptive troubleshooting items
log_checkpoints = on

# log all statements that take longer than 10 minutes
# YMMV here - adjust for your application
log_min_duration_statement = 10min

# keep track of longer-running autovac jobs; may indicate that I need to tweak my autovacuum settings
# again, YMMV
log_autovacuum_min_duration = 10min

# again, I want to keep track of these; can be a harbinger of problems
# you may need to adjust your deadlock_timeout
log_lock_waits = on

# translates an IP to a hostname.
# Useful, but can cause problems on high-activity systems, so use with caution.
log_hostname = on

What are your favorite log settings?