postgresql database patch 06030038 fails

[harribal]

All,

I'm trying to upgrade from Zabbix 6.2.9 to 7.0.X, without any intermediate upgrades, eg 6.2 -> 6.4 -> 7.0. I have not seen anywhere that this is required, but I may be wrong.
I have followed the upgrade procedure in https://www.zabbix.com/documentation.../packages/rhel.

Now when I'm starting the zabbix-server, it should apply the required patches, but I'm stuck in a loop where patch 06030038 fails to install. After a 10 sec pause the process starts and fails again. Rinse and repeat.
Please see the snippet from Zabbix_server.log below.

What should be done here to succeed with the db-upgrade? Please bear in mind that I'm almost totally noob with sql.

38497:20250626:162427.483 Starting Zabbix Server. Zabbix 7.0.15 (revision d72b831a577).
 ...
 38497:20250626:162427.483 using configuration file: /etc/zabbix/zabbix_server.conf
 38497:20250626:162427.525 current database version (mandatory/optional): 06030037/06030037
 38497:20250626:162427.525 required mandatory version: 07000000
 38497:20250626:162427.525 mandatory patches were found
 38497:20250626:162427.527 starting automatic database upgrade
 38497:20250626:162427.528 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  constraint "c_dchecks_1" of relation "dchecks" does not exist
 [alter table dchecks drop constraint c_dchecks_1]
 38497:20250626:162427.528 database upgrade failed on patch 06030038, exiting in 10 seconds
 38497:20250626:162437.528 Zabbix Server stopped. Zabbix 7.0.15 (revision d72b831a577).
^

PostgreSQL 13
timescaledb 2.15.3
rocky linux 8.10

[harribal]

Now I have installed new server with postgreSQL 15 and Zabbix 7. I did export the db from the old server and imported it onto the new.
At startup Zabbix-server still logs the same as above.

What does this error mean and how to fix it?

78602:20250702:100937.716 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  constraint "c_dchecks_1" of relation "dchecks" does not exist
 [alter table dchecks drop constraint c_dchecks_1]

[Markku]

The database version 06030037 indicates that you were using a version after 6.2.x but before 6.4.0, maybe alpha or beta version of 6.4. (Or it may be just an artifact of the otherwise incomplete database upgrade.)

Maybe try to upgrade (a copy of the database) to 6.4.x first, if it makes the database upgrade succeed. Basically the database seems to be in an unexpected state at the moment.

Markku

[harribal]

Thanks for the suggestion Markku

That did not solve the issue, though.

Does the table "dchecks" look like it should be?

zabbix=# \d dchecks;
                                     Table "public.dchecks"
        Column         |          Type           | Collation | Nullable |        Default
-----------------------+-------------------------+-----------+----------+------------------------
 dcheckid              | bigint                  |           | not null |
 druleid               | bigint                  |           | not null |
 type                  | integer                 |           | not null | 0
 key_                  | character varying(2048) |           | not null | ''::character varying
 snmp_community        | character varying(255)  |           | not null | ''::character varying
 ports                 | character varying(255)  |           | not null | '0'::character varying
 snmpv3_securityname   | character varying(64)   |           | not null | ''::character varying
 snmpv3_securitylevel  | integer                 |           | not null | 0
 snmpv3_authpassphrase | character varying(64)   |           | not null | ''::character varying
 snmpv3_privpassphrase | character varying(64)   |           | not null | ''::character varying
 uniq                  | integer                 |           | not null | 0
 snmpv3_authprotocol   | integer                 |           | not null | 0
 snmpv3_privprotocol   | integer                 |           | not null | 0
 snmpv3_contextname    | character varying(255)  |           | not null | ''::character varying
 host_source           | integer                 |           | not null | 1
 name_source           | integer                 |           | not null | 0
Indexes:
    "dchecks_1" btree (druleid, host_source, name_source)
Triggers:
    dchecks_delete BEFORE DELETE ON dchecks FOR EACH ROW EXECUTE FUNCTION changelog_dchecks_delete()
    dchecks_insert AFTER INSERT ON dchecks FOR EACH ROW EXECUTE FUNCTION changelog_dchecks_insert()
    dchecks_update AFTER UPDATE ON dchecks FOR EACH ROW EXECUTE FUNCTION changelog_dchecks_update()

What I understand from the error message above (in msg #2) the script tries to drop a constraint, but that is already missing/dropped. Is there a way to restore the constant, so it can be dropped later on?
I tried to run `ALTER TABLE ONLY dchecks ADD CONSTRAINT c_dchecks_1 FOREIGN KEY (druleid) REFERENCES drules (druleid);` in various combinations, but for error each time. Disclaimer: I do not know what I'm doing :-)

I also run a command I found that should list the constraints

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'

In the output there are no references to either constraint c_dchecks_1 nor table dchecks.

                         constraint_name                         |                    table_name                    |       column_name        |     foreign_table_name      |   foreign_column_name
-----------------------------------------------------------------+--------------------------------------------------+--------------------------+-----------------------------+--------------------------
 ...
 c_dashboard_user_2                                              | dashboard_user                                   | userid                   | users                       | userid
 c_dashboard_usrgrp_2                                            | dashboard_usrgrp                                 | usrgrpid                 | usrgrp                      | usrgrpid
 c_drules_1                                                      | drules                                           | proxy_hostid             | hosts                       | hostid
 c_event_suppress_2                                              | event_suppress                                   | maintenanceid            | maintenances                | maintenanceid
...

Edit: words

[Markku]

I don't know how the database should look like, but by looking at the Zabbix sources, here is apparently the database patch that fails:

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/libs/zbxdbupgrade/dbupgrade_6030.c?at=refs%2Fheads%2Frelease%2F7.0#325

Maybe looking further from there leads you understanding what went wrong.

Or, maybe installing first a new 6.2 install shows you how the database should look like.

Markku

[Markku]

Idea: increase the database version manually to get past the failing patch.

But, as mentioned, your database is in unexpected state, so there can be further implications.

Markku

[harribal]

Markku Thanks for your input.

I tried to increase the database version to just over ...38, but I just got new error messages. So, this route is out of question.

I'm really stuck here now with this. I would like to upgrade, but I'm out of means. I do have a clean 7.x install, and I could continue with that but that means I'll need to rebuild everything and I would lose all history. That is not tempting at all.