Enabling logging_collector

The logging_collector parameter enables PostgreSQL's log collection system, which redirects log output to files for better log management and troubleshooting. To enable this feature, you must modify the configuration and restart PostgreSQL.

⚙️ Step 1: Configure logging_collector in postgresql.conf

Depending on your PostgreSQL setup, you need to update the logging_collector configuration either:

• If you're using a standard PostgreSQL installation: Edit your postgresql.conf file and add or modify:

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

• If you're using Patroni: Run the following command to update the cluster config:

patronictl edit-config
Then update or add the following section under postgresql.parameters:

yaml
postgresql:
  parameters:
    logging_collector: on
    log_directory: log
    log_filename: postgresql-%Y-%m-%d_%H%M%S.log
    log_rotation_age: 1d
    log_rotation_size: 100MB

🔄 Step 2: Restart PostgreSQL

Restart the PostgreSQL service to apply the logging_collector change:

# For standalone PostgreSQL

sudo systemctl restart postgresql

# For Patroni-managed clusters

patronictl restart '<cluster_name>'

● After enabling, you can verify with:

SHOW logging_collector;

This should return on if properly configured.

📝 Recommended Additional Logging Settings

Once logging_collector is enabled, consider these additional parameters (can be reloaded without restart):

log_statement = 'ddl'           # Log all queries
log_min_duration_statement = 1000  # Log slow queries (>1 second)
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h queryid=%Q'  # Detailed log prefix
log_connections = on            # Log connection attempts
log_disconnections = on         # Log disconnections