PostgreSQL - limit the total log size

Question

I have a very simple requirement - PostgreSQL log directory should have fixed max size, e.g. max 10 GB for all PostgreSQL logs.

It can be achieved by creating fixed-size logs and using log rotation (e.g. rotate 10 log files, 1 GB per log file).

I tried to set this up using PostgreSQL logging configuration, but I didn't manage to achieve that. The problem is that "log_truncate_on_rotation" does not works for size-based rotation, only for time-based rotation.

So, my question is - can it be achieved using only PostgreSQL configuration or I have to use something else? If not, why PostgreSQL does not have that feature?

I have few options:

set max file size to 1 GB using "log_rotation_size" property and then set CRON job which will remove oldest files if file count is above 10
use logrotate utility, but I have to execute it more often than once per day

I believe you got it right - you need a job. and cron can run jobs minutely - not only daily (regarding your last expression) — Vao Tsun
– Vao Tsun, Commented Jan 26, 2018 at 8:53

Vao Tsun · Accepted Answer · 2018-01-26 09:01:40Z

3

Yes - you are right about no such feature - you need a cron

https://www.postgresql.org/docs/current/static/runtime-config-logging.html

log_truncate_on_rotation (boolean)

When logging_collector is enabled, this parameter will cause PostgreSQL to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rotation, not during server startup or size-based rotation.

you can set up even minutely job to check the amount of logs:

* * * * * rm $(ls -t /PGDATA/pg_log/* | tail -n 1)

answered Jan 26, 2018 at 9:01

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bitifet · Accepted Answer · 2018-01-26 12:18:15Z

Apart of what others said, I would suggest to not reinvent the wheel.

Logrotate is usually invoked by cron and, even it may not be good idea to run it every minute, if you have a postgres logs size issue, you can add an additional cron entry that executes it conditionally depending of the logs dir size or free space (as you prefer).

Caring about free space:

Data:

joanmi@alpha:~$ sudo df -h /var/log/postgresql
S. fitxers                    Mida En ús Lliure  %Ús Muntat a
/dev/mapper/kubuntu--vg-root  229G  131G    87G  61% /

Pick:

joanmi@alpha:~$ sudo df -h /var/log/postgresql | tail -n 1 | cut -d ' ' -f 5
131G

Caring about used space:

Note that it is more expensive calculation. It was fast enough for me because I have no big databases in this machine (it is just my workstation).

Data:

joanmi@alpha:~$ sudo du -sh /var/log/postgresql/
76K     /var/log/postgresql/

Pick:

joanmi@alpha:~$ sudo du -sh /var/log/postgresql/ | cut -f 1
76K

And finally...

Less user readable (but easier to comparer in script):

joanmi@alpha:~$ sudo df /var/log/postgresql | tail -n 1 | cut -d ' ' -f 5
137003128

or

joanmi@alpha:~$ sudo du -s /var/log/postgresql/ | cut -f 1
76

NOTE: I used sudo to avoid permission issues. In a root cron job it can be removed.

Example:

Regarding free space:

This outputs nothing (if you have at least 100k free):

[[ \
    $(df /var/log/postgresql | tail -n 1 | cut -d ' ' -f 5) \
    -gt 100 \
]] \
    || echo 'Logrotate should be performedd!!'

This outputs Logrotate should be performedd!! (except if you have a really big disk ;-)):

[[ \
    $(df /var/log/postgresql | tail -n 1 | cut -d ' ' -f 5) \
    -gt 100000000000000000000 \
]] \
    || echo 'Logrotate should be performedd!!'

Collectives™ on Stack Overflow

PostgreSQL - limit the total log size

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related