Cleanup Postgres DB

  • Document ID:KM03709684
  • Creation Date:16-Sep-2020
  • Modified Date:16-Sep-2020
    • Micro Focus Products:
      operations bridge reporter 10.30

This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.

Summary

Cleanup Postgres DB

Error

PostgresDB becomes slow and affects streams and Admin Console

Fix

1. Stop the COllection, Orchestration services

2. Please run this below command, this will increase the potgres performance
 $PMDB_HOME/bin/mgmtsqlexecutor -sqlscript $PMDB_HOME/scripts/vacuum_postgres.sql -logfile $PMDB_HOME/temp/postgresql_vacuum.log

3. Log into the potgres DB and run the below steps

delete  from job_stream_rt stream where stream.state='FINISHED' and stream.END_TIME < now()- Interval '? HOUR'

delete  from job_stream_rt stream where stream.state='FINISHED' and stream.END_TIME < now()- Interval '360 HOUR'

 DELETE from job_stream_step_rt where md_batch_id in (select step.md_batch_id FROM job_stream_step_rt step, job_stream_rt stream WHERE stream.md_batch_id = step.md_batch_id AND stream.state='FINISHED' AND stream.END_TIME < now()-Interval ? HOUR)

DELETE from job_stream_step_rt where md_batch_id in (select step.md_batch_id FROM job_stream_step_rt step, job_stream_rt stream WHERE stream.md_batch_id = step.md_batch_id AND stream.state='FINISHED' AND stream.END_TIME < now()-Interval 360 HOUR)

delete from execution_log where md_process_id not in(select md_process_id from job_stream_step_rt)

delete  from long_message where long_message_id not in(select long_message_id from execution_log)

4. After this restart the collection and Orchestration Services