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:
NOTE:This step is required for Windows only.
PGPORT: <existing source DB port configured>
By default, ZENworks uses 54327
PGDATA: <embedded_database_location>
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 PGPORT=54327
SET PGDATA= %ZENSERVER_HOME%\database\pgsql\data
SET PGUSER=<super user retrieved from zman dgcs>
SET PGDATABASE=postgres
SET PGPASSWORD=<password retrieved from zman dgcs>;
Example for Linux Primary Server: Open the terminal and set the following variables based on the existing configuration.
export LD_LIBRARY_PATH=/opt/microfocus/zenworks/share/pgsql/lib;
export PATH=$PATH:/opt/microfocus/zenworks/share/pgsql/bin;
export PGPORT=54327;
export PGDATA=/var/opt/microfocus/pgsql/data;
export PGUSER=<super user retrieved from zman dgcs>;
export PGDATABASE=postgres;
export PGPASSWORD=<password retrieved from zman dgcs>;
Run the following commands to VACUUM on all databases:
On Windows:
cd "%ZENSERVER_HOME%\share\postgres\bin\"
vacuumdb.exe --analyze --full --skip-locked --verbose --all
On Linux:
cd /opt/microfocus/zenworks/share/pgsql/bin/
docker exec -it -e PGUSER=<super user retrieved from zman dgcs> -e PGDATABASE=postgres -e PGPASSWORD=<password retrieved from zman dgcs> zenpostgres vacuumdb --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