Analytics: Control Size of Run Resources Table

Sean Horn -

NOTE: This will not reduce the size of the database on disk, but will recover space internally. If you need to recover space on disk, you will need to shutdown and run a VACUUM VERBOSE FULL (info here) or shutdown the whole system, start postgresql again and EXPORT/IMPORT

You can do a full EXPORT/IMPORT like this, for safety.

Export Actions database

/opt/opscode-analytics/embedded/bin/pg_dump -Fc actions > alright-actions.psqlc

Restore Actions database

/opt/opscode-analytics/embedded/bin/pg_restore -c -Fc -v -d actions alright-actions.psqlc

Immediately, you can delete run_resources rows based on date from Analytics with:

0) backup the existing table (customers having space issues might opt not to)
create table run_resources_backup as select * from run_resources;

You can get to the postgres monitor on your Analytics machine and

  1. opscode-analytics-ctl stop && opscode-analytics-ctl start postgresql
  2. sudo su - chef-pgsql
  3. bash
  4. psql actions
  5. Enter query followed by semicolon
  6. \q
  7. opscode-analytics-ctl start

You can exit the monitor with \q

1) This example query will retain run_resources data before the given timestamp and remove all other run_resources data, modified by the other sub selects. This will save older data and get rid of the new. Change the direction of the arrow to retain only the rows after the given timestamp. This will save newer data and get rid of the old. The only screen that I know of that is affected is in the Nodes detail view. You can also adjust how much history is deleted by adjusting the timestamp given at the end of the following query.

1a. Export the complete database using the commands given earlier to a filesystem other than /var/opt/opscode-analytics. Verify that it looks correct.

1b. COPY (select * from run_resources where id in (
select id from run_resources where run_converge_id in (
select id from run_converges where run_id in (
select id from runs where start_time < timestamp '2016-01-01 00:00:00')))) TO '/absolute/path/to/my/amended/table/some_file'; 1c. Verify the COPY above took place and contains only the desired rows 1d. TRUNCATE run_resources; 1e. COPY 'run_resources' FROM '/absolute/path/to/my/amended/table/some_file';

1f. Start the full Analytics system back up
Have more questions? Submit a request


Powered by Zendesk