unfortunately Agilent does not provide official tutorial for automatic maintenance PostgreSQL database as a default database engine for OpenLAB ECM XT (or Content Management). I would like to share unofficial recommendation how we can automatically run Vacuum and Reindex databases on weekly base allows us to get faster performance of client-server system in time.
PostgreSQL installation on Windows environment has default installation path: C:\Program Files (x86)\PostgreSQL-9.3\bin\ folder. We can find here vacuumdb.exe and reindexdb.exe scripts which provide functionality we needs.
You can create, for example maintenance_script.bat file to this folder and specify which databases should be maintained and how. This file can contains:
vacuumdb --analyze -d OLSharedServices -U postgres
reindexdb -d OLSharedServices -U postgres
vacuumdb --analyze -d DataStore -U postgres
reindexdb -d DataStore -U postgres
Next step is to modify external file pgpass.conf where you specify password to your databases, default path: C:\Users\YOURUSER\AppData\Roaming\postgresqlpgpass.conf. Add this line:
Now, you can create basic task in Windows Task Scheduler and set period:
This should be normally covered by customer's IT, but sometimes in real world there is no other way and you should do it yourself in laboratory. Hope it helps someone.