Postgresql – How to limit the size of the Postgres log

disk-spacelogpostgresqlsize;

I’m using Postgres 9.5 on Ubuntu 14.04. I notice I’m generating a lot of Postgres logs in

/var/log/postgresql/postgresql-9.5-main.log

. I would like to limit the size of this file, and doing some time based rotation (e.g. daily) is not an option. Is there any way I can tell Postgres to rotate its logs once they reach a certain size?

Best Answer

The PostgreSQL Error reporting and logging documentation states:

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.

So, I guess you should set this parameter to the desired value. My postgresql.conf option has the default values:

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

Related Solutions

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/

PostgreSQL – Controlling Log Generation

Reduce your log levels; for example, turn off log_transaction and set log_statement to something above all. Use pg_stat_statements instead of relying on full statement logging. Use Munin/Cacti/collectd/similar to collect system stats rather than relying on tools like pgbadger for log parsing.

Use logrotate if you're on a Linux system and using a system PostgreSQL install. It knows how to compress then age out logs. See /etc/logrotate.d/ .

PostgreSQL's built-in log rotation, as you have observed, is somewhat limited and really expects you'll be archiving and deleting them via a cronjob or similar. But you can do just that and use log_rotation_size.

You can also log to syslog. This gives you severity filtering, etc with a decent syslog like rsyslogd. You can make it keep ERROR level logs for ages, but quickly discard INFO and older, for example, by working alongside logrotate. You can even log to a remote syslog server with abundant disk space if you want to do long term archival.

There are commercial log ingestion products you can use to feed your logs in for archival and analysis too.

Best Answer

Related Solutions

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

PostgreSQL – Controlling Log Generation

Related Question