PostgreSQL will stop logging when logrotate rotate postgresql log file

Question

I have installed

PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit

And configured:

log_filename = 'postgresql.log'

But I would like to manage log rotations by logrotate instead of internal logrotation by PostgreSQL:

#vim /etc/logrotate.d/postgresql

/var/lib/pgsql/data/log/postgresql.log {
    daily
    rotate 7
    compress
    create 0664 postgres postgres
    missingok
    notifempty
    sharedscripts
}

Where logrotate works as expected but PostgreSQL will stop logging into the rotated /var/lib/pgsql/data/log/postgresql.log file. The only resolution is to systemctl restart postgresql which could be placed inside logrotate postrotate/endscript block but I can't do it this way if this is done on critical production system.

Do you have any advice how to properly logrotate PostgreSQL log file without need of restarting PostgreSQL?

Thanks!

UPDATE: During reloading of PostgreSQL it looks for changes. I found a workaround how to achieve that PostgreSQL will start to log into rotated postgresql.log file - I need to modify config file, for example comment out/modify log_filename then reload PostgreSQL and revert changes back and reload PostgreSQL again. Because PostgreSQL looks for changes in configuration file. But I would be glad if there is another better solution than touching configuration file.

Answer 1

You can try using the copytruncate directive of logrotate if you can accept the small risk of losing log data.

Answer 2

PostgreSQL has the old log file open and keeps writing to it. Renaming the file doesn't change that.

Try to add a postrotate command that calls

pg_ctl reload -D /path/to/datadir

or uses some other way to send a signal 1 to the postmaster.

Answer 3

This is my actual workaround using sed:

/var/lib/pgsql/data/log/postgresql*.log {
    daily
    rotate 7
    compress
    delaycompress
    create 0664 postgres postgres
    missingok
    notifempty
    sharedscripts
    prerotate
        sed -i "s/log_filename = 'postgresql.log'         # log file name pattern,/log_filename = 'postgresql_rotate.log'         # log file name pattern,/" /var/lib/pgsql/data/postgresql.conf
        systemctl reload postgresql
        sleep 1
    endscript
    postrotate
        sed -i "s/log_filename = 'postgresql_rotate.log'         # log file name pattern,/log_filename = 'postgresql.log'         # log file name pattern,/" /var/lib/pgsql/data/postgresql.conf
        systemctl reload postgresql
    endscript
}

Where line

log_filename = 'postgresql.log'         # log file name pattern,

Will be temporarly renamed to postgresql_rotate.log, reload PostgreSQL and after rename log file back to postgresql.log and then reload PostgreSQL again.

delaycompress is there because of postgresql_rotate.log which needs to be there to properly rotate remporary rotate log file. If we will just comment out log_filename PostgreSQL will create log file named by actual time and this can be never rotated because it changes name each rotation. So first 2 files will be kept uncompressed and all others compressed.

I would be glad if you could find more better solution.

Answer 4

I've struggled to find many references in the postgres docs, but you can call an admin function to rotate the logs:

select pg_rotate_logfile()

In more recent postgres the same code can also be triggered by putting a logrotate signal file inside the $PGDATA folder.

touch /var/lib/postgresql/13/main/logrotate

This allows for moving the log file and then triggering postgres to start writing to a new one afterwards:

/var/lib/pgsql/data/log/postgresql.log {
    daily
    missingok
    notifempty
    postrotate
        touch /var/lib/postgresql/13/main/logrotate
    endscript
}