PostgreSQL logging basics
Topic: Databases core
Summary
Configure PostgreSQL logging with log_destination, log_directory, and log_filename. Use log_min_duration_statement for slow queries and log_connections for audit. Use this when debugging or meeting audit requirements.
Intent: How-to
Quick answer
- Set log_destination = 'stderr' or 'csvlog'. log_directory and log_filename control file location. Restart or reload to apply. Logs often go to journald when using stderr.
- log_min_duration_statement = 1000 logs queries over 1 second. log_connections = on and log_disconnections = on for connection audit. log_statement = 'all' for full query log (use sparingly).
- Rotate logs with logrotate or let journald manage. Avoid logging passwords; use log_line_prefix to add timestamps and session info.
Prerequisites
Steps
-
Set destination and files
postgresql.conf: log_destination, log_directory, log_filename. Reload. Check data directory or systemd journal for logs.
-
Enable useful log types
log_min_duration_statement for slow queries; log_connections/log_disconnections for audit. Avoid log_statement=all in production unless debugging.
-
Rotate and secure
Configure logrotate for file logs; never log passwords. Use log_line_prefix for consistent parsing.
Summary
Configure PostgreSQL logging for files or stderr; enable slow-query and connection logging; rotate and secure logs.
Prerequisites
Steps
Step 1: Set destination and files
Set log_destination, log_directory, log_filename; reload and confirm logs appear.
Step 2: Enable useful log types
Enable slow-query and connection logging; avoid full query logging in production.
Step 3: Rotate and secure
Use logrotate or journald; do not log secrets; set log_line_prefix.
Verification
- Logs appear in the configured location; slow queries and connections are logged as intended.
Troubleshooting
No logs — Check log_destination and permissions; ensure reload was done. Disk full — Rotate and trim retention; reduce verbosity.