PostgreSQL problem (duplicated key and connection problem reported)

Icinga 2
, ,
1
  • Version used (icinga2 --version)
    icinga2 --version
    icinga2 - The Icinga 2 network monitoring daemon (version: 2.12.3)

Copyright (c) 2012-2021 Icinga GmbH (https://icinga.com/)
License GPLv2+: GNU GPL version 2 or later http://gnu.org/licenses/gpl2.html
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

System information:
Platform: CentOS Linux
Platform version: 7 (Core)
Kernel: Linux
Kernel version: 3.10.0-1127.8.2.el7.x86_64
Architecture: x86_64

Build information:
Compiler: GNU 4.8.5
Build host: runner-hh8q3bz2-project-322-concurrent-0
OpenSSL version: OpenSSL 1.0.2k-fips 26 Jan 2017

Application information:

General paths:
Config directory: /etc/icinga2
Data directory: /var/lib/icinga2
Log directory: /var/log/icinga2
Cache directory: /var/cache/icinga2
Spool directory: /var/spool/icinga2
Run directory: /run/icinga2

Old paths (deprecated):
Installation root: /usr
Sysconf directory: /etc
Run directory (base): /run
Local state directory: /var

Internal paths:
Package data directory: /usr/share/icinga2
State path: /var/lib/icinga2/icinga2.state
Modified attributes path: /var/lib/icinga2/modified-attributes.conf
Objects path: /var/cache/icinga2/icinga2.debug
Vars path: /var/cache/icinga2/icinga2.vars
PID path: /run/icinga2/icinga2.pid`

  • Operating System and version
    cat /etc/redhat-release CentOS Linux release 7.8.2003 (Core)

  • Enabled features (icinga2 feature list)

Disabled features: compatlog elasticsearch graphite icingadb livestatus opentsdb perfdata
Enabled features: api checker command debuglog gelf ido-pgsql influxdb mainlog notification statusdata syslog
  • Icinga Web 2 version and modules (System - About)
Name Version
april 1.0.4
audit 1.0.0
businessprocess 2.2.0
director 1.7.2
doc 2.7.3
generictts 2.0.0
globe 1.0.4
grafana 1.3.6
idoreports 0.9.1
incubator 0.5.0
ipl v0.4.0
jira 1.0.1
map 1.1.0
monitoring 2.7.3
particle 1.0.0
reactbundle 0.7.0
reporting 0.9.2
spring 1.0.0
  • Config validation (icinga2 daemon -C)
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 NotificationComponent.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 SyslogLogger.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 267 Hosts.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 8 Downtimes.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 4 NotificationCommands.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 2 FileLoggers.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 39 Comments.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 9697 Notifications.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 IcingaApplication.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 172 HostGroups.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 EventCommand.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 135 Dependencies.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 CheckerComponent.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 188 Zones.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 GelfWriter.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 StatusDataWriter.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 186 Endpoints.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 ExternalCommandListener.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 10 ApiUsers.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 ApiListener.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 259 CheckCommands.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 InfluxdbWriter.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 1 IdoPgsqlConnection.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 4 TimePeriods.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 14 UserGroups.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 20 Users.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 4189 Services.
[2021-04-22 09:31:23 +0200] information/ConfigItem: Instantiated 8 ServiceGroups.
[2021-04-22 09:31:23 +0200] information/ScriptGlobal: Dumping variables to file '/var/cache/icinga2/icinga2.vars'
[2021-04-22 09:31:23 +0200] information/cli: Finished validating the configuration file(s).
  • If you run multiple Icinga 2 instances, the zones.conf file (or icinga2 object list --type Endpoint and icinga2 object list --type Zone) from all affected nodes
    One icinga instance.

The icinga2 log full of Connection problem, however de db is functional:

postgres version is:
postgres (PostgreSQL) 13.2

[2021-04-22 09:21:27 +0200] information/DbConnection: Resuming IDO connection: ido-pgsql
[2021-04-22 09:21:28 +0200] information/IdoPgsqlConnection: PGSQL IDO instance id: 1 (schema version: '1.14.3')
[2021-04-22 09:21:29 +0200] critical/IdoPgsqlConnection: Error "ERROR:  duplicate key value violates unique constraint "uq_hoststatus"
[2021-04-22 09:21:29 +0200] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2021-04-22 09:21:29 +0200] critical/IdoPgsqlConnection: Error "no connection to the server
[2021-04-22 09:21:29 +0200] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2021-04-22 09:21:37 +0200] information/IdoPgsqlConnection: PGSQL IDO instance id: 1 (schema version: '1.14.3')
[2021-04-22 09:21:38 +0200] critical/IdoPgsqlConnection: Error "ERROR:  duplicate key value violates unique constraint "uq_hoststatus"
[2021-04-22 09:21:38 +0200] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2021-04-22 09:21:38 +0200] critical/IdoPgsqlConnection: Error "no connection to the server
[2021-04-22 09:21:38 +0200] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2021-04-22 09:21:47 +0200] information/IdoPgsqlConnection: PGSQL IDO instance id: 1 (schema version: '1.14.3')
[2021-04-22 09:21:48 +0200] critical/IdoPgsqlConnection: Error "ERROR:  duplicate key value violates unique constraint "uq_hoststatus"
[2021-04-22 09:21:48 +0200] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2021-04-22 09:21:48 +0200] critical/IdoPgsqlConnection: Error "no connection to the server

icinga2.log file problems:

[2021-04-22 08:50:34 +0200] information/DbConnection: Pausing IDO connection: ido-pgsql
[2021-04-22 08:50:34 +0200] information/IdoPgsqlConnection: 'ido-pgsql' paused.
[2021-04-22 08:50:34 +0200] information/DbConnection: 'ido-pgsql' stopped.
[2021-04-22 09:21:27 +0200] information/DbConnection: 'ido-pgsql' started.
[2021-04-22 09:21:27 +0200] information/IdoPgsqlConnection: 'ido-pgsql' resumed.
[2021-04-22 09:21:27 +0200] information/DbConnection: Resuming IDO connection: ido-pgsql
[2021-04-22 09:21:28 +0200] information/IdoPgsqlConnection: PGSQL IDO instance id: 1 (schema version: '1.14.3')
[2021-04-22 09:21:29 +0200] critical/IdoPgsqlConnection: Error "ERROR:  duplicate key value violates unique constraint "uq_hoststatus"
[2021-04-22 09:21:29 +0200] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2021-04-22 09:21:29 +0200] critical/IdoPgsqlConnection: Error "no connection to the server
[2021-04-22 09:21:29 +0200] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2021-04-22 09:21:37 +0200] information/IdoPgsqlConnection: PGSQL IDO instance id: 1 (schema version: '1.14.3')

Do you have idea what causes duplicate key problem and database exception problem?

2

What I did here:
On the icinga DB deleted all the status infomation:

#icinga db

delete from icinga_servicestatus;

delete from icinga_hoststatus;

I hope this is the correct resolution of the problem.

3

You’ll find the list of historical tables here. But do not truncate icinga_dbversion although is is listed on that page.

1 Like
4

Hello there :slight_smile:
I edited your post a little for readability and changes some single backticks ` to tripple ones ``` so the formatting recognises multiline.
I would recommend reading through our formatting guidelines for future reference :slight_smile:

1 Like