PostgreSQL – Controlling Log Generation

logspostgresql

My disk space fills up daily with postgresql logs. One optimzation solution I'm pursuing is that instead of turning off logs, I should overwrite the original log file (i.e. rotate?) once it hits a certain size (say 200MB).

How do I enable such a setting in postgresql.conf? I'm aware that a better solution would be to backup all logs, but for now this is the one I want to pursue.

Best Answer

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.

Related Question