PostgreSQL records all activity and errors of cluster database into logs. From these logs, we can get useful information for troubleshooting, monitoring and debugging.
1. Where To Log
- Controls logging type for a database cluster
- Can be set to stderr, csvlog, jsonlog, syslog, and eventlog
- Enables logger process to capture stderr and csv logging messages
- These messages can be redirected based on configuration settings
Log File and Directory Settings
- log_directory - Directory where log files are written. The default is log
- log_filename - Format of log file name (e.g. postgresql-%Y-%m-%d_%H%M%S.log)
- log_file_mode - permissions for log files
- log_rotation_age - Used for file age-based log rotation
- log_rotation_size - Used for file size-based log rotation
2. When To Log
- log_min_messages: Messages of this severity level or above are sent to the server log
- log_min_error_statement: When a message of this severity or higher is written to the server log, the statement that caused it is logged along with it
- log_min_duration_statement: When a statement runs for at least this long, it is written to the server log
- log_autovacuum_min_duration: Logs any Autovacuum activity running for at least this long
- log_statement_sample_rate: Percentage of queries(above log_autovacuum_min_duration) to be logged
- log_transaction_sample_rate: Sample a percentage of transactions by logging statements
3. What To Log
- log_connections: Log successful connections to the server log
- log_disconnections: Log some information each time a session disconnects, including the duration of the session
- log_temp_files: Log temporary files of this size or larger, in kilobytes
- log_checkpoints: Causes checkpoints and restart points to be logged in the server log
- log_lock_waits: Log information if a session is waits longer than deadlock_timeout to acquire a lock
- log_error_verbosity: How detailed the logged message is. Can be set to default, terse or verbose
- log_line_prefix: Additional details to log with each line. Default is '%m [%p] ‘ which logs a timestamp and the process ID
- log_statement: Legal values are none, ddl, mod (DDL and all other data-modifying statements), or all
Ref:
- How to Get the Best Out of PostgreSQL Logs | EDB (enterprisedb.com)
- PostgreSQL Logs: How to Enable Logging [Config Tutorial] (sematext.com)
- Mastering PostgreSQL Log Management - Mydbops | Blog
- PostgreSQL Logging: Everything You Need to Know | Better Stack Community
- Syslog logging with journald in PostgreSQL (cybertec-postgresql.com)