Zabbix Upgrade issue with Postgres Database

[JohnnySSH]

Hi,

I'm trying to upgrade my Zabbix install and keep running into this:

 83129:20161114:184811.930 using configuration file: /usr/local/etc/zabbix32/zabbix_server.conf
 83129:20161114:184811.992 current database version (mandatory/optional): 02030006/02030006
 83129:20161114:184811.992 required mandatory version: 03020000
 83129:20161114:184811.992 starting automatic database upgrade
 83129:20161114:184811.999 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  there is no unique constraint matching given keys for referenced table "triggers"
 [alter table trigger_discovery add constraint c_trigger_discovery_1 foreign key (triggerid) references triggers (triggerid) on delete cascade]
 83129:20161114:184811.999 database upgrade failed

I run Postgresql 9.3 on a standalone DB server and trying to upgrade from ZBX version 2.2 to 3.2.

What can I do to resolve this issue?

Checking the table lists both tables exist. I'm not sure what the issue actually is as "triggerid" exists in both tables mentioned in the psql command.


Would anyone have any suggestions or solution?

Many Thanks!

[JohnnySSH]

I think I fixed the issue.....??

The first thing was to install the Zabbix 3.2 database schema into a new DB. - zabbix_temp

Then cross compare the "triggers" and "trigger_discovery" tables.


I used the tool pgAdmin III to do this as I needed a graphical representation of what was going on.

It turns out that my zabbix db was missing "primary constraints" and "keys" which got created in the zabbix_temp db through the "schema.sql" file.

Using pgAdmin to show the query of how the zabbix_temp constraints and keys were created I used these to recreate them on my zabbix db. - This ensured that they were compliant with the ZBX schema.

It turns out due to lack of UNIQUE Constraints that some trigger ID's had become duplicated.

Using this:

https://wiki.postgresql.org/wiki/Deleting_duplicates

DELETE FROM tablename
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                     FROM tablename) t
              WHERE t.rnum > 1);

setting "id" to "triggerid" and matching the first 3 columns in the db: triggerid - column 1 , expression - column 2 , description - column 3 ; and tablename set to "triggers"

I was able to remove the duplicate values.

After going ahead with creating the constraints and keys I re-ran the Zabbix startup service and checked the log file.

It kept complaining about certain tables "already existing", so the answer was to manually delete each table that was complained about in the log file; of course I needed to re-run the service multiple times and keep removing tables until the complaints stopped and then let the upgrade go through.


Hopefully everything is ZBX compliant now so in future upgrades there shouldn't be any more issues.....??

At least the service starts now and all the data seems to still be in the DB.