Question: How do you configure log rotation in PostgreSQL?

Answer

PostgreSQL includes various logging options which are crucial for troubleshooting and monitoring. One important aspect of logging is log rotation, which helps manage log file sizes and prevent them from consuming excessive disk space. Here's how to configure log rotation directly through PostgreSQL:

Configuration Parameters

Key configuration parameters related to log rotation in PostgreSQL can be found in the postgresql.conf file:

• logging_collector: This must be enabled (on) for log rotation to work, as it sends logs to a specified log directory instead of standard output.
• log_directory: Specifies the directory where the log files will be written. The default is pg_log.
• log_filename: Defines the naming convention for log files. You can include timestamp patterns like %Y-%m-%d_%H-%M-%S to make each filename unique.
• log_rotation_age: Determines the maximum age of a logfile before rotation occurs (e.g., 1d for one day).
• log_rotation_size: Specifies the maximum size of a logfile before rotation occurs (e.g., 100MB).

Example Configuration

Here’s an example configuration that enables daily rotation or when the log file size exceeds 100MB:

# Enable logging collector
logging_collector = on

# Set the directory where log files are stored
log_directory = 'pg_logs'

# Define the log filename pattern
log_filename = 'postgresql-%Y-%m-%d_%H-%M-%S.log'

# Set log rotation age
log_rotation_age = 1d

# Set log rotation size
log_rotation_size = 100MB

Applying the Changes

After making changes to postgresql.conf, you need to reload the server configuration for the changes to take effect:

SELECT pg_reload_conf();

or you can use command line:

pg_ctl reload -D /path/to/data/directory

These settings will help manage the growth of log files by rotating them based on age or size, whichever condition is met first.

Additional Notes

If you’re managing a highly active database that generates logs rapidly, consider implementing external log management solutions or additional scripting for compression and archival of old log files.

Furthermore, the choice between rotating based on size or time (or both) depends heavily on your specific application and operational requirements. Adjust these settings as needed to ensure optimal performance and disk usage.