Postgresql database is growing and disk space utilization of /var/lib/pgsql is increasing in Red Hat Satellite 6

Environment

• Red Hat® Satellite 6.9 and above

Issue

• The candlepin and/or foreman database has grown huge.
• The usage of /var/lib/pgsql filesystem is in GB's now and growing further day by day.
• Performance of the Red Hat Satellite server has been degraded.

Resolution

CAUTION: It's recommended to take a Satellite Backup or VMware snapshot of the Red Hat Satellite server before proceeding any further with the cleanup process.

STEP 1: Perform Diagnosis:

• At first, go through the Diagnostic Steps section of this article to identify the tables containing a huge amount of data inside the foreman or candlepin database.

  • Execute the postgres-size-report.sh on the Red Hat Satellite server 6.9 and below.
  • Execute the postgres-size-report-with-pulpcore.sh on the Red Hat Satellite server 6.10 and above.
  • Identify the table consuming a good amount of space (in GB).
  • Note down the table name which requires cleanup.

STEP 2: Investigate and cleanup Candlepin database:

• It's usually the cp_event table that grows inside the candlepin database.
• If the postgres-size-report-with-pulpcore.sh script have displayed abnormal size for the cp_event table, then follow the steps mentioned in the solution article 3083651 to further investigate and cleanup.

STEP 3: Investigate and cleanup foreman database:

• The postgres-size-report-with-pulpcore.sh should already have provided the information required to identify the table which might require cleanup.

• Among all, There are nine specific tables present inside the foreman database which are known to grow over the period of time and get filled up with a good amount of data.

  • logs
  • foreman_tasks_tasks
  • audits
  • fact_names
  • fact_values
  • sessions
  • reports
  • trends
  • trend_counters

• If it's being observed that the foreman database has grown huge, there is a possibility that any or some of these tables are holding up the older records and require cleanup.

• If any database table other than those listed above has been identified by the script as huge, please reach out to This content is not included.Red Hat Technical Support with a sosreport and the output of the script.

• Depending on the results provided by postgres-size-report-with-pulpcore.sh script, one can identify and clear the old records from the concerned table by following the relevant cleanup process as mentioned below.

  • The logs table inside the foreman database can be investigated and cleaned up further by following the solution article 4449961.

  • The foreman_tasks_tasks tables inside the foreman database can be investigated and cleaned up further by following the solution article 2755731.

  • The old audit information from the audits table inside the foreman database can be cleaned up by following the solution article How to clean up old audit information from the audits table inside the foreman database on Red Hat Satellite 6?.

  • The host facts stored in fact_names table inside the foreman database can be investigated and cleaned up further by following the solution article 3764171.

  • The orphan values of the host facts stored in fact_values table inside the foreman database can be cleaned up by following the solution article How to clean up orphan values of the host facts stored in fact_values table inside the foreman database on Red Hat Satellite 6?

  • The sessions table inside the foreman database can be cleaned up by following the solution article How to clean up the sessions table inside the foreman database on Red Hat Satellite 6?

  • The reports table inside the foreman database can be cleaned up by following the solution article How to clean up old reports information from the reports table inside the foreman database on Red Hat Satellite 6?

  • If trends or trend_counters table inside foreman database is consuming too much space and requires cleanup, then follow the steps in the solution article How to clean up the trends and trend_counters tables inside the foreman database on Red Hat Satellite 6?

STEP 4: Investigate and cleanup pulpcore database ( applicable for Satellite 6.11+ ):

• Cleanup

  • Any unused CV and CCV versions
  • Any CV or CCV that may no longer be needed at all
  • Any repositories that may no longer be needed anymore

• Clean up the orphaned data. Refer to the following article 6685201 for the same.

• Reduce the amount of rpm changelog entries stored in the database by following the approach mentioned in the article 6977656 for Red Hat Satellite 6.11.

• Perform the vacuumdb operation on pulpcore database by referring to the next section of this article.

   

STEP 5: Perform vacuuming on the individual databases:

• As It's been mentioned earlier, It might only be required to clean up one or two tables based on the initial diagnosis and once the cleanup has been performed, re-execute the postgres-size-report-with-pulpcore.sh script and identify if the sizes of the concerned tables got reduced or not.

• The deletion of records from different tables can still keep the disk space reserved in the database. To finish the cleanup process, it is also advised to perform vacuuming of the database as per the solution article How to perform vacuuming on the individual Red Hat Satellite 6 databases?

In case of any questions or further concerns, please reach out to This content is not included.Red Hat Technical Support.

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

• There are multiple sceanrios present which could be the reason behind the growth of postgres database.

  • Old and\or Orphaned data is still residing in individual database tables and requires manual cleanup.
  • The cronjobs for clearing old sessions and reports data are not running properly.
  • Too many old tasks and audit reports present in the Database.
  • Hosts were removed from satellite but the related host facts are still present in the satellite database.

Diagnostic Steps

# chomd +x postgres-size-report-with-pulpcore.sh
# ./postgres-size-report-with-pulpcore.sh

 - This script shows the following details.

    1. The total size of both the candlepin and foreman database.
    2. The individual size of all the tables inside the candlepin database.
3. The individual size of all the tables inside the foreman database.
4. The usage of the filesystem related to postgresql.
5. Count of total data and data older than days for some of the concerned tables from the foreman database which gets easily filled up and causes performance issues.

• From the output of the script, identify the large tables consuming data in GB's or having an unexpected amount of records in some of the tables causing the table to grow huge. An example of huge table size will look something like below.

                table_name               |  total  |  index  |   toast    |  table
  ---------------------------------------+---------+---------+------------+---------
   sessions                              | 39 GB   | 21 GB   | 8192 bytes | 18 GB
   audits                                | 17 GB   | 1041 MB | 14 GB      | 1832 MB
  

• Investigate if the cronjobs present inside /etc/cron.d/foreman are getting executed properly or not.

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.