CODEWITHSUNDEEP
postgresqllogging
elBastarde
elBastarde

Reputation: 315

PostgreSQL - limit the total log size

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:

Upvotes: 7

Views: 11483

Answers (2)

bitifet
bitifet

Reputation: 3669

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!!'

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51496

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)

Upvotes: 3

Related Questions