Re-Index OpManager Postgres Database

Re-Index OpManager Postgres Database

Query to check table size

Reach out to Manage Engine prior to proceeding with any changes to the database. All queries must be run as the 'admin' user. 

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
systemctl stop OpManager.service

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

Zip Logs

      zip -r DATE_STAMP-logs.zip /opt/ManageEngine/OpManagerProbe/logs/

Access the Database Server

To access the Probe database, navigate to the following folder:
/opt/ManageEngine/OpManagerProbe/pgsql/bin/
Enter the following command:
./psql -U dbuser -p13308 -h127.0.0.1 ProbeDB
Enter the password (must be obtained from Manage Engine)


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 occupied by deleted values
vacuum full verbose;

re-index polled data 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";

Collect database 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

    • [OpManager] Accessing the PGSQL Database

      Accessing the Database · Although uncommon, accessing the OpManager database may be required to pull specific data, apply changes, or to troubleshoot an issue as directed by Manage Engine. · To access the Central database, navigate to the following ...

    • SQL Database query for OpManager

      SQL Database query for OpManager Database queries can be run from the web UI to pull various information from the database. In addition, Manage Engine may provide queries and request the output when troubleshooting an issue. To run a query, click the ...

    • 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 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 ...

    • 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 ...