Host is 14.0-RELEASE-p3. Installed: icinga2-2.14.0_2 net-mgmt/icinga2 icingadb-1.1.1_6 net-mgmt/icingadb Backend ist PostgreSQL 15 Server on another db-host, carrying a database named "icingadb" and accessible by the host in question. Application of the icingadb-schema provided via /usr/local/share/examples/icingadb/schema/pgsql/schema.sql (psql -f ...) results in some errors, like: ERROR: relation "history" does not exist ERROR: relation "idx_history_event_time" does not exist ERROR: relation "idx_history_host_service_id" does not exist When starting service icingadb start service starts and stops immediately with dropping an error: icingadb[65506]: 2024-01-12T10:43:16.972Z FATAL icingadb pq: relation "history" does not exist can't perform "INSERT INTO \"history\" (\"environment_id\", \"host_id\", \"state_history_id\", \"endpoint_id\", \"object_type\", \"service_id\", \"event_type\", \"id\", \"event_time\") VALUES (:environment_id,:host_id,:state_history_id,:endpoint_id,:object_type,:service_id,:event_type,:id,:event_time) ON CONFLICT ON CONSTRAINT pk_history DO UPDATE SET \"id\" = EXCLUDED.\"id\"" I'm out of ideas here. The problem occurs even on a fresh installation were no IDO is supposed to be installed. Something is broken.
This is more application support than a package/port bug. Have you connected directly to the database to determine it is correctly populated? i.e. that the history table exists?
Yes, I connected to the DB server and the corresponding DB (for simplicity, plainly "icingadb"). I've created the scheme of the DB with the schea.sl file provided by the port: /usr/local/share/examples/schema/pgsql/schema.sql I also tried to "upgrade" the existing icinga_ido DB by application of /usr/local/share/examples/schema/mysql/upgrades/1.1.1.sql I see no way how I should provide the scheme of the DB otherwise. In both cases, the schema seems incomplete. Checking on the DB server itself and connecting to the DB, a simple "\d" reveals only sla_history_xxxx state_history as tables with a designation "history". I tried to follow the installation recommendations given by the vendors, came that far having the appropriate databases up and running - but now icingadb fails complaining about missing relation "history" amongst other missing relations. And for the record: The problem seems to persist even when setting up a fresh instance (any way, we use the Icinga Director, if this has any influence - which I guess has not).
I got it! Checking the log output reveals that an extension named "citext" could not be created. Postgresql main page states this is part of the core - but the FreeBSD port "databases/postgresqlXX-server" doesn't contain it! Solution: Extension CITEXT is part of the outsourced port databases/postgresqlXX-contrib" Whatever postgresql database server is installed, postgresqlXX-contrib seems to be a requisite for port icingadb.
A commit in branch main references this bug: URL: https://cgit.FreeBSD.org/ports/commit/?id=f62e56bfe5cfa160f502774d03fb76e15192141d commit f62e56bfe5cfa160f502774d03fb76e15192141d Author: Dan Langille <dvl@FreeBSD.org> AuthorDate: 2024-01-15 13:24:20 +0000 Commit: Dan Langille <dvl@FreeBSD.org> CommitDate: 2024-01-15 13:25:34 +0000 net-mgmt/icingadb: add missing dependency The contrib component is required. PR: 276272 net-mgmt/icingadb/Makefile | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-)
Good debugging, thank you. I hope there were some errors when examples/schema/pgsql/schema.sql was applied without contrib installed. If not, that schema file can use improvements. Looking at https://www.postgresql.org/docs/16/citext.html, that page is labelled "Appendix F. Additional Supplied Modules and Extensions" - citext is indeed a contrib. Please get back to me and see if this update works.
A commit in branch main references this bug: URL: https://cgit.FreeBSD.org/ports/commit/?id=42714c697fa42b2ec70641b1e1b38a1e5c589972 commit 42714c697fa42b2ec70641b1e1b38a1e5c589972 Author: Dan Langille <dvl@FreeBSD.org> AuthorDate: 2024-01-15 13:53:38 +0000 Commit: Dan Langille <dvl@FreeBSD.org> CommitDate: 2024-01-15 13:55:20 +0000 net-mgmt/icingadb: add pkg-message A PostgreSQL extension needs to be created before using this package. PR: 276272 net-mgmt/icingadb/Makefile | 2 +- net-mgmt/icingadb/pkg-message (new) | 11 +++++++++++ 2 files changed, 12 insertions(+), 1 deletion(-)
This puppet merge request supports installing contrib is required: https://github.com/Icinga/puppet-icinga/pull/82 NOTE: you'll also need to do: create extension if not exists citext; re: https://www.postgresql.org/docs/16/contrib.html I have added that to pkg-message
(In reply to Dan Langille from comment #7) Of course, sorry for my sloppyness - I had to do that on the appropriate database to make that work.
(In reply to O. Hartmann from comment #8) No apology expected. Your reporting will help others.