postgres database keeps growing

[cmcs]

Hi Folks - the Proxmox_ruledb on my cluster keeps growing, regardelss of what I do. I have turned off greylisting and I have changed statistics from 14 days to 7 days, and neither of these things did anything at all to reduce the size of the database. I have /var setup as its own partition in case that's causing problems.

SELECT pg_size_pretty( pg_total_relation_size('cstatistic') );
 pg_size_pretty
----------------
 555 MB
(1 row)

Proxmox_ruledb=>
SELECT pg_size_pretty( pg_total_relation_size('cgreylist') );
 pg_size_pretty
----------------
 703 MB
(1 row)

Proxmox_ruledb=>
SELECT pg_size_pretty( pg_total_relation_size('cmsreceivers') );
 pg_size_pretty
----------------
 120 MB
(1 row)

Proxmox_ruledb=>
SELECT pg_size_pretty( pg_total_relation_size('creceivers') );
 pg_size_pretty
----------------
 274 MB
(1 row)

Proxmox_ruledb=>
SELECT pg_size_pretty( pg_total_relation_size('cstatistic') );
 pg_size_pretty
----------------
 555 MB
(1 row)

Proxmox_ruledb=>
SELECT pg_size_pretty( pg_total_relation_size('cmailstore') );
 pg_size_pretty
----------------
 670 MB
(1 row)

 

[Stoiko Ivanov]

Hi Folks - the Proxmox_ruledb on my cluster keeps growing, regardelss of what I do. I have turned off greylisting and I have changed statistics from 14 days to 7 days, and neither of these things did anything at all to reduce the size of the database.

It's been a while since I dealt with the deep intricacies of postgresql and it's disk management - but AFAIR postgres does not 'return' space to the OS (it marks rows as free for later reusal) - if you really need to get the diskspace back - I think issuing a 'vacuum full' on the database might help (keep in mind that this will block access to the database and thus also block processing mail):

https://www.postgresql.org/docs/current/routine-vacuuming.html

I hope this helps!

 

[cmcs]

Hi! Thanks, I only just had time to circle back around to this. Lack of vacuum doesn't appear to be the problem here - the databases have data in them. For instance, the greylisting database is filled with data, and appears to have new entries from today in it (based on the mtime). Several of the statistic databases have info over a month old in them - is there a way to purge this info more frequently? Aside from dropping or flushing the greylist db is there a way to deal with that?

Thanks!

 

[Stoiko Ivanov]

Hi! Thanks, I only just had time to circle back around to this. Lack of vacuum doesn't appear to be the problem here - the databases have data in them. For instance, the greylisting database is filled with data, and appears to have new entries from today in it (based on the mtime). Several of the statistic databases have info over a month old in them - is there a way to purge this info more frequently? Aside from dropping or flushing the greylist db is there a way to deal with that?

I just checked here - and the greylist table at least gets cleaned out without any issue (after disabling greylisting and waiting a while for the entries to expire) - this also shows in pg_total_relation_size...

So - any modifications to the config?
Since this can happen with postfix during upgrades - are you and PMG indeed connecting to the same postgres instance? - is only one postgres server running?