How to properly perform reindexing PostgreSQL databases in Satellite6?

Solution Verified - Updated

Environment

  • Red Hat Satellite 6.10 or later

Issue

  • For various performance reasons, reindex for some database/table/index in Satellite's PostgreSQL is needed. How to perform it in a safe and proper way?

Resolution

Confirm your expectations

Before starting the procedure, ensure the reindex meets your goal. Reindexing improves performance and decreases response times of Satellite. If your concern is disk usage of PostgreSQL database, then run database vacuuming instead.

Procedure prerequisites:

  • It is recommended to create a VM snapshot or Satellite backup.
  • Ensure logical volume with /var/lib/pgsql is at most 50% full - during the procedure, the pgsqldata dir can double its size.

Procedure itself

  1. Run the reindex command(s) in a safe envelope with Satellite services stopped:
# satellite-maintain service stop --exclude=postgresql
  1. Run the specific REINDEX commands. E.g. to reindex foreman DB:
# su - postgres -c "psql foreman -c \"REINDEX (VERBOSE) DATABASE foreman;\""

The (VERBOSE) option is optional but useful, as it prints some stats and namely progress of potentially lengthy procedure.

  1. Once all reindexing is done, start Satellite services again:
# satellite-maintain service start

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

It is recommended to run reindex when the application is done, due to several reasons:

  • It ensures data consistency.
  • The reindexing itself is executed much faster.
  • Client requests are not delayed or blocked by reindex locking some table(s) it is currently working on.
  • Troubleshooting of potential issues is much easier, as one has an isolated stable environment that is failing.
SBR
Product(s)
Components
Category
Tags

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.