LogRotate Policy For PostgreSQL

In my organisation, we have started building PostgreSQL Clusters with Patroni + Consul. In PostgreSQL we enable a few extensions like pg_stat_statements to ensure we don’t miss any performance impacting query.

But this generates too much log in active servers, leading to PostgreSQL log bloating. Thus, it becomes important to ensure log files do not consume beyond an agreed amount of disk space. For this reason, I implemented the following log rotation steps for Postgresql –

• Ensure to set proper log file name for PostgresSQL logging_collector.
• Add a logrotate policy on linux system for postgres logs directory.
• Add a cron job for running logrotate policy more frequently.

Setting PostgreSQL log_filename and log_directory.

In my environment, I prefer to generate a single log file per day. So with the below log_filename config for postgresql generates log file names like postgresql-Mon.log, postgresql-Tue.log etc.

I plan to keep 5 (+1) files for each day after log rotation.

Add LogRotate Policy for PostgreSQL

For this, I have added a file /etc/logrotate.d/postgresql with the following content. The same can be found on my Github Repo PostgreSQL-Learning/Setup-Configuration/etc_logrotate.d_postgresql.

Key points in the below file are –

• size 500M directive instructs to consider log file rotation when it size crosses 500 MB
• copytruncate directive lets us keep the file ownership & permissions for rotated file due to the way rotation is handled

Add a Cron Job

Since we aim is to rotate log files based on size, time based directive like daily is not helpful to us. So we need to ensure our logrotate policy is executed frequently. For this reason, I have added following cron job. The same code can be found on Github repo PostgreSQL-Learning/Setup-Configuration/etc_cron.d_logrotate_postgresql.conf.

In this configuration, we would be running the postgresql logrotate every 15 minutes to keep rotating log files that crossed 500 mb size.

Result

After implementing the above steps, we should see log files similar to below screenshot.

In simple terms, maximum 6 files of approx 500 mb per day. So total postgresql log directory size should remain approx 21 gb in size.

Above scripts can easily be tested by generating dummy log dump using script PostgreSQL-Learning/Setup-Configuration/generate_dummy_log.sh.

I hope this will be useful for Postgresql DBAs like me out there.