vCenter upgrade fails with "Source vCenter Server schema validation found an issue"

Products

VMware vCenter Server

Issue/Introduction

/var/log/vmware/upgrade/CollectRequirements_com.vmware.vcdb_YYYY_MM_DD_HH_MM.log:

YYYY-MM-DDTHH:MM:SS INFO vcdb.const read_db_value: b'700'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const VCDB schema version is b'700'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const Validating source embedded schema...
YYYY-MM-DDTHH:MM:SS ERROR vcdb.const Error while validating source vCenter Server database: Error while executing ./Upgrade-v2017-to-v2018/postgresql/validate_PostgreSQL.sql, reason: Statement failure(rc=-1).

YYYY-MM-DDTHH:MM:SS ERROR vcdb.const Error while validating source vCenter Server database: RAISE EXCEPTION 'ERROR !%',err_message_full;

YYYY-MM-DDTHH:MM:SS ERROR vcdb.const Error while validating source vCenter Server database: 1 [P0001](1) ERROR: ERROR ! Missing indexes: VPX_VM_VIRTUAL_DEVICE.PK_VPX_VM_VIRTUAL_DEVICE; Missing constraints: VPX_VM_VIRTUAL_DEVICE.PK_VPX_VM_VIRTUAL_DEVICE;;

YYYY-MM-DDTHH:MM:SS ERROR vcdb.const Error while validating source vCenter Server database: Error while executing the query

YYYY-MM-DDTHH:MM:SS INFO vcdb.const Souce embedded schema validation completed.
YYYY-MM-DDTHH:MM:SS INFO vcdb.const read_db_value: b'6826'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const read_db_value: b'16873'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const read_db_value: b'9523'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const Core tables size: 6826 MB
YYYY-MM-DDTHH:MM:SS INFO vcdb.const SET tables size: 16873 MB
YYYY-MM-DDTHH:MM:SS INFO vcdb.const CET tables size: 9523 MB
YYYY-MM-DDTHH:MM:SS INFO vcdb.const read_db_value: b'8278'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const read_db_value: b'30001'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const read_db_value: b'13831'
YYYY-MM-DDTHH:MM:SS INFO vcdb.const Core tables total size: 8278 MB
YYYY-MM-DDTHH:MM:SS INFO vcdb.const SET tables total size: 30001 MB
YYYY-MM-DDTHH:MM:SS INFO vcdb.const CET tables total size: 13831 MB
YYYY-MM-DDTHH:MM:SS INFO vcdb.const Core tables export: 5 min
YYYY-MM-DDTHH:MM:SS INFO vcdb.const Core tables import: 33 min
YYYY-MM-DDTHH:MM:SS INFO vcdb.const SET tables export: 123 min
YYYY-MM-DDTHH:MM:SS INFO vcdb.const SET tables import: 223 min
YYYY-MM-DDTHH:MM:SS INFO vcdb.const CET tables export: 70 min
YYYY-MM-DDTHH:MM:SS INFO vcdb.const CET tables import: 126 min
YYYY-MM-DDTHH:MM:SS INFO extensions The component script returned '{'coreRequirement': {'requiredSrcDiskSpace': 0.7, 'requiredDstDiskSpace': {'/storage/db': 8.9}, 'exportEstimationTime': 5, 'importEstimationTime': 33}, 'dstPortSpecs': [], 'srcPorts': [], 'userOptionSpecs': [{'optionId': 'vcdb.migrateSet', 'answerSpecs': [{'answerId': 'all', 'optionRequirement': {'requiredSrcDiskSpace': 4.1, 'requiredDstDiskSpace': {'/storage/seat': 32.3}, 'exportEstimationTime': 123, 'importEstimationTime': 223}, 'requirementMismatchSpecs': []}, {'answerId': 'core_events_tasks', 'optionRequirement': {'requiredSrcDiskSpace': 1.9, 'requiredDstDiskSpace': {'/storage/seat': 14.9}, 'exportEstimationTime': 70, 'importEstimationTime': 126}, 'requirementMismatchSpecs': []}, {'answerId': 'core', 'optionRequirement': {'requiredSrcDiskSpace': 0.0, 'requiredDstDiskSpace': {}, 'exportEstimationTime': 0, 'importEstimationTime': 0}, 'requirementMismatchSpecs': []}]}], 'installArguments': {'db.type': 'embedded'}, 'extraArguments': {'src.db.type': 'embedded'}, 'requirementMismatchSpecs': [{'text': Source vCenter Server schema validation found an issue., 'description': None, 'resolution': Read Knowledge Base Article 2149901 and address the issues found., 'problemId': None, 'severity': 'ERROR'}]}'

/var/log/vmware/upgrade/vcdb_req.err:

1 [P0001](1) ERROR: ERROR ! Missing indexes: VPX_VM_VIRTUAL_DEVICE.PK_VPX_VM_VIRTUAL_DEVICE; Missing constraints: VPX_VM_VIRTUAL_DEVICE.PK_VPX_VM_VIRTUAL_DEVICE;;
Error while executing the query

Trying to recreate the tables INDEX as per the instructions from KB 396790 (legacy 2149901) will return error:

493382-CREATE UNIQUE INDEX IF NOT EXISTS pk_vpx_vm_virtual_device ON vc.vpx_vm_virtual_device USING btree (id, device_key);
493499:psql.bin:/var/tmp/lab_indexes.sql:2778: ERROR: could not create unique index "pk_vpx_vm_virtual_device"
493604-DETAIL: Key (id, device_key)=(VM_ID, DEVICE_ID) is duplicated.

Environment

vCenter Server Appliance 7.0

vCenter Server Appliance 8.0

Cause

This is due to missing Primary Key constraint related to the VPX_VM_VIRTUAL_DEVICE tables.

Resolution

Step 1: Ensure that you grant valid snapshot and/or backup of the vCenter environment in accordance with the guidelines outlined in the following Knowledge Base articles:

Step 2: Detect the VM that is causing the duplicated index.

Using this command, check in the column "device_text" for details about the VM name, you can identify it by the location of devices such as the disks of the VM.
- /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "select id,device_key,device_text from vpx_vm_virtual_device where id = VM_ID;" | less -S
Power off the VM and remove it from inventory.
Remember to take note of the VM details to to be possible import that VM back to the inventory later.

Step 3: Stop VPXD service with this command:

service-control --stop vmware-vpxd

Step 4: Then, adjust the database to have the index and constrain of table vpx_vm_virtual_device fixed.

Recreate the index:
- /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "CREATE UNIQUE INDEX IF NOT EXISTS pk_vpx_vm_virtual_device ON vc.vpx_vm_virtual_device USING btree (id, device_key);"
Create the missing constrain:
- /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "ALTER TABLE vpx_vm_virtual_device ADD CONSTRAINT pk_vpx_vm_virtual_device FOREIGN KEY (id) REFERENCES vpx_vm (id);"

Step 5: Start VPXD service with this command:

service-control --start vmware-vpxd

Step 6: Access vCenter and use the steps of KB 335224 to register the VM that was causing the duplicated INDEX.

Step 7: Try again the upgrade of the vCenter.