Recurring analytics services maintenance tasks

Many Apigee Analytics Services tasks can be performed using standard Postgres utilities. The routine maintenance tasks you would perform on the Analytics database—such as database reorganization using VACUUM, reindexing and log file maintenance—are the same as those you would perform on any PostgreSQL database. Information on routine Postgres maintenance can be found at http://www.postgresql.org/docs/9.1/static/maintenance.html.

For more on maintaining PostgreSQL database, see http://www.postgresql.org/docs/9.1/static/maintenance.html.

Pruning Analytics Data

As the amount of analytics data available within the Apigee repository increases, you may find it desirable to "prune" the data beyond your required retention interval. Run the following command to prune data for a specific organization and environment:

/opt/apigee/apigee-service/bin/apigee-service apigee-postgresql pg-data-purge org_name env_name number_of_days_to_retain

To run the script, enter the following command:

/opt/apigee/apigee-service/bin/apigee-service apigee-postgresql pg-data-purge org_name env_name number_of_days_to_retain [Delete-from-parent-fact - N/Y] [Confirm-delete-from-parent-fact - N/Y]

The script has the following options:

  • Delete-from-parent-fact Default : No. Will also delete data older than retention days from parent fact table.
  • Skip-confirmation-prompt. Default: No. If No, the script will prompt for confirmation before deleting data from parent fact. Set to Yes if the purge script is automated.

This command interrogates the "childfactables" table in the "analytics" schema to determine which raw data partitions cover the dates for which data pruning is to be performed, then drops those tables. Once the tables are dropped, the entries in "childfactables" related to those partitions are deleted.

Childfactables are daily-partitioned fact data. Every day new partitions are created and data gets ingested into the daily partitioned tables. So at a later point in time, when the old fact data will not be required, you can purge the respective childfactables.

The script has the following options since version 4.51.00.00:

  • Delete-from-parent-fact Default : No. Will also delete data older than retention days from parent fact table.
  • Confirm-delete-from-parent-fact. Default: No. If No, the script will prompt for confirmation before deleting data from parent fact. Set to Yes if the purge script is automated.

Purge aggregate table data

Aggregate tables typically don't consume as much space as fact tables. However, if you want to purge old data from aggregate tables, run the following command:

/opt/apigee/apigee-service/bin/apigee-service apigee-postgresql agg-data-purge org_name env_name days_to_retain [confirm_data_deletion-Y/N]

where confirm_data_deletion is an optional parameter to prompt for confirmation. The default value is N.