Re-Index OpManager POSTGRes Database

Re-Index OpManager POSTGRes Database

Query to check table size

Log into the server GUI and go to the database lookup page "Support>DB Query". Execute the following commands and export the results.
SELECT relname as "Table",pg_size_pretty(pg_total_relation_size(relid)) As "Total Size", pg_size_pretty(pg_table_size(relid)) as "Table Size", pg_size_pretty(pg_indexes_size(relid)) as "Index Size" FROM pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc limit 30;

Stop OpManager and postgres

DO NOT LOGIN AS ROOT
      sudo su
/etc/init.d/OpManagerServer stop

kill postgres if required
ps -ef |grep post
killall postgres

Move logs and zip

mv logs/ DATE_STAMP-logs

tar -cvf DATE_STAMP-logs.tar /opt/ManageEngine/OpManagerProbe/logs/*
gzip DATE_STAMP-logs.tar

Start access to postgres

cd /opt/ManageEngine/OpManagerProbe/bin/
./startPgSQL.sh
cd /opt/ManageEngine/OpManagerProbe/psql/bin/
psql -U postgres -h127.0.0.1 -p13308 ProbeDB


Re-index POSTGres tables

reindex all tables

reindex database "ProbeDB";

clean all database tables

clears or removes space left over so it can be re-written to. Space ocupied by deleted values
vacuum full verbose;

re-index polleddata only (Not required if previous steps completed)

To reindex polled data only run the following commands.
reindex table "polleddata";
polling data store

clean tables

vacuum verbose "polleddata";

stop postgres

cd /opt/ManageEngine/OpManagerProbe/bin
./stopPgSQL.sh


Collect databse information post re_indexing

Query to check table size

Log into the server GUI and go to the database lookup page "Support>DB Query". Execute the following commands and export the results.
SELECT relname as "Table",pg_size_pretty(pg_total_relation_size(relid)) As "Total Size", pg_size_pretty(pg_table_size(relid)) as "Table Size", pg_size_pretty(pg_indexes_size(relid)) as "Index Size" FROM pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc limit 30;

Compare the results from the first query run before Re-Indexing the database.

Log into the server GUI and go to the database lookup page "Support>DB Query". Execute the following commands and export the results for Manage Engine to review if required.
select * from datacollectionrate order by collectiontime desc limit 200;
select * from Metatable where lookuptablename like 'Event'; 
select * from DatabaseMaintenance; 
select * from PushNotificationDevices; 
select count(*) from Alert; 

    • Related Articles

    • Accessing OpManager PGSQL Database

      INTRODUCTION OpManager tool relies on PostgreSQL (PGSQL) database for data storage. This document describes how to access the database to run customized reports METHODS OF ACCESS The PGSQL database can be accessed locally (or by using SSH session) ...

    • SQL Database query for OpManager

      SQL Database query for OpManager  OpManager allows searches for database information, while logged into the server there is a link at the top right to various links for help and searches. The image that looks like headphones is the link to support ...

    • Opmanager is attempting to contact Zoho Networks and SMTP

      Introduction A customer noted that the OpManager Probe server was attempting to send data to Manage Engine which was in violation of the Firewall rules that they had on their IP network. Settings that may need to be changed to stop automated data ...

    • How to apply OpManager PPM Update and com.zip Patches

        Table of Contents Overview.. 2 Prep and file transfer. 2 Release Notes. 2 UpDate Manager for PPM... 2 Central Server upgrade. 4 Update files. 5 Discover Properties. 5 Edit list of Traps for auto-discovery. 7 Hardware Info in inventory. 7 To Enable ...

    • OpManager Trap Processor Config

      OpManager Trap Processor Config Trap processor can be edited to display a number of labels and information based on the markup language and variables (VarBinds) https://pitstop.manageengine.com/portal/kb/articles/how-to-configure-trap-message Changes ...