XRAY: How to fix Xray error: ”duplicate key value violates unique constraint” (SQLSTATE 23505)

Introduction 

When you use Xray 3.80+ and run Vulnerability/PublicData sync (vulnerabilities / public components / operational risk) against PostgreSQL, during DB Sync, Xray may fail with errors similar to:

ERROR: duplicate key value violates unique constraint "public_vulnerabilities_vuln_id_idx" (SQLSTATE 23505)

You may also see:

ERROR: duplicate key value violates unique constraint "versions_operational_risk_public_components_fk_idx" (SQLSTATE 23505)

Or:

ERROR: duplicate key value violates unique constraint "public_components_component_package_type_idx" (SQLSTATE 23505)

Root Cause 

The target PostgreSQL tables already contain records with the same unique key (e.g., the same vuln_id), so DB Sync attempts to insert duplicates and PostgreSQL rejects the insert with SQLSTATE 23505. This can happen after interrupted syncs, leftover/partial data, or upgrade/sync-state inconsistencies.

Resolution 

Migrate to DBSync v3, and reset DB sync state, and rerun onboarding. (If you already run DBSync v3,only rerun the onboarding)
Reference: DBSync v1 → v3 migration guide (Self-Hosted)
https://jfrog.com/help/r/jfrog-security-user-guide/products/xray/xray-management/xray-and-jfrog-external-db-sync/migration-guide-for-self-hosted-customers-upgrading-from-dbsync-v1-to-v3

The steps:
1. Enable DBSync v3 and temporarily disable Impact Analysis
    Add the following to Xray system.yaml:

server:
  dbSync:
    version3:
      enabled: true
    impactAnalysis:
      enabled: false

2. Reset DB sync parameters (force onboarding)
    Run the following SQL on Xray’s PostgreSQL database:

UPDATE updates_state
SET should_do_onboarding = False,
    last_successful_update = 0
WHERE id in (3,4);

3. Restart Xray
    Restart the Xray application/services to apply the configuration and re-establish DB connections.

4. Run DB Sync onboarding
    From the Xray UI, start the DB Sync process again. Onboarding can take a few hours depending on environment/data size.

5. Re-enable Impact Analysis (post-success)
    After DB Sync completes successfully, set impactAnalysis section to true in system.yaml.

server:
  dbSync:
    version3:
      enabled: true
    impactAnalysis:
      enabled: true

Then restart Xray again, and the error should be fixed now.

Note:

If you have more than 10 custom vulnerabilities, please contact JFrog Support before proceeding.