The error message is indicating that a duplicate key value is violating a unique constraint on the VPX_ALARM_RUNTIME table.
Error message in /var/log/vmware/vpxd/vpxd.log
[YYYY-MM-DDTHH:MM: SS] error vpxd[34913] [Originator@6876 sub=Vmomi opID=HostSync-host-XXX-658d8c13] GetVpxaInfo failed for host esx.local
[YYYY-MM-DDTHH:MM:SS] error vpxd[34913] [Originator@6876 sub=Vmomi opID=HostSync-host-XXX-658d8c13] Cannot contact the specified host (esx.local)
[YYYY-MM-DDTHH:MM: SS] error vpxd[34875] [Originator@6876 sub=Vmomi opID=lro-545-6212040e] Could not get compatible VIM version for host esx.local
[YYYY-MM-DDTHH:MM: SS] error vpxd[34875] [Originator@6876 sub=Vmomi opID=lro-545-6212040e] Cannot contact the specified host (esx.local)
[YYYY-MM-DDTHH:MM:SS] error vpxd[34875] [Originator@6876 sub=MoHost opID=lro-545-6212040e] Could not read 'config' from hostd for vim.HostSystem:host-XXX:
N5Vmomi5Fault17HostCommunication9ExceptionE(Fault cause: vmodl. fault.HostCommunication
-- > )
-- >
[context]zKq7AVECAAAAACrfOAEYdnB4ZAAAEFcrbGlidm1hY29yZS5zbwAAJEUbAFqxGAE2HFR2cHhkAAHeAFsBfQxgARwRYAFDtV8BI/tfAi+PEmxpYnZtb21pLnNvAINdbwgBbGlidmltLXR5cGVzLnNvAAGvH8IBzyLCAXYg
[/context]
[YYYY-MM-DDTHH:MM: SS] warning vpxd[34913] [Originator@6876 sub=MoHost opID=HostSync-host-XXX-658d8c13] host [vim.HostSystem:host-XXX, esx.local] connection state changed to
NO_RESPONSE
[YYYY-MM-DDTHH:MM: SS] warning vpxd[34913] [Originator@6876 sub=VpxProfiler opID=HostSync-host-XXX-658d8c13] InvtHostSyncLRO :: StartWork [HostSyncTime] took 3021 ms
[YYYY-MM-DDTHH:MM:SS] error vpxd[34883] [Originator@6876 sub=Default] [VdbStatement] Execute result code: -1
[YYYY-MM-DDTHH:MM: SS] error vpxd[34883] [Originator@6876 sub=Default] [VdbStatement] SQL execution failed: UPDATE VPX_ALARM_RUNTIME SET ENTITY_TYPE = ? , STATE_VALUE = ? ,
METRIC_VALUE = ? , CREATED_TIME = ? , STATUS_VALUE = ? WHERE ENTITY_ID = ? AND ALARM_ID = ? AND EXPRESSION_NAME = ?
[YYYY-MM-DDTHH:MM: SS] error vpxd[34883] [Originator@6876 sub=Default] [VdbStatement] Execution elapsed time: 1 ms
[YYYY-MM-DDTHH:MM:SS] error vpxd[34883] [Originator@6876 sub=Default] [VdbStatement] Statement diagnostic data from driver is 23505:0:1: ERROR: duplicate key value violates
raint "pk_vpx_alarm_runtime";
unique constraint
/var/log/vmware/vpostgres/postgres.log
[YYYY-MM-DDTHH:MM:SS] 66ffa54f.85d5 1245906874 VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_alarm_runtime"
[YYYY-MM-DDTHH:MM:SS] 66ffa54f.85d5 1245906874 VCDB vc DETAIL: Key (entity_id, alarm_id, expression_name)=(244, 1, runtime.powerState) already exists.
[YYYY-MM-DDTHH:MM:SS] 66ffa54f.85d5 1245906874 VCDB vc STATEMENT: UPDATE VPX_ALARM_RUNTIME SET ENTITY_TYPE = $1 , STATE_VALUE = $2 , METRIC_VALUE = $3 , CREATED_TIME = $4 ,
STATUS_VALUE = $5 WHERE ENTITY_ID = $6 AND ALARM_ID = $7 AND EXPRESSION_NAME = $8
[YYYY-MM-DDTHH:MM: SS] 66ffa54f.85d5 1245906874 VCDB vc LOG: unexpected EOF on client connection with an open transaction
[YYYY-MM-DDTHH:MM: SS] 66ffa54f.85d6 0 VCDB vc LOG: could not receive data from client: Connection reset by peer
[YYYY-MM-DDTHH:MM: SS] 66ffa55a.8892 1245908517 VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_alarm_runtime"
[YYYY-MM-DDTHH:MM: SS] 66ffa55a.8892 1245908517 VCDB vc DETAIL: Key (entity_id, alarm_id, expression_name)=(244, 1, runtime.powerState) already exists.
[YYYY-MM-DDTHH:MM: SS] 66ffa55a.8892 1245908517 VCDB vc STATEMENT: UPDATE VPX_ALARM_RUNTIME SET ENTITY_TYPE = $1 , STATE_VALUE = $2 , METRIC_VALUE = $3 , CREATED_TIME = $4 ,
STATUS_VALUE = $5 WHERE ENTITY_ID = $6 AND ALARM_ID = $7 AND EXPRESSION_NAME = $8
[YYYY-MM-DDTHH:MM:SS] 66ffa55a.8892 1245908517 VCDB vc LOG: unexpected EOF on client connection with an open transaction
A host went not responding and is completely unresponsive. Due to that, the "Network Connectivity Lost" alarm was triggered. When the vCenter was trying to put the record of the alarm event into the database, there was a duplicate entry which caused vpxd to crash and vCenter to go down
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "REINDEX index pk_vpx_alarm_runtime;"
[YYYY-MM-DDTHH:MM: SS]66ffa54f.85d5 1245906874 VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_alarm_runtime"
[YYYY-MM-DDTHH:MM: SS]66ffa54f.85d5 1245906874 VCDB vc DETAIL: Key (entity_id, alarm_id, expression_name)-(244, 1, runtime.powerState) already exists.
[YYYY-MM-DDTHH:MM: SS]66ffa54f.85d5 1245906874 VCDB vc STATEMENT: UPDATE VPX_ALARM_RUNTIME SET ENTITY_TYPE - $1 , STATE_VALUE - $2 , METRIC_VALUE - $3 , CREATED_TIME - $4 ,
STATUS VALUE = $5 WHERE ENTITY ID = $6 AND ALARM_ID = $7 AND EXPRESSION _NAME = $8
ENTITY_ID = 244
ALARM_ID = 1
So this:
select * from TABLE_NAME where ENTITY_ID=ENTITY_ID and ALARM_ID=ALARM_ID and EXPRESSION_NAME="EXPRESSION_NAME"
Would be replaced by this:
select * from VPX_ALARM_RUNTIME where ENTITY_ID=244 and ALARM_ID=1 and EXPRESSION_NAME='runtime.powerState';
delete from VPX_ALARM_RUNTIME where ENTITY_ID=244 and ALARM_ID=1 and EXPRESSION_NAME='runtime.powerState';