task-cleanup-sql.bash

#!/bin/bash

# This will delete all tasks and associated task data from anything older than
# the number of days specified below defaulting to 120 days.
DAYS_AGO=120

echo ""
echo "DELETING All tasks and associated data from [$DAYS_AGO] days ago"
echo ""

TABLE_COUNTS="select count(*) as foreman_tasks_tasks from foreman_tasks_tasks;
select count(*) as foreman_tasks_locks from foreman_tasks_locks;
select count(*) as dynflow_execution_plans from dynflow_execution_plans;
select count(*) as dynflow_actions from dynflow_actions;
select count(*) as dynflow_steps from dynflow_steps;"

echo ""
echo "Table counts before:"
echo $TABLE_COUNTS | sudo -i -u postgres psql -d foreman

echo "Deleting dynflow_steps"
DS="DELETE FROM
  dynflow_steps ds
  USING
  foreman_tasks_tasks ft
  WHERE ft.external_id = ds.execution_plan_uuid and
  ft.ended_at < current_date - interval '$DAYS_AGO' day;"

sudo -i -u postgres psql -d foreman -c "$DS"

echo "Deleting dynflow_actions"
DA="DELETE FROM
  dynflow_actions da
  USING
  foreman_tasks_tasks ft
  WHERE ft.external_id = da.execution_plan_uuid and
  ft.ended_at < current_date - interval '$DAYS_AGO' day;"

sudo -i -u postgres psql -d foreman -c "$DA"

echo "Deleting dynflow_execution_plans"
DE="DELETE FROM
  dynflow_execution_plans de
  USING
  foreman_tasks_tasks ft
  WHERE ft.external_id = de.uuid and
  ft.ended_at < current_date - interval '$DAYS_AGO' day;"

sudo -i -u postgres psql -d foreman -c "$DE"

echo "Deleting from foreman_tasks_locks"

FL="DELETE FROM
  foreman_tasks_locks fl
  USING
  foreman_tasks_tasks ft
  WHERE ft.external_id = fl.task_id and
  ft.ended_at < current_date - interval '$DAYS_AGO' day;"

sudo -i -u postgres psql -d foreman -c "$FL"

echo "Deleting foreman_tasks_tasks"
FT="DELETE FROM
  foreman_tasks_tasks ft
  WHERE ft.ended_at < current_date - interval '$DAYS_AGO' day;"



sudo -i -u postgres psql -d foreman -c "$FT"

echo "Done."

echo "Table counts after:"
echo $TABLE_COUNTS | sudo -i -u postgres psql -d foreman