Database administrative tasks - PostgreSQL
The dbmaintenancePostgresql script is provided to manage the database for the server components and the Dynamic Workload Console, performing tasks such as granting permissions and reorganizing databases.
This script updates the database statistics (updateStat argument) and reduces index fragmentation by physically reordering pages and compacting space without fully rebuilding the index (reorg argument). These operations improve query performance and data read efficiency.
The script is located in the <inst_dir>/<component_name>/dbtools/postgresql/<OS_type>/scripts path.
To run the script, perform the following steps:
- Copy the postgresql folder to the database server.
- Check that the user who is going to run the procedure has
the appropriate rights:
- Database administrator permissions
- Full access to and control of the HCL Workload Automation or Dynamic Workload Console installation directory
- Run the script with the -help parameter to display a list of supported parameters.
Syntax and arguments for the dbmaintenancePostgres script
- Windows operating systems:
-
dbmaintenancePostgres.exe -db database [-u user] [-p password] [-a updateStat|reorg] - UNIX operating systems:
-
./dbmaintenancePostgres -db database [-u user] [-p password] [-a updateStat|reorg]
Supported parameters for the dbmaintenancePostgres script are as follows:
- -db database
- The database name. This parameter is required.
- -u user
- Name of the user accessing the database. This parameter is optional when you specify user name and password in the shell where you run the command.
- -p password
- Password of the user accessing the database. This parameter is optional when you specify user name and password in the shell where you run the command.
- -a action
- The action to be performed by the script. Supported values are updateStat and reorg. The default value is updateStat. If you choose the updateStat argument, the script updates the database statistics. If you choose the reorg, the script reduces index fragmentation by physically reordering pages and compacting space without fully rebuilding the index.