From 964bdf8eec0fe2687e86f1219916c22faf3609c9 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 21 Jul 2016 17:07:34 -0400 Subject: [PATCH] JBAS-1501 Copy audit table maintenance scripts New DB function for deleting rows from auditor.asset_copy_history plus a script we can run from CRON for nightly batches. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/audit-table-maint.sql | 99 ++++++++++++++++++++++ KCLS/sql/schema/revert/audit-table-maint.sql | 8 ++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/audit-table-maint.sql | 7 ++ .../utility-scripts/purge_auditor/purge-auditor.sh | 17 ++++ .../purge_auditor/purge-auditor.sql | 16 ++++ 6 files changed, 148 insertions(+) create mode 100644 KCLS/sql/schema/deploy/audit-table-maint.sql create mode 100644 KCLS/sql/schema/revert/audit-table-maint.sql create mode 100644 KCLS/sql/schema/verify/audit-table-maint.sql create mode 100755 KCLS/utility-scripts/purge_auditor/purge-auditor.sh create mode 100644 KCLS/utility-scripts/purge_auditor/purge-auditor.sql diff --git a/KCLS/sql/schema/deploy/audit-table-maint.sql b/KCLS/sql/schema/deploy/audit-table-maint.sql new file mode 100644 index 0000000000..38f482dd84 --- /dev/null +++ b/KCLS/sql/schema/deploy/audit-table-maint.sql @@ -0,0 +1,99 @@ +-- Deploy kcls-evergreen:audit-table-maint to pg +-- requires: payflow-hosted-org-settings + +BEGIN; + +CREATE OR REPLACE FUNCTION auditor.purge_audit_data ( + keep_cur_entries INTEGER, -- num entries to keep for current interval + keep_old_entries INTEGER, -- num entries to keep for previous interval + pivot_age INTERVAL, -- what defines current vs previous interval (e.g. 1 year) + drop_age INTERVAL, -- delete all entries older than this. (e.g. 3 years) + run_duration INTERVAL -- exit the function once it's run this long. (e.g. 2 hours) +) RETURNS VOID AS $FUNC$ +DECLARE + aid BIGINT; + del_counter BIGINT DEFAULT 0; + start_time TIMESTAMPTZ; +BEGIN + + start_time := CLOCK_TIMESTAMP(); + + DELETE FROM auditor.asset_copy_history + WHERE audit_time < (NOW() - drop_age); + + IF CLOCK_TIMESTAMP() - start_time > run_duration THEN + RAISE NOTICE + 'Exiting auditor.purge_audit_data() on max duration %', + run_duration; + RETURN; + END IF; + + RAISE NOTICE 'Done with old entry deletes. Starting current deletes.'; + + -- loop over auditor rows that constitute the 16th or older + -- row per copy and delete each. + FOR aid IN + WITH ranked_entries_per_copy AS ( + SELECT audit_id, + ROW_NUMBER() OVER ( + PARTITION BY id + ORDER BY audit_time DESC, audit_id DESC + ) AS position + FROM auditor.asset_copy_history + ) SELECT ranked.audit_id + FROM ranked_entries_per_copy ranked + WHERE ranked.position > keep_cur_entries + LOOP + + DELETE FROM auditor.asset_copy_history WHERE audit_id = aid; + del_counter := del_counter + 1; + + IF (del_counter % 10000) = 0 THEN + RAISE NOTICE 'Deleted % auditor rows', del_counter; + END IF; + + IF CLOCK_TIMESTAMP() - start_time > run_duration THEN + RAISE NOTICE + 'Exiting auditor.purge_audit_data() on max duration %', + run_duration; + RETURN; + END IF; + END LOOP; + + RAISE NOTICE 'Done with current entry maintenence; cleaning older entries'; + + -- loop over auditor rows older than pivot_age that constitute + -- the 4th or older row per copy and delete them. + FOR aid IN + WITH ranked_entries_per_copy AS ( + SELECT audit_id, + ROW_NUMBER() OVER ( + PARTITION BY id + ORDER BY audit_time DESC, audit_id DESC + ) AS position + FROM auditor.asset_copy_history + WHERE audit_time < (NOW() - pivot_age) + ) SELECT ranked.audit_id + FROM ranked_entries_per_copy ranked + WHERE ranked.position > keep_old_entries + LOOP + + DELETE FROM auditor.asset_copy_history WHERE audit_id = aid; + del_counter := del_counter + 1; + + IF (del_counter % 10000) = 0 THEN + RAISE NOTICE 'Deleted % auditor rows', del_counter; + END IF; + + IF CLOCK_TIMESTAMP() - start_time > run_duration THEN + RAISE NOTICE + 'Exiting auditor.purge_audit_data() on max duration %', + run_duration; + RETURN; + END IF; + END LOOP; +END; +$FUNC$ LANGUAGE PLPGSQL; + +COMMIT; + diff --git a/KCLS/sql/schema/revert/audit-table-maint.sql b/KCLS/sql/schema/revert/audit-table-maint.sql new file mode 100644 index 0000000000..acf6d09414 --- /dev/null +++ b/KCLS/sql/schema/revert/audit-table-maint.sql @@ -0,0 +1,8 @@ +-- Revert kcls-evergreen:audit-table-maint from pg + +BEGIN; + +DROP FUNCTION auditor.purge_audit_data( + INTEGER, INTEGER, INTERVAL, INTERVAL, INTERVAL); + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index cb65197949..d6b36b049b 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -29,3 +29,4 @@ purge-user-activity [sip-activity-types] 2016-04-29T17:07:46Z Bill Erickson # Vandelay authority import sets edit[or|_date] payflow-hosted-org-settings [vand-auth-edit-date] 2016-07-06T18:39:40Z Bill Erickson # PayflowPro Hosted Pages org unit settings sip-act-type-freegalsip [payflow-hosted-org-settings] 2016-08-19T20:24:01Z Bill Erickson # User activity type for freegalsip login +audit-table-maint [payflow-hosted-org-settings] 2016-07-22T14:49:08Z Bill Erickson # Audit table cleanup functions diff --git a/KCLS/sql/schema/verify/audit-table-maint.sql b/KCLS/sql/schema/verify/audit-table-maint.sql new file mode 100644 index 0000000000..8556645ee4 --- /dev/null +++ b/KCLS/sql/schema/verify/audit-table-maint.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:audit-table-maint on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/KCLS/utility-scripts/purge_auditor/purge-auditor.sh b/KCLS/utility-scripts/purge_auditor/purge-auditor.sh new file mode 100755 index 0000000000..30e36d8c54 --- /dev/null +++ b/KCLS/utility-scripts/purge_auditor/purge-auditor.sh @@ -0,0 +1,17 @@ +#!/bin/bash + +RUN_DURATION="1 hour" +KEEP_CUR_ENTRIES="15" +KEEP_OLD_ENTRIES="3" +PIVOT_AGE="1 year" +DROP_AGE="3 years" + +psql -U evergreen \ + --set keep_cur_entries="'$KEEP_CUR_ENTRIES'" \ + --set keep_old_entries="'$KEEP_OLD_ENTRIES'" \ + --set pivot_age="'$PIVOT_AGE'" \ + --set drop_age="'$DROP_AGE'" \ + --set run_duration="'$RUN_DURATION'" \ + -f purge-auditor.sql + + diff --git a/KCLS/utility-scripts/purge_auditor/purge-auditor.sql b/KCLS/utility-scripts/purge_auditor/purge-auditor.sql new file mode 100644 index 0000000000..d23469ccef --- /dev/null +++ b/KCLS/utility-scripts/purge_auditor/purge-auditor.sql @@ -0,0 +1,16 @@ +-- TODO: Transaction wrapper is for testing only. Remove it for production. + +--BEGIN; + +SET STATEMENT_TIMEOUT = 0; + +SELECT auditor.purge_audit_data( + :keep_cur_entries::INTEGER, + :keep_old_entries::INTEGER, + :pivot_age::INTERVAL, + :drop_age::INTERVAL, + :run_duration::INTERVAL +); + +--ROLLBACK; +--COMMIT; -- 2.11.0