Schema Validation errors when upgrading vCenter Server with PostgreSQL

Products

• VMware vCenter Server

Issue/Introduction

• Upgrading vCenter Server with a PostgreSQL database fails with the error:
  
  Source vCenter Server schema validation found an issue. Resolution: Read the vcdb_req.err log file and address the issues found"
  
  
• In the VMware-VCS-logs-timestamp_number/vcsUpgrade/vcdb_req.err log file is empty.
  
  
• In the VMware-VCS-logs-timestamp_number/vcsUpgrade/UpgradeRunner.log file, you see entries similar to:
  
  ERROR vcdb.const Error while validating source vCenter Server database: Error while executing .\Upgrade-v2015-to-v2016\postgresql\validate_PostgreSQL.sql:298, reason: Cannot execute statement(rc=-1)
  
  
• In PostgreSQL logs, you see entries similar to:
  
  ERROR: ERROR ! Missing indexes: VPX_DBM_COUNTER_METADATA.PK_VPX_DBM_COUNTER_METADATA; Missing constraints:

Cause

This issue occurs due to the missing primary key constraint PK_VPX_DBM_COUNTER_METADATA on table VPX_DBM_COUNTER_METADATA.

Resolution

Caution: Back up the database prior to performing the steps in this article. For more information, see Back up and restore vCenter Server Appliance/vCenter Server 6.0 vPostgres database (2091961).

 

To resolve this issue, run the the 2147952_fix_dbm_missing_constraints.sql script:

1. Download the 2147952_fix_dbm_missing_constraints.sql file attached to this article.
2. Obtain the VCDB useranme and password from vcdb.properties. For more information, see Connecting to the embedded vPostgres Database in a Windows installed vCenter Server 6.0 (2108848).
3. Open a command prompt and navigate to C:\program files\vmware\vcenterserver\vpostgres\bin.
4. Run this command:
   
   psql.exe -U vc -d VCDB -f PATH_TO_SCRIPT
   
   For example:
   
   psql.exe -U vc -d VCDB -f C:\temp\2147952_fix_dbm_missing_constraints.sql
   
   Note: You'll be prompted for the VCDB username and password from step #2.

Additional Information

• Back up and restore vCenter Server Appliance/vCenter Server 6.0 vPostgres database
• Connecting to the embedded vPostgres Database in a Windows installed vCenter Server 6.0