Reporting and Logging / Where to Log

event_source

• What it does: Sets the application name used to identify PostgreSQL messages in the Windows Event Log when using the eventlog destination.
• Why it matters: On Windows systems, this parameter helps categorize and identify PostgreSQL log messages within the Windows Event Log system. Proper naming makes it easier to filter, search, and manage PostgreSQL events alongside other system events, and is essential for integration with Windows-based monitoring and alerting tools.
• Ideal value & Best Practice: Set to a descriptive name like 'PostgreSQL' or include the instance name such as 'PostgreSQL-15'. Use consistent naming across your Windows environments for easier log management and monitoring.

log_destination

• What it does: Specifies where PostgreSQL sends its log output, supporting multiple simultaneous destinations including stderr, syslog, csvlog, jsonlog, and eventlog (Windows).
• Why it matters: This fundamental parameter determines how you access and process your database logs. Different destinations serve different purposes: stderr for direct capture, syslog for system integration, csvlog for programmatic processing, and jsonlog for structured logging. Choosing the right combination affects log management, monitoring integration, and troubleshooting capabilities.
• Ideal value & Best Practice: For most Linux systems: 'stderr,csvlog' provides both human-readable and machine-processable logs. For Windows: 'eventlog,csvlog'. Include jsonlog if you use JSON-based log processing tools. Avoid using only stderr without the logging collector.

log_directory

• What it does: Sets the directory where log files are stored when using the logging collector.
• Why it matters: Proper log directory configuration ensures that logs are stored in a dedicated, secure location with sufficient storage space. The location affects log retention, backup strategies, and security compliance. Using a separate directory from the data directory also helps with performance and maintenance.
• Ideal value & Best Practice: Set to an absolute path like /var/log/postgresql or /pglogs. Ensure the directory exists and the PostgreSQL user has write permissions. Use dedicated storage with sufficient capacity for your log retention needs.

log_file_mode

• What it does: Sets the filesystem permissions for log files, controlling who can read and access database logs.
• Why it matters: Log files often contain sensitive information including query patterns, connection details, and potentially sensitive data. Proper permission settings are crucial for security compliance and preventing unauthorized access to database activity information.
• Ideal value & Best Practice: Default 0600 (read/write for owner only) is recommended for security. Use 0640 if you need to allow group read access for monitoring tools. Never use permissive settings like 0666 in production environments.

log_filename

• What it does: Sets the naming pattern for log files, supporting strftime-style formatting for including timestamps.
• Why it matters: The filename pattern affects log organization, rotation, and archival. Including timestamps in filenames makes it easier to manage log files, identify when logs were created, and automate log rotation and compression.
• Ideal value & Best Practice: Use 'postgresql-%Y-%m-%d_%H%M%S.log' for detailed timestamps or 'postgresql-%a.log' for daily rotation. Include enough timestamp information to avoid filename collisions while keeping names manageable.

log_rotation_age

• What it does: Sets the maximum time before log files are rotated, based on time elapsed rather than file size.
• Why it matters: Time-based rotation ensures regular log file management regardless of activity volume. This is particularly important for systems with variable workloads where size-based rotation might not occur frequently enough.
• Ideal value & Best Practice: Default 1d (daily rotation) is standard. For very busy systems, consider 12h or 6h. For troubleshooting periods, temporary smaller values like 1h can help isolate issues to specific timeframes.

log_rotation_size

• What it does: Sets the maximum size a log file can reach before being automatically rotated.
• Why it matters: Size-based rotation prevents individual log files from becoming too large and unmanageable. This is crucial for maintaining performance (large files are harder to process) and ensuring that log files can be easily transferred, compressed, or analyzed.
• Ideal value & Best Practice: Default 10MB is often too small for production. Set to 100MB or 500MB depending on your log volume. Balance between file manageability and having too many small files.

log_truncate_on_rotation

• What it does: Controls whether to overwrite existing log files with the same name during rotation, or append to them.
• Why it matters: This parameter manages log file lifecycle and prevents unlimited disk space consumption. Truncation allows for a fixed set of log files, while appending preserves historical data but requires manual cleanup.
• Ideal value & Best Practice: Default off. Set to on for fixed-size log rotation cycles. When using timestamped filenames, off is usually better as filenames won't conflict. For fixed names, on prevents unlimited growth.

logging_collector

• What it does: Enables or disables the background process that captures PostgreSQL's stderr output and redirects it to log files.
• Why it matters: This is essential for reliable log capture in production environments. Without the logging collector, stderr output might be lost depending on how PostgreSQL is started. The collector ensures consistent log management and rotation.
• Ideal value & Best Practice: Default off on some systems. Set to on for all production deployments. This ensures reliable log capture regardless of how PostgreSQL is started or restarted.

syslog_facility

• What it does: Sets the syslog facility code used when sending logs to syslog, categorizing messages within the syslog system.
• Why it matters: The facility code determines how syslog handles and routes PostgreSQL messages. Different facilities have different default behaviors in syslog configurations, affecting where messages are stored and how they're processed.
• Ideal value & Best Practice: Default local0. Use local0 through local7 based on your organization's syslog conventions. Coordinate with your system administrators to ensure proper integration with existing log management systems.

syslog_ident

• What it does: Sets the program name used to identify PostgreSQL messages in syslog.
• Why it matters: The identifier helps distinguish PostgreSQL messages from other system logs in syslog. A clear identifier makes it easier to filter, search, and process database logs within centralized logging systems.
• Ideal value & Best Practice: Set to 'postgres' or include the instance name like 'postgres-main'. Use consistent naming across your environment for easier log management and monitoring.

syslog_sequence_numbers

• What it does: Adds sequence numbers to syslog messages to prevent duplicate message suppression by syslog daemons.
• Why it matters: Some syslog implementations suppress duplicate consecutive messages. Sequence numbers ensure every message is logged even if they're similar, which is crucial for debugging where similar error messages might occur in rapid succession.
• Ideal value & Best Practice: Default on is recommended. Disable only if you have specific reasons and understand your syslog's duplicate handling behavior. Keeping this on ensures no messages are lost to suppression.

syslog_split_messages

• What it does: Controls whether long messages are split into multiple syslog messages to fit within traditional syslog size limits.
• Why it matters: Traditional syslog has message size limitations (often 1024 bytes). This parameter ensures that long log messages, such as complex query plans or detailed error messages, are not truncated but rather split across multiple syslog entries.
• Ideal value & Best Practice: Default on is recommended. This ensures complete messages are preserved rather than truncated. Only disable if you have modern syslog implementations that support larger message sizes.

---

Try pghealth Free Today 🚀

Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial — no installation required.

👉 Start Free Trial