Very very very often vCenter 6.5 will greet me with the helpful message "Could not connect to one or more vCenter Server systems". It turns out this is a bug in vCenter 6.5.
To recover vCenter, I had to follow these simple few steps.
1. Secure shell into the vCenter appliance and open the vpxd log file.
/var/log/vmware/vpxd/vpxd.log
2. In it, find error messages at the end that look like this.
error vpxd[7F0A533E7700] [Originator@6876 sub=vpxCommon opID=HostSync-host-19-795056c4] [Vpxd_HandleVmRootError] Received unrecoverable VmRootError. Generating minidump ...
error vpxd[7F0A533E7700] [Originator@6876 sub=Default opID=HostSync-host-19-795056c4] 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_vm_virtual_device";
--> Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_VM_VIRTUAL_DEVICE (ID, DEVICE_KEY, CONN_AL_GU_CONTROL_FLAG, CONN_CONNECTED_FLAG, CONN_START_CONNECTED_FLAG, CONN_STATUS, DEVICE_INFO_SUMMARY, DEVICE_INFO_LABEL, DEVICE_CONTROLLER_KEY, DEVICE_UNIT_NUMBER, DEVICE_TYPE, DEVICE_TEXT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
3. Open the postgresql log file (use ls -trl to find the current one, check the last two or three).
/var/log/vmware/vpostgres
4. Find the corresponding (to the above errors) entry. It looks like this.
VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_vm_virtual_device"
VCDB vc DETAIL: Key (id, device_key)=(101, 3002) already exists.
VCDB vc STATEMENT: INSERT INTO VPX_VM_VIRTUAL_DEVICE (ID, DEVICE_KEY, CONN_AL_GU_CONTROL_FLAG, CONN_CONNECTED_FLAG, CONN_START_CONNECTED_FLAG, CONN_STATUS, DEVICE_INFO_SUMMARY, DEVICE_INFO_LABEL, DEVICE_CONTROLLER_KEY, DEVICE_UNIT_NUMBER, DEVICE_TYPE, DEVICE_TEXT) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
5. Make a note of the id and device_key (here 101 and 3002).
6. Start the psql client and connect as the postgres user. (Apparently no password is needed.)
/opt/vmware/vpostgres/current/bin/psql -U postgres
7. Switch to the VMware database. Note the case sensitivity.
\c VCDB
You can also show the tables in the database using
\dt
8. Show the offending device entry. Use the ID and DEVICE_KEY found above.
SELECT * FROM vpx_vm_virtual_device WHERE ID=101 AND DEVICE_KEY=3002;
9. If the device looks like it could be the offender (perhaps it is an externally connected USB device), go ahead and delete it (but don't blame me if this goes horribly wrong).
DELETE FROM vpx_vm_virtual_device WHERE ID=101 AND DEVICE_KEY=3002;
10. Reboot the vCenter appliance.
Also see http://vman.ch/vcenter-vdbodbcerror-unique-constraint-pk_vpx_vm_virtual_device-fix/.