'Database in-place upgrade failed' 'VPX_VM_DS_SPACE ADD CONSTRAINT FK_VPX_VM_ DS_SPACE1' error while upgrading/migrating vCenter Server 6.5

Products

• VMware vCenter Server

Issue/Introduction

• Upgrade or migration to vCenter Server 6.5 fails during firstboot with error 

Database in-place upgrade failed

• You see the following output in the vcdb_inplace.err log file:

Error while executing ./Upgrade-v2015-to-v2016/postgresql/upgrade_PostgreSQL.sql:1138, reason: Cannot execute statement(rc=-1).ALTER TABLE VPX_VM_DS_SPACE ADD CONSTRAINT FK_VPX_VM_DS_SPACE1 FOREIGN KEY(VM_ID) REFERENCES VPX_VM (ID) ON DELETE CASCADE;

Resolution

To resolve this issue follow the steps below:

1. Restore vCenter Server to a state before the upgrade or migration.
2. Restore the vCenter Server database from a backup.
3. Retrieve the vCenter Server database password. See Delete old tasks, events and statistics data in vCenter Server 5.x and 6.x (2110031) ​​​​​​​
   • Microsoft SQL Server Express
   1. Log in as an administrator to the vCenter Server machine.
   2. Open the command prompt and run the following command:
      sqlcmd -E -S IP-address-or-FQDN-of-the-database-machine\instance_name -d database-name
   • Microsoft SQL Server
   1. Log in as an administrator to the machine on which Microsoft SQL Server is installed.
   2. Open the command prompt and run the following command:
      sqlcmd -S IP-address-or-FQDN-of-the-database-machine\instance_name -U vCenter-Server-database-user -P password -d database-name
   • Oracle
   1. Log in as an administrator to the Oracle database machine.
   2. Log in to sqlplus with the vCenter Server database user name and password:
      sqlplus vCenter-Server-database-user/password
   • PostgreSQL
   1. Log in as an administrator to the vCenter Server machine
   2. Run the script by using psql and provide the database password when prompted:
      • For vCenter Server, open a command prompt and run the following command:

%VMWARE_CIS_HOME%\vPostgres\bin psql -U vCenter-Server-database-user -d database-name

• For vCenter Server Appliance, log in to the appliance shell and run the following command:

/opt/vmware/vpostgres/current/bin/psql -U vCenter-Server-database-user -d database-name

4. Log in to the vCenter Server database and run the following command:

DELETE FROM VPX_VM_CONFIG_INFO WHERE ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_VM_STATIC_OVERHEAD_MAP WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_VM_FILE_LAYOUT_EX WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_VM_DS_SPACE WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_VM_FLE_DISK_LAYOUT WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_COMPUTE_RESOURCE_DAS_VM WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_VM_FLE_FILE_INFO WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_VM_FLE_SNAPSHOT_LAYOUT WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_COMPUTE_RESOURCE_DRS_VM WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );
DELETE FROM VPX_GUEST_DISK WHERE VM_ID NOT IN (SELECT ID FROM VPX_VM );

5. Run the upgrade or migration again.