How to remove duplicates and inactive nodes from Postgres DB

  • Document ID:KM03773836
  • Creation Date:29-Jan-2021
  • Modified Date:29-Jan-2021
    • Micro Focus Products:
      operations bridge reporter 10.40

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

Summary

After we remove the duplicates/inactive nodes from Vertica DB, we need to clear them from Postgres DB as well, so that they can stop appearing in OBR Admin UI. We need to find the duplicates/inactive hosts in Operations Agent page in Admin UI. For example, usually one of them is loaded with short name and the other with full FQDN. After that, we should note the hosts with the older collection date and replace them with ('host1','host2')) in the Postgres queries.

Fix

delete from dwabc.pa_poller_map where pa_fk in ( select PA_ID from dwabc.dict_pa_ds where upper(hostname) in ('host1','host2'));

delete from dwabc.dict_pa_host_to_domain  where host_fk in ( select PA_ID from dwabc.dict_pa_ds where upper(hostname) in ('host1','host2'));

delete from dwabc.dict_pa_host_to_group  where host_fk in ( select PA_ID from dwabc.dict_pa_ds where upper(hostname) in ('host1','host2'));

delete from dwabc.dict_pa_ds where upper(hostname) in ('host1','host2');