In Postgres, set log_rotation_size but my logs are still not rotating

Question

I’m using Postgres 9.5 on Ubuntu 14.04. I wanted to rotate my logs whenever they reach a certain size. So I edited /etc/postgresql/9.5/main/postgresql.conf and set this

log_rotation_size = 50MB                # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

Then I restarted my server

sudo /etc/init.d/postgresql restart

However after running some operations, I ran out of disk space, and discovered my log had not rotated at all …

myuser@mymachine:~$ ls -al /var/log/postgresql/postgresql-9.5-main.log
-rw-r----- 1 postgres postgres 3165773943 Dec 29 18:34 /var/log/postgresql/postgresql-9.5-main.log

What else do I need to do to get the log rotation to kick in?

Answer 1

In Debian/Ubuntu, per policy it's the logrotate package that is in charge of handling log rotation and purge for all services, PostgreSQL included.

From https://www.debian.org/doc/debian-policy/ch-files.html :

Log files must be rotated occasionally so that they don't grow indefinitely. The best way to do this is to install a log rotation configuration file in the directory /etc/logrotate.d, normally named /etc/logrotate.d/package, and use the facilities provided by logrotate.

So there is a /etc/logrotate.d/postgresql-common configuration file that can be tuned to rotate log files when they grow over a size given by the size keyword , among a lot of other options.

On the other hand, should you want to use the PostgreSQL feature to achieve this, as opposed to the pre-configured method of the OS, you need to consider all logging parameters in postgresql.conf, not just log_rotation_size. First a Debian/Ubuntu packaged PostgreSQL will turn off logging_collector to not interfere with logrotate, but most of log_* options depend on it to be effective, as mentioned in the doc, for instance:

https://www.postgresql.org/docs/current/static/runtime-config-logging.html

log_rotation_size (integer)

When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files. This parameter can only be set in the postgresql.conf file or on the server command line.

Answer 2

In order to use Postgres internal logging collector the name of the log file must contain some %-escapes, for example

log_filename = 'postgres.%T.log'

It is written in the documentation in the somewhat not clear way:

If you specify a file name without escapes, you should plan to use a log rotation utility to avoid eventually filling the entire disk

Answer 3

I get same problem in PostgreSQL 9.6.2, and I find the root cause. 😂

$ ls -l /var/lib/pgsql/9.6/data/pg_log/
-rw------- 1 postgres postgres 2.4G Jun 13 10:21 postgresql-Wed.log

If we want to use the log_rotation_size args to limit log file size, we need modify the log_filename args too, else the logs always write to same file.

Working after modify the log_filename

1. Different the postgresql.conf.

   $ diff /var/lib/pgsql/9.6/data/pgsql_9.6/data/postgresql.conf.ori /var/lib/pgsql/9.6/data/pgsql_9.6/data/postgresql.conf
   345c345
   < log_filename = 'postgresql-%a.log'    # log file name pattern,
   ---
   > log_filename = 'postgresql-%a-%H%M.log'  # log file name pattern,
   357c357
   < log_rotation_age = 1d                 # Automatic rotation of logfiles will
   ---
   > log_rotation_age = 60                 # Automatic rotation of logfiles will
   359c359
   < log_rotation_size = 0                 # Automatic rotation of logfiles will
   ---
   > log_rotation_size = 1000000           # Automatic rotation of logfiles will
   

2. Rotate resule

   $ ls -l /var/lib/pgsql/9.6/data/pg_log/
   -rw------- 1 postgres postgres 977M Jun 13 10:04 postgresql-Wed-1001.log
   -rw------- 1 postgres postgres 977M Jun 13 10:07 postgresql-Wed-1004.log
   -rw------- 1 postgres postgres 611M Jun 13 10:18 postgresql-Wed-1007.log
   

By the way, I fixed this problem after I fllow the official document under log_truncate_on_rotation (boolean).

Example: To keep 24 hours of logs, one log file per hour, but also rotate sooner if the log file size exceeds 1GB, set log_filename to server_log.%H%M, log_truncate_on_rotation to on, log_rotation_age to 60, and log_rotation_size to 1000000. Including %M in log_filename allows any size-driven rotations that might occur to select a file name different from the hour's initial file name.

https://www.postgresql.org/docs/current/static/runtime-config-logging.html

Answer 4

log_rotation_size = 50MB here the log_rotation_size value as per postures should be an integer representing the number of KB's of data so you may try removing the MB out of 50 and adding three more zeros.