After problematic upgrade to Satellite 6.17, errata applicability is not being updated

Environment

• Red Hat Satellite 6.17

Issue

• Having Satellite 6.17 upgraded from 6.16 where the upgrade hit either of this postgres evr issue or a similar one
• Since the upgrade, errata applicability is not being updated: it shows applicable errata to a Host we just fully updated, regardless of uploading a DNF profile from it

Resolution

1. Create a Satellite backup or VM snapshot or have any other way of backup

2. Perform the checks listed in the Diagnostic Section of this solution article to verify that the issue at hand is exactly the same issue documented here.

3. Ensure some part of the migration will run smoothly. Run:

# su - postgres -c "psql foreman -c \"\d+ katello_installed_packages\"" | grep evr

If it will print something, DO NOT proceed with the next steps and contact Red Hat support.

4. If the output of the PostgreSQL query in step (3) above is empty, locate the failed migration file we need to manually re-run:

# file /usr/share/gems/gems/katello-4.16.*/db/migrate/20240924161240_katello_recreate_evr_constructs.rb

5. Apply the migration. To do so, run below command and wait for a prompt:

# foreman-rake console

6. Import the located file - correct the path if it is different:

require '/usr/share/gems/gems/katello-4.16.0.5/db/migrate/20240924161240_katello_recreate_evr_constructs.rb'

7. Run:

krec = KatelloRecreateEvrConstructs.new
krec.extension_enabled?("evr")
krec.up

8. Press Ctrl+D to exit the foreman-rake console

9. Run the following PostgreSQL queries:

# su - postgres -c "psql foreman -c \"\d+ katello_rpms\"" | grep evr
# su - postgres -c "psql foreman -c \"\d+ katello_installed_packages\"" | grep evr

Both commands should return several lines like:

  # su - postgres -c "psql foreman -c \"\d+ katello_rpms\"" | grep evr
   evr              | evr_t                       |           |          |                                          | extended |              | 
      "index_katello_rpms_on_name_and_arch_and_evr" btree (name, arch, evr)
      evr_insert_trigger_katello_rpms BEFORE INSERT ON katello_rpms FOR EACH ROW EXECUTE FUNCTION evr_trigger()
      evr_update_trigger_katello_rpms BEFORE UPDATE OF epoch, version, release ON katello_rpms FOR EACH ROW WHEN (old.epoch::text IS DISTINCT FROM new.epoch::text OR old.version::text IS DISTINCT FROM new.version::text OR old.release::text IS DISTINCT FROM new.release::text) EXECUTE FUNCTION evr_trigger()

  # su - postgres -c "psql foreman -c \"\d+ katello_installed_packages\"" | grep evr
   evr     | evr_t                  |           |          |                                                        | extended |              | 
      evr_insert_trigger_katello_installed_packages BEFORE INSERT ON katello_installed_packages FOR EACH ROW EXECUTE FUNCTION evr_trigger()
      evr_update_trigger_katello_installed_packages BEFORE UPDATE OF epoch, version, release ON katello_installed_packages FOR EACH ROW WHEN (old.epoch::text IS DISTINCT FROM new.epoch::text OR old.version::text IS DISTINCT FROM new.version::text OR old.release::text IS DISTINCT FROM new.release::text) EXECUTE FUNCTION evr_trigger()

If the lines are present, the issue is fixed.

10. Invoke a new errata applicability recalculation for a Host by executing dnf uploadprofile --force-upload on the host.

Root Cause

Due to conflicts with evr postgres extension observed during 6.16 -> 6.17 upgrade, one database migration was turned to be a no-op. That caused two postgres tables to miss a field that is used by errata applicability calculation.

Diagnostic Steps

• Actions::Katello::Applicability::Hosts::BulkGenerate task fails with error:

  PG::UndefinedColumn: ERROR: column katello_rpms.evr does not exist
  LINE 5: katello_rpms.evr > katello_installed_packages.evr
  

• /var/lib/pgsql/data/log/*log file contains similar error:

  2025-06-18 01:37:38 CEST ERROR:  column katello_rpms.evr does not exist at character 336
  2025-06-18 01:37:38 CEST STATEMENT:  SELECT "katello_rpms"."id" FROM "katello_rpms" INNER JOIN katello_repository_rpms ON
                      katello_rpms.id = katello_repository_rpms.rpm_id INNER JOIN katello_installed_packages ON 
                      katello_rpms.name = katello_installed_packages.name AND
                      katello_rpms.arch = katello_installed_packages.arch AND
                      katello_rpms.evr > katello_installed_packages.evr AND
  

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.