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

log-fileslogginglogrotatepostgresql

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

Best Answer

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.

Related Solutions

Nginx – Proper way to rotate Nginx logs

While the world is divided on whether the humble named pipe is friend or foe, it is probably the simplest solution to your problem. It does have a few drawbacks (in that you need to create the pipes ahead of time), but it eliminates the need for a cron and allows you to used the logging pipe-filter of your choice.

Here's an example using cronolog on access.log:

Pick a path for our named pipe. I intend to keep my logs in /var/log/nginx, so I'll put my pipes there as well. The name is up to you; I append .fifo, and it's access.log, so mine will be at /var/log/nginx/access.log.fifo.
Delete the file if it exists.
Make a named pipe for the logfile:
```
mkfifo /var/log/nginx/access.log.fifo
```
Configure nginx.conf to point the log at the pipe you just made:
```
access_log /var/log/nginx/access.log.fifo;
```
Modify your init.d script to start the log rotator listening to the pipe before we start the server:
```
LOGS="/var/log/nginx"
pkill -f "/usr/sbin/cronolog --symlink $LOGS/access.log"
( cat $LOGS/access.log.fifo | /usr/sbin/cronolog --symlink $LOGS/access.log "$LOGS/%Y/%m/%d/access.log" ) &
```
A similar commandline would be used for rotatelogs should you prefer it to cronolog -- see their docs for the syntax.

If your distrobution has a start-stop-daemon, you should be using that instead, as it theoretically has whatever special knoweldge about your platform there is, and taking care of pkill for you. Simply wrap the command in a script, and pass it as --exec to start-stop-daemon in your init.d/nginx.

Why do JBoss and Logrotate create log files full of NUL characters

We had the same problem for a file being written by log4j. The solution was to set the property "Append" for the FileAppender to "true". After this change, we have not seen this problem with files having NUL when rotated by an external program like logrotate.

Best Answer

Related Solutions

Nginx – Proper way to rotate Nginx logs

Why do JBoss and Logrotate create log files full of NUL characters

Related Topic