Re-indexing the Postgres Database can not create unique index for katello_erratum_packages_eid_nvrea_n_f..

Environment

• Red Hat Satellite 6.

Issue

• Re-indexing the Postgres Database can not create unique index for katello_erratum_packages_eid_nvrea_n_f.

  # runuser -u postgres -- reindexdb -a
  could not change directory to "/root": Permission denied
  reindexdb: reindexing database "candlepin"
  reindexdb: reindexing database "foreman"
  reindexdb: error: reindexing of database "foreman" failed: ERROR:  could not create unique index 
  "katello_erratum_packages_eid_nvrea_n_f"
  DETAIL:  Key (erratum_id, nvrea, name, filename)=(29867, openblas-openmp64-0.3.3-2.el7.x86_64, openblas- 
  openmp64, 
  openblas-openmp64-0.3.3-2.el7.x86_64.rpm) is duplicated.
  

Resolution

• Run following command to delete the duplicate entries Postgres:

  # echo "delete from katello_erratum_packages where id in (select max(id) from katello_erratum_packages where 
  erratum_id in (select erratum_id from katello_erratum_packages group by nvrea, erratum_id having count(nvrea) 
  >1) and nvrea in (select nvrea from katello_erratum_packages group by nvrea, erratum_id having count(nvrea) >1) 
  and id not in (select id from katello_module_stream_erratum_packages) group by nvrea, erratum_id order by 
  nvrea);" | sudo -u postgres psql foreman
  

• Once the cleanup has been completed, retry the reindexdb action:

  # satellite-maintain service restart
  # satellite-maintain maintenance-mode stop
  # satellite-maintain service stop --exclude postgresql
  # runuser -u postgres -- reindexdb -a
  

• If the reindexdb action reports the duplication error with PuppetFactName, or Katello::RhsmFactName, or ForemanAnsible::FactName, or DiscoveryFactName, then refer to Solution 2 of solution article to resolve the conflict.

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

Root Cause

• The database has stale entries.

• When this occurs during a Satellite upgrade, the issue is tracked under This content is not included.JIRA SAT-35489.

• This content is not included.JIRA SAT-35489 has been fixed in Satellite 6.17.6, 6.18.0, and later versions.

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.