topic Re: Compacting Postgresql database size in Alfresco Forum

Compacting Postgresql database size

villdre — Tue, 31 Jan 2017 05:42:56 GMT

Hi AllWe are running Alfresco CE 5.1 and Postgresql 9.4 on a separate server. When I take a dump of the database for backup, the SQL file generated is more than 20GB in size. This seems a bit large given the number of users and number of documents that we have (we do have a few tenants created).Any

Re: Compacting Postgresql database size

afaust — Sat, 18 Feb 2017 15:15:17 GMT

Depending on the features that you have enabled the database can grow quite a lot larger than you would assume from just the number of users / documents / tenants. Especially when Auditing is enabled and used by some customisations / addons will you see a significant growth of the database. Similarily, if you use workflows extensively they will also add to the size of the DB - and workflows that have been completed are not really apparent to the user but are still stored in the database.

Also, you should make sure to regularly perform maintenance tasks like cleaning up the Alfresco "trash can" of deleted nodes, checking and limiting the amount of versions being retained (I have seen customers with 300+ minor versions in single documents), running the Alfresco cleanup scripts for the alf_prop_* tables, removing historic workflow data etc.

Re: Compacting Postgresql database size

villdre — Wed, 22 Feb 2017 08:20:02 GMT

Thanks Axel. Are the cleanup scripts available in Community Edition 5.1? I couldn't find anything in the docs (though I found something in the docs for Alfresco One).

Re: Compacting Postgresql database size

afaust — Wed, 22 Feb 2017 08:31:55 GMT

Alfresco Community Edition 5.1 does contain the alf_prop_* cleanup job / scripts.

Re: Compacting Postgresql database size

villdre — Fri, 22 Sep 2017 11:31:52 GMT

I finally managed to shrink my database by nearly 100GB using the below steps:

1. Added the below line in alfresco-global.properties and restarted alfresco:

attributes.propcleaner.cronExpression=* 11 18 * * ? *

This runs the property cleaner job at 6.11pm. Can take upto a few hours to complete, depending on the size of the database.

2. Ran Vacuum DB on Postgres

a) sudo su postgres

b) /usr/bin/vacuumdb -a -z -f

Re: Compacting Postgresql database size

gomezma — Thu, 01 Mar 2018 10:00:14 GMT

Hi everybody,

I managed to shrink my database by nearly 40GB adding the line commented using pgAdminIII with Vacuum FULL/ANALYZE. But the database have 105GB yet.

I have Windows Server 2012 R2. Do you know if i can to do somthing else ?

Thank you very much,