I have been looking around in the icinga database, and there seems to be a lot of things that shouldn’t really be there; things that seem to be leftovers from earlier attempts. Is there a good way to clean up the database? I wouldn’t simply start deleting things based on my very incomplete understanding - but I might consider reinstalling the whole thing, since I would like to upgrade the underlying database software. What is the consensus on this?
Manually deleting things in IDO will make a mess and confuse Icingaweb. History can be set to autoclean, but services and hosts change to inactive as they may be referenced by historical data in other tables. Note some settings here:
https://icinga.com/docs/icinga2/latest/doc/09-object-types/#idopgsqlconnection
If you’re not worried about losing all of your historical data, you can rebuild your IDO database and Icinga will repopulate it.
Hi Blake, thx. I think I will have to rebuild the database at some any way - the historical data aren’t important at the moment, and I will have to upgrade postgres.
What I am trying to do is extract some information directly from the database, and the older services that no longer show up in the web GUI confuse things. How does the GUI know which ones to display? - I guess there must be a value in a column, but I notice there is a fair few of those.
there’s a boolean column on hosts and services called “is_active”. When you reload your Icinga configuration after removing something, that flips to 0 but retains it as historical data references it.
Sounds like some things may be way outof whack here, though. If you follow the logs for icinga and postgres, are you seeing any failed database insertions?
Hmm, I don’t find a column salled something similar to is_active in icinga_hosts or icinga_services. And it doesn’t look like our postgres is set up to do logging either. So, I’ll have to learn postgres as well, sigh.
===
Oh, I see, icinga_objects has that column
ha, yeah, sorry, was doing that from memory. You can correlate things there.
postgres logs will either be in /var/lib/pgsql/[versionmaybe]/data/log typically, or via journalctl. log_destination in postgresql.conf adjusts this.
You can purge all config and status tables (except the dbversion table!) via a full table truncate, then restart Icinga 2. It will populate everything from runtime in there. You may also delete everything from the objects table with a conditional where is_active=0. Keep in mind though, that the history tables may contain a reference - so better select which objects are not active anymore and purge the rows from the history tables first.
Keep in mind that deleting from history tables with many rows can take a while, with sql row level locking in place - don’t do that in production, but test and plan a maintenance window for this kind of operations.
Cheers,
Michael