Hey,
I recognized that the postgresql data directory of our nextcloud app grew up to more than 30GB while the dump of this database is about 1.5GB. So the postgresql files of this database (only the one folder) is like 20x larger than the stored data.
I vacuuming deactivated in the standard installation of postgresql under UCS?
Andy
Did you check the “deleted files” - folder of all users? These folders can grow very fast and high, if no retention policy is created. If not defined, the objects in “deleted files” will be deleted, if space is needed
You mean the trashbin within nextcloud?
Even large amount of trashbin/ deleted files in nextcloud would not explain why the db directory of postgresql is 20x larger than an ASCII dump of the same database.
If a dataset becomes deleted, it only becomes marked as deleted, but it remains in the database.
Maybe this might help you:
Don’t forget a backup before handling the database.
I recognized that autovacuum is deactivated in the pgsql settings of univention. Is there a special reason for this?
I mean we are a small company with about 10 reguarly users of nextcloud. Can’t even estimate how postgresql will grow if we would be 100 or 1,000.
postgres vaccum should run as cronjob by default, see
(second table)
The cronjob is installed and running. I see some repeating vacuum log messages in the psql log. but none refers to the nextcloud database.
I mean if other users do have the same that the database files of nextcloud are 20 times the size the dump of the nextcloud db is, then I will not complain any longer. Somehow it does not feel correct.
Maybe it might be a good idea, to read and follow the headlines of the cron file.
I just foud it:
# Warning: This file is auto-generated and might be overwritten by
# univention-config-registry.
# Please edit the following file(s) instead:
# Warnung: Diese Datei wurde automatisch generiert und kann durch
# univention-config-registry ueberschrieben werden.
# Bitte bearbeiten Sie an Stelle dessen die folgende(n) Datei(en):
#
# /etc/univention/templates/files/etc/cron.d/postgresql
#
# Regular cron jobs for the postgresql package
#
# To ensure proper access rights, 'ident sameuser' access for localhost is
# required in /etc/postgresql/7.4/main/pg_hba.conf. This is now the default setting for
# the Debian configuration.
#
# If password access for "local" is turned on in /etc/postgresql/7.4/main/pg_hba.conf,
# you must create a file ~postgres/.pgpass containing a line specifying the
# password, as explained in section 1.11 of the PostgreSQL Programmer's Guide
# (package postgresql-doc).
#
# If autovacuum is turned on in /etc/postgresql/7.4/main/postmaster.conf, you need
# to give the -F option to do.maintenance for it to do anything.
/etc/cron.d/postgresql
I mean the cron file is there and it’s the unmodified original file which should force a vacuum at least each sunday.
Will have a look into the NC db first. Maybe there is an override set if the vacuum to prevent vacuuming.
The cron script is outdated and not working. The autovacuum is deactivated by default in univention (so far on my server). I activated it and ran a full maintenance vacuum running. Now the file space is like 4GB. I will look if the autovacuuming is having any effect.
Strange - on my UCS server (installed since 4.4.4) the vacuum-task is running every Sunday at 3.10 a.m.
Where exactly is your Postgres database located?
The thing is that the script is calling /usr/lib/postgresql/bin/do.maintenance which is not installed since some versions of postgresql. This has also been mentioned here.
# Warning: This file is auto-generated and might be overwritten by
# univention-config-registry.
# Please edit the following file(s) instead:
# Warnung: Diese Datei wurde automatisch generiert und kann durch
# univention-config-registry ueberschrieben werden.
# Bitte bearbeiten Sie an Stelle dessen die folgende(n) Datei(en):
#
# /etc/univention/templates/files/etc/cron.d/postgresql
#
# Regular cron jobs for the postgresql package
#
# To ensure proper access rights, 'ident sameuser' access for localhost is
# required in /etc/postgresql/7.4/main/pg_hba.conf. This is now the default setting for
# the Debian configuration.
#
# If password access for "local" is turned on in /etc/postgresql/7.4/main/pg_hba.conf,
# you must create a file ~postgres/.pgpass containing a line specifying the
# password, as explained in section 1.11 of the PostgreSQL Programmer's Guide
# (package postgresql-doc).
#
# If autovacuum is turned on in /etc/postgresql/7.4/main/postmaster.conf, you need
# to give the -F option to do.maintenance for it to do anything.
PATH=/usr/sbin:/usr/bin:/sbin:/bin
# Run VACUUM ANALYSE on all databases every 5 hours
2 0,5,10,15,20 * * 1-6 postgres if ! pidof /usr/lib/postgresql/bin/pg_autovacuum > /dev/null && [ -x /usr/lib/postgresql/bin/do.maintenance ]; then /usr/lib/postgresql/bin/do.maintenance -a -F; fi
# On Sunday run a VACUUM FULL ANALYSE as well
# If you run a 24/7 site, you may want to comment out this line and save VACUUM
# FULL for when you think you really need it
10 3 * * sun postgres /usr/bin/test -x /usr/lib/postgresql/bin/do.maintenance && /usr/lib/postgresql/bin/do.maintenance -a -f -F
so the cronjob can not be successful.
You are right!
It seems, that Univention missed to adapt the maintenane script:
There is mentioned
/etc/postgresql/7.4/main/postmaster.conf
but the postmaster.conf is in
/etc/postgresql/9.6....
The folder and file in
usr/lib/postgresql/bin/pg_autovacuum
does not exist - the bin files are in
/usr/lib/postgresql/9.6/bin/
But how is the vacuum on your system called then? Did you do manual tweaks?
No, I didn’t customize anything concerning Postgres. My database in /var/lib/postgresql/9.6/ (I hope, that I am right with that folder) has a size of approx. 170 MBytes
I use Nextcloud with two users and Kopano on my UCS. We add and move approx. 20 Files per day each in Nextcloud.
My databases are located in /var/lib/postgresql/9.6/main/base