0

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.

2
  • postgresql.org/docs/current/static/…
    – user1822
    Nov 18, 2016 at 23:05
  • @a_horse_with_no_name: it seems log_rotation_size could do the trick, but only if the new file created has the same filename as the previous one, so that it overwrites the file. Nov 19, 2016 at 5:27

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.