No healthy upstream while accessing the vCenter - vpxd crashes with error duplicate key value violates unique constraint "pk_vpx_ip_address"

Products

• VMware vCenter Server
• VMware vCenter Server 8.0

Issue/Introduction

• Accessing vCenter Server via browser shows "no healthy upstream" error message.
• Verifying the service status shows, "vmware-vpxd" service in stopped state.
  
  root@<hostname> [ ~ ]# service-control --status
Running:
 applmgmt lookupsvc lwsmd observability observability-vapi pschealth vc-ws1a-broker vlcm vmafdd vmcad vmdird vmware-analytics vmware-certificateauthority vmware-certificatemanagement vmware-cis-license vre-content-library vmware-eam vmware-envoy vmware-envoy-hgw vmware-envoy-sidecar vmware-hvc vmware-infraprofile vmware-perfcharts vmware-pod vmware-postgres-archiver vmware-rhttpproxy vmware-sca vmware-svmware-stsd vmware-topologysvc vmware-trustmanagement vmware-updatemgr vmware-vapi-endpoint vmware-vdtc vmware-vmon vmware-vpostgres vmware-vpxd-svcs vmware-vsan-health vmware-vsm vsphere-ui ats vtsdb wcp
Stopped:
 vmcam vmonapi vmware-imagebuilder vmware-netdumper vmware-rbd-watchdog vmware-vcha vmware-vpxd
  
  
• Service 'vmware-vpxd' crashes immediately while trying to start with the command service-control --start vpxd
• VPXD service logs shows similar to below entries :
  
  /var/log/vmware/vpxd/vpxd.log

[YYYY-MM-DDTHH:MM:SS].238Z error vpxd[<id>] [Originator@xxxx sub=Default opID=HB-<host-moid>@xxxxxxx-xxxxxxxx] [VdbStatement] SQLError was thrown: "ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_ip_address"
--> DETAIL: Key (surr_key)=(<id>) already exists.;
--> Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_IP_ADDRESS (ENTITY_ID,DEVICE_ID,IP_ADDRESS) VALUES (?,?,?)"

Environment

vCenter Server 8.0 U2 and above versions.

Cause

• This issue occurs if EITHER the sequence of vpx_ip_address table is set to "vpx_host_scsi_disk_part_surr_key_seq", OR if it's set to "vpx_ip_address_surr_key_seq" but the "SELECT nextval('vpx_ip_address_surr_key_seq');" outputs a smaller value than "SELECT max(surr_key) FROM vpx_ip_address;"
  
  
  • Execute below Postgres DB query on vCenter SSH session to verify the sequence configured for column surr_key in vpx_ip_address table:
    
    /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres -c "\d+ vpx_ip_address"
    
    Example:
    
root@<hostname> [ ~ ]# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres -c "\d+ vpx_ip_address"

                                                                          Table "vc.vpx_ip_address"
   Column   |         Type          | Collation | Nullable |                          Default                          | Storage  |
------------+-----------------------+-----------+----------+-----------------------------------------------------------+----------+
 entity_id  | bigint                |           | not null |                                                           | plain    |
 device_id  | integer               |           | not null |                                                           | plain    |
 ip_address | character varying(50) |           | not null |                                                           | extended |
 surr_key   | bigint                |           | not null | nextval('vpx_host_scsi_disk_part_surr_key_seq'::regclass) | plain    |
    
    
  • Execute below Postgres DB query on vCenter SSH session to check the next value of sequence "vpx_ip_address_surr_key_seq" and the max value of column surr_key in vpx_ip_address table.
    
    /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres -c "SELECT nextval('vpx_ip_address_surr_key_seq')"

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres -c "SELECT max(surr_key) FROM vpx_ip_address"

Resolution

This issue is resolved in vCenter 9.0.

Workaround

Change the sequence name configured for vpx_ip_address table by executing below DB queries.

1. SSH to vCenter Server as root account
2. Set the next value for sequence 'vpx_ip_address_surr_key_seq' by executing below query:
   
   /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres -c "select setval('vpx_ip_address_surr_key_seq', greatest(nextval('vpx_ip_address_surr_key_seq'), nextval('vpx_host_scsi_disk_part_surr_key_seq')) + 1000, true)"
   
   
3. Change the sequence name configured for surr_key column to the right sequence 'vpx_ip_address_surr_key_seq' by executing below query.
   
   /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres -c "alter table vc.vpx_ip_address alter column surr_key set default nextval('vc.vpx_ip_address_surr_key_seq')"
   
   
4. Start the VPXD service
   
   service-control --start vpxd

Additional Information

For assistance contact Broadcom Support in case facing difficulty in executing the resolution steps.