(5.2.4 to 6.0.9) Database Upgrade Failed

[mckinnonsc]

I'm trying to upgrade Zabbix to 6.0.9 from 5.2.4 and I'm receiving this error

[Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  there is no unique constraint matching given keys for referenced table "problem"

Versions (pre → post upgrade)

• Ubuntu 20.04 LTS
• Zabbix 5.2.4 → 6.0.9
• PostgreSQL 12.12 → 14.5-1
• TimescaleDB 1.7.4 → 2.7.0

Process to followed to upgrade

1. Installed PostgreSQL 14.5
2. Upgraded TimescaleDB using the instructions Upgrade TimescaleDB for both pgsql 12 & 14.
3. Perform a pg_clusterupgrade using --method=upgrade and --link, linking my data dirs /mnt/Data/postgresql/12/main and /mnt/Data/postgresql14/main
4. Upgraded to Zabbix 6.0.9 using the instructions Upgrade Procedure
5. Following Upgrade notes for 6.0.0 I need to perform a manual upgrade of history tables to primary keys for PostgreSQL + TimescaleDB.
6. Start zabbix-server, and database upgrade fails.

One thing to note is I removed the csv files located in /tmp/ that are generated by the manual primary key upgrade, before starting zabbix as the disk space is limited, are these files needed for the database upgrade beyond the manual upgrade for primary keys?

zabbix_server.log

71078:20221015:113759.199 Starting Zabbix Server. Zabbix 6.0.9 (revision 64721203c07).
 71078:20221015:113759.199 ****** Enabled features ******
 71078:20221015:113759.199 SNMP monitoring:           YES
 71078:20221015:113759.199 IPMI monitoring:           YES
 71078:20221015:113759.199 Web monitoring:            YES
 71078:20221015:113759.199 VMware monitoring:         YES
 71078:20221015:113759.200 SMTP authentication:       YES
 71078:20221015:113759.200 ODBC:                      YES
 71078:20221015:113759.200 SSH support:               YES
 71078:20221015:113759.200 IPv6 support:              YES
 71078:20221015:113759.200 TLS support:               YES
 71078:20221015:113759.200 ******************************
 71078:20221015:113759.200 using configuration file: /etc/zabbix/zabbix_server.conf
 71078:20221015:113759.316 current database version (mandatory/optional): 05050006/05050006
 71078:20221015:113759.316 required mandatory version: 06000000
 71078:20221015:113759.316 optional patches were found
 71078:20221015:113759.316 starting automatic database upgrade
 71078:20221015:113759.317 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  there is no unique constraint matching given keys for referenced table "problem"
 [alter table service_problem add constraint c_service_problem_1 foreign key (eventid) references problem (eventid) on delete cascade]
 71078:20221015:113759.318 database upgrade failed​

[Markku]

I have no knowledge about using PostgreSQL with Zabbix so cannot help you directly, but this got my attention:

Zabbix 5.2.4 → 6.0.9

current database version (mandatory/optional): 05050006/05050006

How is that possible? The original database version looks something like 6.0beta instead of 5.2. Is there something that has been done manually to the database?

Markku

[mckinnonsc]

I have no knowledge about using PostgreSQL with Zabbix so cannot help you directly, but this got my attention:





How is that possible? The original database version looks something like 6.0beta instead of 5.2. Is there something that has been done manually to the database?

Markku

Nothing beyond the steps I listed above. I checked the original VM (I'm use a clone of the Zabbix VM to trial this upgrade) is reporting this version.

current database version (mandatory/optional): 05020000/05020000