Reindexing of postgresql databases fail with WARNING: collation "pulp_ansible_semver" has version mismatch in Red Hat Satellite 6

Environment

• Red Hat Satellite 6.15 or later

Issue

• Reindex of Satellite postgresql database shows a warning related to version mismatch:

  WARNING:  collation "pulp_ansible_semver" has version mismatch 
  DETAIL:  The collation in the database was created using version 153.80, but the operating system provides version 153.14.
  

Resolution

• Resolve the warning with the following command:

  # runuser -c 'echo "ALTER COLLATION pulp_ansible_semver REFRESH VERSION;" | psql pulpcore' postgres
  

  The above command may show something like could not change directory to "/root": Permission denied but that is not an error to be concerned of.

• Then try to run the reindex again:

  # runuser -u postgres -- reindexdb -a
  

• If the reindex finishes successfully, run satellite-installer:

  # satellite-installer -v
  

• If you encountered this issue during an upgrade, run the following command finish any remaining post-upgrade tasks:

  # satellite-maintain upgrade run
  

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

• Versions of some objects in the database are mismatched:

    # su - postgres -c "psql pulpcore -c \"SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "collation_name", pg_describe_object(classid, objid, objsubid) AS "Object", c.collversion AS collversion, pg_collation_actual_version(c.oid) actual_collversion FROM pg_depend d JOIN pg_collation c ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid WHERE pg_describe_object(refclassid, refobjid, refobjsubid) ilike '%pulp_ansible_semver%' ORDER BY 1, 2;\""
  
          collation_name         |                                object                                | collversion | actual_collversion
  -------------------------------+----------------------------------------------------------------------+-------------+--------------------
  collation pulp_ansible_semver | column version of table ansible_collectionversion                    | 153.80      | 153.14
  collation pulp_ansible_semver | column version of table ansible_role                                 | 153.80      | 153.14
  collation pulp_ansible_semver | index ansible_collectionversion_namespace_name_version_96aacd81_uniq | 153.80      | 153.14
  collation pulp_ansible_semver | index ansible_role_version_name_namespace_8efe8ef2_uniq              | 153.80      | 153.14
  (4 rows)
  

Diagnostic Steps

• Reindex of the database shows a warning related to version mismatch:

  # runuser -u postgres -- reindexdb -a
  reindexdb: reindexing database "candlepin"
  reindexdb: reindexing database "foreman"
  reindexdb: reindexing database "postgres"
  reindexdb: reindexing database "pulpcore"
  WARNING:  collation "pulp_ansible_semver" has version mismatch 
  DETAIL:  The collation in the database was created using version 153.80, but the operating system provides version 153.14.
  HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION public.pulp_ansible_semver REFRESH VERSION, or build PostgreSQL with the right library version.
  reindexdb: reindexing database "template1"
  

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.