In Red Hat Satellite 6 many repositories fail to sync with error 'duplicate key value violates unique constraint "rpm_package_pkgId_key"'

Solution Verified - Updated 16 Jul 2024

Environment

Red Hat Satellite 6

Issue

In Red Hat Satellite 6 many repositories fail to sync with error :

duplicate key value violates unique constraint "rpm_package_pkgId_key"
DETAIL:  Key ("pkgId")=(53d3c15711e89c48416a7e134576b62abfb8bdcdfc5db0e65bd423f4bga8e428) already exists.

Resolution

Before Proceeding please ensure to fulfill following requirement:

 * Make a satellite snapshot so that we can rollback if something unexpected is happened.
 * Add and make sure `/var/opt/rh/rh-postgresql12/lib/pgsql` directory till **Red Hat Satellite 6.11 on RHEL7** and `/var/lib/pgsql` from **Red Hat Satellite 6.11 on RHEL8** onwards has at least equivalent free space as that of consumed. This is to perform full vacuum on some large tables in the Pulpcore database

Stop all Satellite services except Postgres.

# foreman-maintain service stop --exclude postgresql

Full vacuum and re-analyze "pulpcore" database.

# su - postgres -c "vacuumdb -f -d pulpcore -v -z"

Delete duplicate rows in core_content;

# su - postgres -c "psql pulpcore -c \"DELETE FROM core_content WHERE ctid IN (SELECT ctid FROM (SELECT ctid, ROW_NUMBER() OVER (partition BY pulp_id) AS rnum FROM core_content) t WHERE t.rnum > 1);\""

Reindex all tables in pulpcore database.

# su - postgres -c "reindexdb -d pulpcore -v"

Run the following commands to delete orphaned rows.

   # su - postgres

   # psql pulpcore

   # DELETE FROM rpm_package WHERE content_ptr_id IN (select content_ptr_id from rpm_package rp left join core_content c on c.pulp_id = rp.content_ptr_id and pulp_type = 'rpm.package' where c.pulp_id is null);

   # DELETE FROM rpm_updatereference WHERE update_record_id IN (select content_ptr_id from rpm_updaterecord rp left join core_content c on c.pulp_id = rp.content_ptr_id and pulp_type = 'rpm.advisory' where c.pulp_id is null);

   # DELETE FROM rpm_updatecollectionpackage WHERE update_collection_id IN (select pulp_id from rpm_updatecollection where update_record_id in (select content_ptr_id from rpm_updaterecord rp left join core_content c on c.pulp_id = rp.content_ptr_id and pulp_type = 'rpm.advisory' where c.pulp_id is null));

   # DELETE FROM rpm_updatecollection WHERE update_record_id IN (select content_ptr_id from rpm_updaterecord rp left join core_content c on c.pulp_id = rp.content_ptr_id and pulp_type = 'rpm.advisory' where c.pulp_id is null);

   # DELETE FROM rpm_updaterecord WHERE content_ptr_id IN (select content_ptr_id from rpm_updaterecord rp left join core_content c on c.pulp_id = rp.content_ptr_id and pulp_type = 'rpm.advisory' where c.pulp_id is null);

   # \q

   # exit

Restart Satellite services.

   # foreman-maintain service restart

   # hammer ping

For more KB articles/solutions related to Red Hat Satellite 6.x Repository Issues, please refer to the Red Hat Satellite Consolidated Troubleshooting Article for Red Hat Satellite 6.x Repository Issues.

Root Cause

Multiple corrupted repositories leads to sync failure which can be cleared using above steps.

Diagnostic Steps

Till Red Hat Satellite 6.11 on RHEL7:

   # cd /var/opt/rh/rh-postgresql12/lib/pgsql/data/log

   $ grep 'ERROR:' *.log | cut -c-1000 | sed 's/\(2022-.* [0-9].:\).*UTC/\1/' | sort | uniq -c
         1 postgresql-Fri.log:2022-09-30 02:52:54 EDT ERROR:  duplicate key value violates unique constraint "core_artifact_sha256_key"
         3 postgresql-Fri.log:2022-09-30 02:52:58 EDT ERROR:  duplicate key value violates unique constraint "core_artifact_sha256_key"
         1 postgresql-Fri.log:2022-09-30 12:00:00 EDT ERROR:  current transaction is aborted, commands ignored until end of transaction block
         1 postgresql-Fri.log:2022-09-30 12:00:00 EDT ERROR:  update or delete on table "cp_cert_serial" violates foreign key constraint "fk16370c54b9410fc" on table "cp_certificate"
         2 postgresql-Mon.log:2022-10-03 00:00:56 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"
         1 postgresql-Mon.log:2022-10-03 00:01:26 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"
         1 postgresql-Mon.log:2022-10-03 00:01:35 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"
         2 postgresql-Mon.log:2022-10-03 00:02:18 EDT ERROR:  duplicate key value violates unique constraint "core_artifact_sha256_key"
         1 postgresql-Mon.log:2022-10-03 00:02:31 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"

From Red Hat Satellite 6.11 on RHEL8 onwards:

   # cd /var/lib/pgsql/data/log

   $ grep 'ERROR:' *.log | cut -c-1000 | sed 's/\(2022-.* [0-9].:\).*UTC/\1/' | sort | uniq -c
         1 postgresql-Fri.log:2022-09-30 02:52:54 EDT ERROR:  duplicate key value violates unique constraint "core_artifact_sha256_key"
         3 postgresql-Fri.log:2022-09-30 02:52:58 EDT ERROR:  duplicate key value violates unique constraint "core_artifact_sha256_key"
         1 postgresql-Fri.log:2022-09-30 12:00:00 EDT ERROR:  current transaction is aborted, commands ignored until end of transaction block
         1 postgresql-Fri.log:2022-09-30 12:00:00 EDT ERROR:  update or delete on table "cp_cert_serial" violates foreign key constraint "fk16370c54b9410fc" on table "cp_certificate"
         2 postgresql-Mon.log:2022-10-03 00:00:56 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"
         1 postgresql-Mon.log:2022-10-03 00:01:26 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"
         1 postgresql-Mon.log:2022-10-03 00:01:35 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"
         2 postgresql-Mon.log:2022-10-03 00:02:18 EDT ERROR:  duplicate key value violates unique constraint "core_artifact_sha256_key"
         1 postgresql-Mon.log:2022-10-03 00:02:31 EDT ERROR:  duplicate key value violates unique constraint "rpm_package_pkgId_key"

SBR

SysMgmt

Product(s)

Red Hat Satellite

Components

Category

Configure

Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.