Bug 2167984

Summary:	Cannot reindex postgres on Satellite 6 in RHEL 8
Product:	Red Hat Satellite	Reporter:	Jessica Richards <jrichards2>
Component:	Upgrades	Assignee:	satellite6-bugs <satellite6-bugs>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Satellite QE Team <sat-qe-bz-list>
Severity:	high	Docs Contact:
Priority:	high
Version:	6.11.0	CC:	ahumbe, ajchavan, arsingh, bmertens, dhjoshi, egolov, ehelms, hyu, jbreitwe, jwfrey, msunil, sabhasin, saydas, wpinheir
Target Milestone:	Unspecified	Keywords:	PrioBumpGSS, Triaged
Target Release:	Unused
Hardware:	x86_64
OS:	Linux
Whiteboard:
Fixed In Version:		Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2023-09-06 12:53:33 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:	2142270
Bug Blocks:

Description Jessica Richards 2023-02-07 22:26:29 UTC

Description of problem:

On Satellite 6 servers that have been upgraded to RHEL 8, attempts to reindex the postgres database yield errors like this:

reindexdb: error: reindexing of database "foreman" failed: ERROR:  could not create unique index "index_fact_names_on_name_and_type"
DETAIL:  Key (name, type)=(augeasversion, PuppetFactName) is duplicated.

The key name varies, but the index error remains.


Version-Release number of selected component (if applicable):

Satellite 6.11 and 6.12


How reproducible:

unclear


Steps to Reproduce:
1.  upgrade Satellite 6.11 to RHEL 8 (and optionally to Satellite 6.12)
2.  run this command:  runuser -u postgres -- reindexdb -a


Actual results:

index_fact_names_on_name_and_type errors


Expected results:

reindex should complete successfully


Additional info:

These steps also failed:

# foreman-maintain service stop --exclude postgresql

# su - postgres

$ psql foreman
foreman=# reindex database foreman;

\c candlepin
candlepin=# reindex database candlepin;
\q

\c pulpcore
pulpcore=# reindex database pulpcore;
\q

# foreman-maintain service restart

Comment 20 Sayan Das 2023-02-10 16:52:12 UTC


With the "runuser -u postgres -- reindexdb -a " step, one can run into two different issues:

A) With rhsm related facts


# 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 "index_fact_names_on_name_and_type" DETAIL: Key (name, type)=(memory::swaptotal, Katello::RhsmFactName) is duplicated.


To fix the same, Delete any facts that are of type 'Katello::RhsmFactName' 

# cat << EOF | foreman-rake console
conf.echo = false
fact_name_ids = FactName.unscoped.where(:type => 'Katello::RhsmFactName').ids
pp fact_name_ids.count
FactValue.unscoped.where(:fact_name_id => fact_name_ids).delete_all
FactName.unscoped.where(:id => fact_name_ids).delete_all
pp FactName.unscoped.where(:type => 'Katello::RhsmFactName').ids.count
EOF


These facts will come back on their own when the rhsm checkin's will happen for the individual client systems ( in every 4 hours ). 



B) With puppet related facts :

# 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 "index_fact_names_on_name_and_type"
DETAIL:  Key (name, type)=(augeasversion, PuppetFactName) is duplicated.


To fix the same, Delete any facts that are of type 'PuppetFactName' 

# cat << EOF | foreman-rake console
conf.echo = false
fact_name_ids = FactName.unscoped.where(:type => 'PuppetFactName').ids
pp fact_name_ids.count
FactValue.unscoped.where(:fact_name_id => fact_name_ids).delete_all
FactName.unscoped.where(:id => fact_name_ids).delete_all
pp FactName.unscoped.where(:type => 'PuppetFactName').ids.count
EOF


These facts will also come back on their own when puppet agent will be executed on concerned systems or a scheduled puppet run will happen on the client systems.


Once done, Retry the reindexing 

# runuser -u postgres -- reindexdb -a


Once it is successful and all services of satellite are running fine, Proceed with executing the post-upgrade steps from https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8/html/upgrading_from_rhel_7_to_rhel_8/performing-post-upgrade-tasks-rhel-7-to-rhel-8_upgrading-from-rhel-7-to-rhel-8#doc-wrapper

Comment 26 Sayan Das 2023-03-29 03:49:53 UTC

We have another situation where the facts are not affected but it's katello_available_module_streams 

# 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.

KB:

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 - Red Hat Customer Portal
https://access.redhat.com/solutions/7004750