Bobcares

wesupport

CLIENT AREACall Us 1-800-383-5193
Bobcares

wesupport

Call Us 1-800-383-5193
Bobcares

wesupport

Call Us 1-800-383-5193

Need help?

Our experts have had an average response time of 11.7 minutes in August 2021 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

PostgreSQL Error code 23505 – How to fix

by | Jun 7, 2021

Wondering how to fix PostgreSQL Error code 23505? We can help you.

One of the most common error codes with the PostgreSQL database is 23505. It can be seen along with the error message “duplicate key violates unique constraint”

Here at Bobcares, we often handle requests from our customers to fix similar PostgreSQL errors as a part of our Server Management Services. Today we will see how our support engineers fix this for our customers.

How PostgreSQL Error code 23505

At times 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.

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.

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? We can help you]

Conclusion

In short, we saw how our Support Techs fix PostgreSQL Error code 23505 for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Categories

Tags