Compacting Postgresql database size

[villdre]

Hi All

We 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 ideas where to start to check if this size can be reduced / optimized?

Thanks

Vipul

[afaust · Accepted solution]

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.

[villdre]

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).

[afaust]

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

[villdre]

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

[gomezma]

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,