Compacting Postgresql database size

cancel
Showing results for 
Search instead for 
Did you mean: 
villdre
Active Member II

Compacting Postgresql database size

Jump to solution

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

1 Solution

Accepted Solutions
afaust
Master

Re: Compacting Postgresql database size

Jump to 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.

View solution in original post

5 Replies
afaust
Master

Re: Compacting Postgresql database size

Jump to 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
Active Member II

Re: Compacting Postgresql database size

Jump to solution

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
Master

Re: Compacting Postgresql database size

Jump to solution

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

villdre
Active Member II

Re: Compacting Postgresql database size

Jump to solution

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
Active Member

Re: Compacting Postgresql database size

Jump to solution

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,