vCenter upgrade from 7.0 U3 to 8.0 U2d or 8.0 U3 fails with an error "Database in-place upgrade failed. Please see vcdb_inplace.err and vcdb_inplace.out for details."

Products

• VMware vCenter Server

Issue/Introduction

• vCenter upgrade fails in stage 2
• An error message is shown, saying:

Database in-place upgrade failed. Please see vcdb_inplace.err and vcdb_inplace.out for details.

• Failure message in vcdb_inplace.err is:

  1 [23505](1) ERROR: could not create unique index "pk_vpx_dual"
  DETAIL: Key (dummy)=() is duplicated.;
  Error while executing the query
  Error while executing ./Upgrade-v2017-to-v2018/postgresql/upgrade_PostgreSQL.sql, reason: Statement failure(rc=-1).

or 

 1 [23505](1) ERROR: could not create unique index "pk_vpx_lock"
DETAIL: Key (id)=(0) is duplicated.;
Error while executing the query

Environment

vCenter Server 7.0X

vCenter Server 8.0X

Cause

This issue occurs when the vCenter database contains a duplicate value, specifically when the table vc.vpx_dual or vc.vpx_lock has more than one row. This table is supposed to contain only one row, any more will violate its key constraint.

Resolution

To solve this issue, clean out the vCenter database table vc.vpx_dual  or vc.vpx_lock in the source appliance, and retry the upgrade. To achieve this, apply the steps below:

Note: please ensure that a fresh backup of the vCenter Server Appliance exists before attempting to make any changes to the vCenter database.

1. Revert to snapshot or backup taken in working state before the failed upgrade attempt
2. Stop the vCenter Server service:

   # service-control --stop vmware-vpxd

3. Connect to the vCenter database:

   # psql -d VCDB -U postgres

4. Check if there are more than 2 rows in vc.vpx_dual or vc.vpx_lock :

   SELECT * FROM VPX_DUAL;
   SELECT * FROM VPX_LOCK;

5. If the output of either of these commands contains more than one line, run the following queries for the respective table:
   

   DELETE FROM VPX_DUAL;
   INSERT INTO VPX_DUAL VALUES('X');
   or 
   DELETE FROM VPX_LOCK;
   INSERT INTO VPX_LOCK VALUES('0');

6. Exit the database CLI:

   \q

7. start the vCenter Server service:

   # service-control --start vmware-vpxd

8. Retry the Upgrade.