Can postgres internal log rotation mechanism delete log files older than a certain time?

Question

I am using posgtres' internal log rotation mechanism. I would like log files older than 30 days to be deleted. How do I get postgres to do this? If postgres does not support it, can I set logrotate to just delete logs older than 30 days?

My current config is:

log_destination = 'stderr'             
logging_collector = on
log_directory = 'pg_log'                
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' 
#log_truncate_on_rotation = off 
log_rotation_age = 1440         
log_rotation_size = 0    

Solution 1

I don't believe the Postgres logging mechanism supports this cleanups, but you are perfectly safe deleting old logs using logrotate or a cron job as long as you don't mess with the active logfile Postgres is writing to.

For Postgres logging I usually prefer to log to syslog and let the normal log rotation procedures do their thing. This has a few advantages, but the two big ones are the ability to use your standard log rotation procedures (like logrotate) the same way you do for other daemons and the ability to have your syslog daemon send the logs to a remote host, if that's something you want to do one day.

Other options are discussed (albeit briefly) in section 23.3 of the Postgres manual.

Solution 2

If you aren't using syslog then you could just run the following script from cron and call it a day.

#!/bin/sh

HOME=/var/lib/pgsql
export HOME
PGDATA=/var/lib/pgsql/9.1/data
export PGDATA

/usr/bin/find $PGDATA/pg_log -type f -ctime +30 -name "*.log" -exec /bin/rm {} \;

By not logging to syslog you can also use tools like pgAdmin or pgFouine to view/analyze the database logs.

Comments

[Freiheit]

pgFouine is what led me down this path. You can use pgFouine from a syslog log but its more work to get it set up right.