12.0 Cleaning Database and Generating Internal Statistics

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:

  1. Login to the Primary Server that has the database role.

  2. Initiate the VACUUM by setting the following variables:

    • 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>;

  3. 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:

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