Applying schema.sql is taking long time while upgrading 6.0 to 7.0

[hakanozanlagan]

hi, I am upgrading our zabbix servers from zabbix 6.04, pgsql 14 and timescaledb 2.6 to zabbix 7, pgsql 16 and timescaledb 2.15
I finished upgrade at a zabbix that have a small db and It is done quickly. but our prod env has 75GB database and it's taking very long time at applying schema sql.
my question is;
Is it neccesary to apply schema.sq at 6. step in below procedure? or do tou have any suggestion for better perofrmance

my procedure is below.
1. upgrade postgresql14 timescaledb from 2.06 to 2.15
3. upgrade zabbix to 6.4 and 7.03 in order. and start with "AllowUnsupportedDBVersions=1" parameter
4. install postgresql16 and timescaledb 2.15 on same machine
5. migrate old db to v16
6. apply schema.sql
7. start zabbix server

[tim.mooney]

Where are you getting this "schema.sql" file from? Can you provide a link to the documentation that indicated you should apply this file, or the download link for the schema.sql itself?

That's the typical name of the file that's used to create the initial structure (tables, views, indexes, etc.) in an empty database, so I'm a little concerned that you may be doing something you shouldn't be.

[hakanozanlagan]

Where are you getting this "schema.sql" file from? Can you provide a link to the documentation that indicated you should apply this file, or the download link for the schema.sql itself?

That's the typical name of the file that's used to create the initial structure (tables, views, indexes, etc.) in an empty database, so I'm a little concerned that you may be doing something you shouldn't be.

hi Tim,
I need to run schema.sql again because of timescaledb. (I attached my procedure to comment)
I used below link to create my upgrade procedure.

https://www.initmax.com/wiki/zabbix-update-to-the-latest-version-7-0-lts/

"Since our Zabbix database uses the TimescaleDB extension, we must also run an SQL script again to initialize hypertables. A new feature in version 7.0 is the hypertabled auditlog table."

[tim.mooney]

hi Tim,
I need to run schema.sql again because of timescaledb. (I attached my procedure to comment)

Ah, I understand now. I wasn't aware that TimescaleDB also uses a (different) file called "schema.sql":

The hypertable schema creation file for TimescaleDB has been moved to database/postgresql/timescaledb/schema.sql.

Unfortunately, my site isn't using timescaledb, so I'm not sure why that step would be taking a long time.

Hopefully someone else that is using timescaledb can offer some suggestions.

[cyber]

What strikes me as odd... If you do upgrade as described there in initMAX wiki.. you need all TS versions to match, then you do pg_upgrade (from 14 to 16 here)... Then you update you TS version also (all your data is already there, hypertables should be present etc...)...In this moment you have your old 6.0 DB with new "engine version (PG16)", they even kill the old version at this point.. You shoudl be able to run old version of zabbix at this point.. Now they do "zabbix upgrade" and need to rerun schema.sql to enable timescale? Shouldn't your first server (v7) startup create all needed stuff for schema upgrade and no need to rerun anything?

[hakanozanlagan]

In my test environment, I upgraded Database from 14 to 16, timescaledb from 1.6 to 1.15. I upgraded Zabbix from 6.03 to 7.04 and did not apply schema.sql. When I restarted Zabbix, I got the error below.

9272:20241010:131836.287 thread started
9227:20241010:131836.355 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: table "history_bin" is not a hypertable
[select set_integer_now_func('history_bin', 'zbx_ts_unix_now', true)]
9227:20241010:131836.355 Table "history_bin" is not a hypertable. Execute TimescaleDB configuration step as described in Zabbix documentation to upgrade schema.
9227:20241010:131836.364 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: unrecognized parameter namespace "timescaledb"
[alter table auditlog set (timescaledb.compress,timescaledb.compress_segment by='auditid',timescaledb.compress_orderby='clock')]
9227:20241010:131836.365 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: "auditlog" is not a hypertable or a continuous aggregate
[select add_compression_policy('auditlog', compress_created_before => interval '612000')]
9227:20241010:131836.365 failed to add compression policy to table 'auditlog'
9266:20241010:131836.376 server #49 started [report writer #1]
zz0.9ss6yw4eiodzz



-----I applied schema sql, and it's configured timescaledb

root@zbxupgdb01:~# cat schema.sql | sudo -u zabbix psql zabbix
NOTICE: function base36_decode(pg_catalog.varchar) does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
NOTICE: function cuid_timestamp(pg_catalog.varchar) does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
NOTICE: PostgreSQL version 16.4 (Ubuntu 16.4-1.pgdg22.04+2) is valid
NOTICE: TimescaleDB extension is detected
NOTICE: TimescaleDB version 2.15.3 is valid
NOTICE: table "history" is already a hypertable, skipping
NOTICE: table "history_uint" is already a hypertable, skipping
NOTICE: table "history_log" is already a hypertable, skipping
NOTICE: table "history_text" is already a hypertable, skipping
NOTICE: table "history_str" is already a hypertable, skipping
WARNING: column type "character varying" used for "auditid" does not follow best practices
HINT: Use datatype TEXT instead.
WARNING: column type "character varying" used for "username" does not follow best practices
HINT: Use datatype TEXT instead.
WARNING: column type "character varying" used for "ip" does not follow best practices
HINT: Use datatype TEXT instead.
WARNING: column type "character varying" used for "resource_cuid" does not follow best practices
HINT: Use datatype TEXT instead.
WARNING: column type "character varying" used for "resourcename" does not follow best practices
HINT: Use datatype TEXT instead.
WARNING: column type "character varying" used for "recordsetid" does not follow best practices
HINT: Use datatype TEXT instead.
NOTICE: migrating data to chunks
DETAIL: Migration might take a while depending on the amount of data.
NOTICE: table "trends" is already a hypertable, skipping
NOTICE: table "trends_uint" is already a hypertable, skipping
NOTICE: TimescaleDB extension is already installed; not changing configuration
NOTICE: TimescaleDB is configured successfully
DO
root@zbxupgdb01:~#
zz0.ol54ppdhjhizz

I restarted zabbix and error message gone.
you can find my upgrade procedure at first message.