Dear community,
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:
#!/bin/bash
PGPASSFILE=C:\Users\YOURUSER\AppData\Roaming\postgresql\pgpass.conf
echo $PGPASSFILE
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:
localhost:5432:*:postgres:YOURPASSWORD
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.
Majkee
