vCenter Server crashes with error: duplicate key value violates unique constraint "pk_vpx_guest

Products

VMware vCenter Server

Issue/Introduction

VPXD service would crash with ERROR: duplicate key value violates unique constraint "pk_vpx_disabled_methods"

Environment

VMware vCenter server 7.0.x

VMware vCenter server 8.0.x

Cause

A duplicate entry already exists for a virtual machine in "pk_vpx_disabled_methods" table in VCDB

/var/log/vmware/vpxd.log

[YYYY-MM-DDTHH:MM:SS] error vpxd[766242] [Originator@6876 sub=Default opID=lro-18-2d61b566] An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_disabled_methods"
--> DETAIL: Key (entity_mo_id_val, method_name, source_id_val)=(vm-xyz, vim.VirtualMachine.powerOn, vCLS) already exists.;
--> Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_DISABLED_METHODS (ENTITY_MO_ID_VAL,METHOD_NAME,SOURCE_ID_VAL,REASON_ID_VAL) VALUES (?,?,?,?)"
[YYYY-MM-DDTHH:MM:SS] info vpxd[766192] [Originator@6876 sub=IO.Http opID=#######] Terminating user agent request; (null), N7Vmacore17CanceledExceptionE(Operation was canceled)
--> [context]zKq7AVECAQAAAPONbgEfdnB4ZAAA

.

[YYYY-MM-DDTHH:MM:SS] error vpxd[766242] [Originator@6876 sub=Default opID=lro-18-2d61b566] [VdbStatement] value = "Unknown"
[YYYY-MM-DDTHH:MM:SS] error vpxd[766242] [Originator@6876 sub=Default opID=lro-18-2d61b566] [Vdb::IsRecoverableErrorCode] Unable to recover from 23505:1
[YYYY-MM-DDTHH:MM:SS] error vpxd[766242] [Originator@6876 sub=Default opID=lro-18-2d61b566] [VdbStatement] SQLError was thrown: "ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_disabled_methods"
--> DETAIL: Key (entity_mo_id_val, method_name, source_id_val)=(vm-xyz, vim.VirtualMachine.powerOn, vCLS) already exists.;
--> Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_DISABLED_METHODS (ENTITY_MO_ID_VAL,METHOD_NAME,SOURCE_ID_VAL,REASON_ID_VAL) VALUES (?,?,?,?)"
[YYYY-MM-DDTHH:MM:SS] panic vpxd[766242] [Originator@6876 sub=vpxCommon opID=########] Unrecoverable VmRootError: 00007f7ac0035b80 Backtrace:

Service-control failed. Error: Failed to start services in profile ALL. RC=1, stderr=Failed to start sps, vpxd services. Error: Operation timed out

Resolution

1) Before you begin, please keep a valid snapshot/ file based backup of the vCenter server from the vCenter server VAMI page.

2) Next, we logged in to the vPostgres database using the below command.

# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

3) Delete the duplicate entry from the respective table - VPX_DISABLED_METHODS

VCDB=# select * from VPX_DISABLED_METHODS;

 entity_mo_id_val |         method_name         |          source_id_val          |         reason_id_val         | expiration_date

------------------+-----------------------------+---------------------------------+-------------------------------+-----------------

 vm-##            | vim.VirtualMachine.relocate | NetBackup Lock by JobId 1320774 | Prevent vMotion during backup |

 vm-xyz           | vim.VirtualMachine.powerOn  | vCLS                            | Unknown                       |

(5 rows)

VCDB=# DELETE from VPX_DISABLED_METHODS WHERE entity_mo_id_val = 'vm-xyz';
DELETE 1

IMPORTANT NOTE : Please ensure you delete the corresponding vm entry, based on the error available from vpxd.log in your particular context.

4) On our next attempt to start all services on vCenter server.. all services now started successfully.

# service-control --start --all

Additional Information

It is recommended to ensure Cluster DRS is set to 'Manual' prior to starting vCenter server upgrade/ patching activity.

vCLS VMs should be excluded from backups.