Postgresql – In Postgres, set log_rotation_size but the logs are still not rotating

disk-spacelogpostgresqlsize;

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?

Best Answer

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.

Related Solutions

Postgresql – Postgres log files not rotating

Try the following log parameters. After modify them, remember to reload the configuration file ($PGDATA/postgresql.conf).

# These are only used if logging_collector is on:
log_directory = '/var/applog/pg_log/1921'               # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600                    # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 10MB               # Automatic rotation of logfiles will

Postgresql – could not open log file “/var/log/postgresql-2015-05-24_181212.log”: Permission denied

I know that only root can write to /var/log ...

You can change that. As root user (or with sudo) in the shell:

mkdir /var/log/postgresql
chmod 0700 /var/log/postgresql
chown postgres:postgres /var/log/postgresql

And in postgresql.conf:

log_destination = 'stderr'      
logging_collector = on      
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'     
log_rotation_age = '1d'          # That's what makes it "dynamic"

Depesz has a detailed article on the topic:
https://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/

Best Answer

Related Solutions

Postgresql – Postgres log files not rotating

Postgresql – could not open log file “/var/log/postgresql-2015-05-24_181212.log”: Permission denied

Related Question