[Satellite6] candlepin postgres database grows

Environment

Red Hat Satellite or Proxy 6.2 - 6.6

Issue

• disk space of /var/lib/pgsql is growing over time
• checking what database and table takes much space, (one of) the biggest table is cp_event in candlepin db
• are any data in the table important? is there a way how to shrink it? How old events are worth to keep?

Resolution

It is safe to purge events older than 14 days. This can be achieved via commands:

echo "delete from cp_event where timestamp < now() - interval '14 days';"  | sudo -u postgres psql -d candlepin
echo "vacuum full cp_event;" | sudo -u postgres psql -d candlepin

Another option is to install a cron script that will clean cp_event table. Create a file called in cp_event_cleanup in /etc/cron.weekly/ with the following content:

#!/bin/bash
echo "delete from cp_event where timestamp < now() - interval '14 days';"  | sudo -u postgres psql -d candlepin

And add execution bit to the file:

chmod +x /etc/cron.weekly/cp_event_cleanup

For more KB articles/solutions related to Red Hat Satellite 6.x Candlepin Issues, please refer to the Consolidated Troubleshooting Article for Red Hat Satellite 6.x Candlepin Issues

Root Cause

cp_event table is used for storing various old events and is meant for auditing purposes only. Any record older than a month is usually not interesting and can be deleted.

A This content is not included.RFE has been opened to automate this process.

Diagnostic Steps

Listing databases and their sizes, candlepin consumes the most space:

# su - postgres -c "psql -c '\l+'"
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges    |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+------------------------+---------+------------+--------------------------------------------
 candlepin | postgres | UTF8     | en_US.utf8  | en_US.utf8  | =T/postgres           +| 8529 MB | pg_default | 
           |          |          |             |             | postgres=CTc/postgres +|         |            | 
           |          |          |             |             | candlepin=CTc/postgres |         |            | 
 foreman   | foreman  | UTF8     | en_US.utf8  | en_US.utf8  | =T/foreman            +| 3160 MB | pg_default | 
           |          |          |             |             | foreman=CTc/foreman    |         |            | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                        | 5477 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +| 6485 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres  |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +| 5477 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres  |         |            | 
(5 rows)

Printing what tables in the candlepin database consume most space:

# su - postgres -c "psql candlepin -c \"select table_schema, table_name, pg_relation_size(table_schema||'.'||table_name) from information_schema.tables order by 3 desc limit 10;\""
 table_schema |    table_name     | pg_relation_size 
--------------+-------------------+------------------
 public       | cp_event          |         35455872
 public       | cp_stat_history   |          8609792
 public       | cp_content        |          2572288
 public       | cp_consumer_facts |           720896
 pg_catalog   | pg_attribute      |           647168
 pg_catalog   | pg_proc           |           507904
 pg_catalog   | pg_depend         |           466944
 public       | cp_pool_products  |           458752
 pg_catalog   | pg_statistic      |           417792
 public       | cp_id_cert        |           319488
(10 rows)

#

Vast majority of events is older than one month:

# su - postgres -c "psql candlepin -c \"select count(*) from cp_event where timestamp < 'now'::timestamp - '1 month'::interval;\""
  count 
--------
 126369
(1 row)

# su - postgres -c "psql candlepin -c \"select count(*) from cp_event where timestamp >= 'now'::timestamp - '1 month'::interval;\""
 count 
-------
   588
(1 row)

#

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.