How to clean up orphan values of the host facts stored in fact_values table inside the foreman database on Red Hat Satellite 6?

Environment

• Red Hat Satellite 6.x

Issue

• Using the script attached to Postgresql database is growing and disk space utilization of /var/lib/pgsql is increasing in Red Hat Satellite 6, the fact_values table inside the foreman database was identified as one of the largest tables.

Resolution

1. Perform the checks as indicated in the Diagnostic Steps section.

2. The orphan values of the host facts stored in fact_values table inside the foreman database can be cleaned up using either of the following commands based on the user's choice:

Option (1):

If the count is not too high, we can perform the cleanup in one batch by running the following command:

# foreman-rake console <<< "FactValue.where.not(:fact_name => FactName.reorder(nil)).in_batches.delete_all"

Option (2):

If the count is too high, we can perform the cleanup in batches along with throttling the rate of deletion:

```
# cat << FIX | foreman-rake console
puts("Records to delete (in batches of 1000): #{FactValue.where.not(:fact_name => FactName.reorder(nil)).count}")
FactValue.where.not(:fact_name => FactName.reorder(nil)).each_with_index do |batch, batch_index|
  puts "#{Time.now}:  Processing batch ##{batch_index}"
  batch.delete_all
  sleep(1)
end
FIX
```

Note:
The batch size, in the script, is implicitly set to the default, which in Rails is 1000 records per batch.

3. Repeat the checks as indicated in the Diagnostic Steps section to see if the count was significantly lowered.

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

Diagnostic Steps

1. Check what fact name stores the most fact values:

# su - postgres -c "psql foreman -c \"SELECT fn.id,fn.name,fn.type,COUNT(*) FROM fact_values AS fv INNER JOIN fact_names AS fn ON fv.fact_name_id = fn.id GROUP BY fn.id ORDER BY count DESC LIMIT 20;\""

2. Check what host requires the most facts values:

# su - postgres -c "psql foreman -c \"SELECT h.id,h.name,COUNT(*) FROM fact_values AS fv INNER JOIN hosts AS h ON fv.host_id = h.id GROUP BY h.id ORDER BY count DESC LIMIT 20;\""

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.