PostgreSQL database 23505 is a common error which can be seen along with the error message "duplicate key violates unique constraint".

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related database queries.

In this context, we shall look into methods to resolve this PostgreSQL error.

Nature of PostgreSQL Error code 23505 

Sometimes we may get the following message when trying to insert data into a PostgreSQL database:

ERROR:  duplicate key violates unique constraint

This happens when the primary key sequence in the table we're working on becomes out of sync. And this might likely be because of a mass import process.

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.

How to fix PostgreSQL Error code 23505 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.

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.

For vCenter Server with vPostgres database:

1. First, take a snapshot of the vCenter Server machine before proceeding.

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

5. Finally start the Service.

We can delete the snapshot after observing the stability of the vCenter Server.

[Need assistance in fixing PostgreSQL Errors? We can help you. ]