0

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.

1

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

| improve this answer | |
0

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.

| improve this answer | |
  • Hi Laurenz, thank you for reply. I tried to add mentioned command into postrotate section but PostgreSQL behaves same way - it won't start logging into new file. I tried it even from database by running SELECT pg_reload_conf(); or systemctl reload postgresql but still no luck. – MyKE Oct 24 '19 at 7:24
  • I verified now: Message during reloading will appear: pid=[7211]: session=[8-1] user=,db=,app=,client= LOG: received SIGHUP, reloading configuration files and will be written into actual log but future events are still written into older one if was rotated. So it's still wiring into old file even it was postroated. – MyKE Oct 24 '19 at 7:29
  • Hm, yes, I just read the code, and the file is only rotated if the parameters log_directory or log_filename change. So you cannot use logrotate that way unless you are willing to restart PostgreSQL. Ise the built-in method. – Laurenz Albe Oct 24 '19 at 8:43
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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