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?