Here we have to manually reset the primary key index after restoring it from a dump file.

To check whether the values are out of sync, we can run the following commands:

SELECT MAX(the_primary_key) FROM the_table;

SELECT nextval('the_primary_key_sequence');

If the first value is higher than the second value, our sequence is out of sync.

We can back up our PG database and then run the following command:

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

This will set the sequence to the next available value that’s higher than any existing primary key in the sequence.

To Resolve this error in VMware

When vpxd process crashes randomly after upgrading to vCenter Server 6.5 with the following error:

ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk";
Panic: Unrecoverable database error. Shutting down VC

In the vpxd.log file, we can see entries similar to the one given below:

error vpxd[7F8DD228C700] [Originator@6876 sub=InvtVmDb opID=HB-host-476@72123-38e1cc31] >[VpxdInvtVm::SaveGuestNetworkAndDiskToDb] Failed to insert guest disk info for VM id = 976because of database error: "ODBC error: >(23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk";

–> Error while executing the query” is returned when executing SQL statement “INSERT INTO VPX_GUEST_DISK (VM_ID, PATH, CAPACITY, >FREE_SPACE) VALUES (?, ?, ?, ?)”
And in the postgresql.log file, you see entries similar to the one given below:

VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk"

VCDB vc DETAIL: Key (vm_id, path)=(976, /tmp) already exists.

Steps to fix the error are given below:

vCenter Services can be given a service restart. If the starting fails to initialize the service and shows the same crash reason, we can fix this by removing the impacted guest disk entry from vCenter Server Database. This information is safe to remove as it will be re-populated from the host.

2. Then connect the vCenter Database.

3. And identify the guest disk entry using the following query:

select FROM vc.vpx_guest_disk where vm_id=<vm id> and path='/tmp';For example:

select FROM vc.vpx_guest_disk where vm_id='976' and path='/tmp';

4. For deleting the duplicate value we can use the following command:

delete FROM vc.vpx_guest_disk where vm_id=<vm id> and path='/tmp';

For example:

select FROM vc.vpx_guest_disk where vm_id='976' and path='/tmp';

We can get the VM id  from the vpxd.log error entry