Any suggestions on how to backup history level

Hi,

Our Activiti database is getting huge, Acitiviti engine History log level is set to Full, we do want to keep it like that for tracking purposes for some time. We are seeking a solution to backup the history data from the database directly and deleting those records from the database afterwards. Are there any suggestions on how to do it neatly, any script might be well appreciated.

Thank you,

Hi Dan,

The history tables don’t have any foreign keys, so there’s all flexibility to delete any history data you want. One approach could be to do a database backup and then delete all history info before a certain date (execute delete queries on the history tables). Another option is to move the old history information to another database with for example a piece of Java code. There are no out-of-the-box scripts available yet, although we have been thinking about such a feature for some time now.

Best regards,

Tijs

We do exactly the same thing as Tijs’s suggestion.:grin:
Set up an automatic job to backup historic data before deletion.

Thank you Tijs and 6Pker for your answers.

I still have another question for the following Scenario: Say you have a parent process instance which continue to run, even after its child processes terminated long time ago. The question: does removing history of terminated children processes impact the still running parent. process I assume no, but one to confirm from the experts.

Thank you,

Dan

I am sharing my SQL scripts to clean History tables. Please feel free to comment and advise. I select for backup and delete only data related to completed process instances. Thank you,


--------- Estimate the number of historic rows to backup ----------------------

SELECT ‘ACT_HI_ATTACHMENT’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_ATTACHMENT A
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)

UNION
SELECT ‘ACT_HI_COMMENT’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_COMMENT c
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND c.proc_inst_id_ = p.proc_inst_id_
)

UNION
SELECT ‘ACT_HI_IDENTITYLINK’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_IDENTITYLINK i
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND i.proc_inst_id_ = p.proc_inst_id_
)

UNION
SELECT ‘ACT_HI_DETAIL’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_DETAIL d
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND d.proc_inst_id_ = p.proc_inst_id_
)

UNION
SELECT ‘ACT_HI_VARINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_VARINST v
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND v.proc_inst_id_ = p.proc_inst_id_
)

UNION
SELECT ‘ACT_HI_TASKINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_TASKINST t
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND t.proc_inst_id_ = p.proc_inst_id_
)

UNION
SELECT ‘ACT_HI_ACTINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_ACTINST a
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)

UNION
SELECT ‘ACT_HI_PROCINST’ as “Table”, count(*) as "Rows to backup"
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
;


------- SELECT FOR BACKUP HISTORIC TABLE DATA FOR ALL COMPLETED PROCESSES -----

SELECT *
FROM ACT_HI_ATTACHMENT A
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;

SELECT *
FROM ACT_HI_COMMENT c
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND c.proc_inst_id_ = p.proc_inst_id_
)
;

SELECT *
FROM ACT_HI_IDENTITYLINK i
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND i.proc_inst_id_ = p.proc_inst_id_
)
;

SELECT *
FROM ACT_HI_DETAIL d
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND d.proc_inst_id_ = p.proc_inst_id_
)
;

SELECT *
FROM ACT_HI_VARINST v
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND v.proc_inst_id_ = p.proc_inst_id_
)
;

SELECT *
FROM ACT_HI_TASKINST t
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND t.proc_inst_id_ = p.proc_inst_id_
)
;

SELECT *
FROM ACT_HI_ACTINST a
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;

SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
;


----------------------- DELETE HISTORIC DATA -----------------------------------

DELETE
FROM ACT_HI_ATTACHMENT A
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;

DELETE
FROM ACT_HI_COMMENT c
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND c.proc_inst_id_ = p.proc_inst_id_
)
;

DELETE
FROM ACT_HI_IDENTITYLINK i
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND i.proc_inst_id_ = p.proc_inst_id_
)
;

DELETE
FROM ACT_HI_DETAIL d
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND d.proc_inst_id_ = p.proc_inst_id_
)
;

DELETE
FROM ACT_HI_VARINST v
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND v.proc_inst_id_ = p.proc_inst_id_
)
;

DELETE
FROM ACT_HI_TASKINST t
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND t.proc_inst_id_ = p.proc_inst_id_
)
;

DELETE
FROM ACT_HI_ACTINST a
WHERE EXISTS (
SELECT *
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
AND a.proc_inst_id_ = p.proc_inst_id_
)
;

DELETE
FROM ACT_HI_PROCINST p
WHERE p.end_time_ < to_timestamp(‘2017-03-25 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
;

1 Like