Checking and Fixing VUMDB inconsistency issues after running vLCM reset script on 8.0U3 to 9.0.x

Products

VMware vSphere Kubernetes Service

Issue/Introduction

Signs of inconsistency with the VMware Update Manager Database (VUMDB) include the following:

You see the following messages in the logs (/var/log/vmware/vmware-updatemgr/vum-server/vmware-vum-server.log):

ERROR vmware-vum-server ##### [vc@#### sub="Default"] [VdbStatement] SQLError was thrown: "ODBC error: (42703) - ERROR: column "vm_placement_policy" of relation "system_vms_cluster_solution_spec_vm_placement_policies" does not exist;

ERROR vmware-vum-server ##### [vc@#### sub="Default"] [VdbStatement] Statement diagnostic data from driver is 42703:0:1:ERROR: column "vm_placement_policy" of relation "system_vms_cluster_solution_spec_vm_placement_policies" does not exist;

You see the following messages in the postgres log (/var/log/vmware/vpostgres/postgresql.log):

VCDB vumuser [local] ##### 43ERROR:  column "vm_placement_policy" of relation "system_vms_cluster_solution_spec_vm_placement_policies" does not exist at character 94

VCDB vumuser [local] ##### 44STATEMENT:  INSERT INTO SYSTEM_VMS_CLUSTER_SOLUTION_SPEC_VM_PLACEMENT_POLICIES (CLUSTER_ID, SOLUTION_ID, VM_PLACEMENT_POLICY) VALUES ($1,$2,$3)

The VCF Diagnostic Tool for vSphere (VDT) reports the following:

[FAIL]    VUM SCHEMA CHECK
VUM DB Schema fix is required before upgrading to 9.1. Missing required columns, primary keys, or foreign keys.

Environment

vCenter Server 8.0u3

vCenter Server 9.0

vSphere Kubernetes Service (VKS) Supervisor Cluster

Environments preparing to upgrade to vCenter Server 9.1 from vCenter Server 8.0u3 or vCenter Server 9.0

Cause

A reset of the VMware Update Manager Database results in the LCCM database losing the "vm_placement_policy" column and the following relations:

system_vms_cluster_solution_spec_vm_placement_policies
system_vms_cluster_solution_spec_vm_networks
system_vms_cluster_solution_spec_vm_datastores

This inconsistency in the LCCM database critically impacts LCCM operations.

The VMware Update manager database was reset with the following script:

# python /usr/lib/vmware-updatemgr/bin/updatemgr-utility.py reset-db

This issue has also been observed to affect vSphere Kubernetes Service (VKS) Supervisor Cluster and Foundation Load Balancer (FLBs) after an upgrade to vCenter 9.1.0/9.1.1.

Resolution

This will be resolved in the next release of vCenter 9.1.

Validation Script

Follow the below steps to validate that the LCCM database has been affected by this issue and needs to be corrected:

Download the attached VUM_DB_Inconsistency_Checker.sql script and upload it to your vCenter
SSH into the vCenter

Run the validation script with the below command:

psql -U postgres -d VCDB -f VUM_DB_Inconsistency_Checker.sql

If you are encountering this issue, the above script will return:

     lccm_schema_status
----------------------------
VUM DB SCHEMA FIX REQUIRED
(1 row)

Otherwise, the above checker script will return the below which indicates no action is needed:
```
     lccm_schema_status
----------------------------
VUM DB SCHEMA VALID
(1 row)
```
If the return is VUM DB SCHEMA FIX REQUIRED then continue to the next section to fix the inconsistency.

Fix DB Inconsistency Script

Download the attached VUM_DB_Inconsistency_Fixer.sql script and upload it to your vCenter
SSH into the vCenter

Run the fix script with the below command:

psql -U postgres -d VCDB -f VUM_DB_Inconsistency_Fixer.sql

The following output is expected:

SET
BEGIN
DO
ALTER TABLE
DO
COMMIT

Confirm that the issue is resolved by running the validation script again and confirming that it returns "VUM DB SCHEMA VALID":

psql -U postgres -d VCDB -f VUM_DB_Inconsistency_Checker.sql

     lccm_schema_status
----------------------------
VUM DB SCHEMA VALID
(1 row)

Additional Information

This KB is to inform and provide preventive steps to take regarding a potential issues post-upgrade of the vCenter to 9.1 when this vCenter has previously had its VMware Update Manager Database reset.

These steps must be run before upgrading to vCenter 9.1 or 9.1.1 from vCenter 8.0u3 or vCenter 9.0.x

This issue is related to the following KB articles:

Attachments

VUM_DB_Inconsistency_Fixer.sql get_app

VUM_DB_Inconsistency_Checker.sql get_app