Reindexing of foreman database fails with error `ERROR: could not create unique index "katello_available_module_streams_name_stream_context"` after the in-place OS upgrade of Satellite 6.11 to RHEL 8

Environment

• Red Hat Satellite 6.11
• Red Hat Enterprise Linux 8.x

Issue

Reindexing of foreman database fails with the following error:

# runuser -u postgres -- reindexdb -a
reindexdb: reindexing database "candlepin"
reindexdb: reindexing database "foreman"
reindexdb: error: reindexing of database "foreman" failed: ERROR: could not create unique index "katello_available_module_streams_name_stream_context".
DETAIL: key (name, stream, context)=(pmdk, 1-fileformat-v6, b4937e53) is duplicated.

Resolution

1. Create a backup/snapshot of the Satellite server, as we are going to merge the duplicate records.

2. It is recommended to stop all the Satellite services except the Postgresql server before proceeding to the next step, especially for a heavy\busy Red Hat Satellite server.
   This will ensure that no duplicate records will be re-created before we fix the database indexes.

   # satellite-maintain service stop --exclude postgresql
   

3. Merge the duplicate records in the database by running the following foreman-rake script on the affected satellite server:

   # cat << EOF | foreman-rake console
   Katello::AvailableModuleStream.all.group_by{|ms| [ms.name, ms.stream, ms.context]}.select{|k,v| v.size > 1}.values.each do |dups|
     dups.sort_by!(&:id)
     existing = dups[0]
     dup_ids = dups[1..-1].map(&:id)
     p "Found duplicate available module stream #{existing.name} #{existing.stream} #{existing.context} ids #{dup_ids}"
     hams = Katello::HostAvailableModuleStream.where(available_module_stream_id: dup_ids)
     p "Merging #{hams.size} host available module streams."
     hams.in_batches.update_all(available_module_stream_id: existing.id)
     p "Deleting duplicate available module stream ids: #{dup_ids}"
     Katello::AvailableModuleStream.where(id: dup_ids).destroy_all
   end
   EOF
   

4. Once the cleanup has been completed, retry the reindexdb action.

   # runuser -u postgres -- reindexdb -a
   

5. Restart the Satellite services if you have stopped them.

   # satellite-maintain service restart
   

6. Once successfully executed and all services of the concerned satellite server are running fine, proceed with completing the post-upgrade steps from the RHEL upgrade guide.

Reach out to the This content is not included.Red Hat Technical Support should any further assistance be required.

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

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.