PostgreSQL required a strong maintenance mechanism to deal with these DEAD tuples and statistics. VACUUM is the maintenance process which takes care of DEAD tuples along with it analyzes the contents of a tables and collects statistics about the distribution of values in each column of every table.
Perform the following steps to clean the PostgreSQL database:
Login to the Primary Server that has the database role.
Initiate the VACUUM by setting the following variables:
PGPORT: <existing source DB port configured>
By default, ZENworks uses 54327
PGUSER: <existing super user for ZENworks postgres DB> The default user for ZENworks is zenpostgres.
PGDATABASE: <existing database name> ZENworks default is postgres
PGPASSWORD: <password for the above super user PGUSER>
Example for Windows Primary Server: Open the command prompt and set the following variables based on the existing configuration.
SET PGDATA= %ZENSERVER_HOME%\database\pgsql\data
SET PGUSER=<super user retrieved from zman dgcs>
SET PGPASSWORD=<password retrieved from zman dgcs>;
Run the following commands to VACUUM on all databases:
vacuumdb.exe --analyze --full --skip-locked --verbose --all
NOTE:Micro Focus recommends to run these steps every week during the low activity on the database (non-business hours).
For more information, see https://www.postgresql.org/docs/current/app-vacuumdb.html