Problem restoring database (PostgreSQL, TimeScaleDB, Zabbix 6.0) from backup file

[bilbolodz]

I was trying to recover database PostgreSQL with TimeScaleDB according your instruction:

`systemctl stop zabbix-server.service sudo -u postgres dropdb zabbix sudo -u postgres createdb -O zabbix zabbix

echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix echo "SELECT timescaledb_pre_restore();" | sudo -u postgres psql zabbix

gunzip /var/backup/zabbix_cfg_localhost_20200730-1810_db-psql-5.0.1.sql.gz sudo -u postgres psql zabbix < /var/backup/zabbix_cfg_localhost_20200730-1810_db-psql-5.0.1.sql

echo "SELECT timescaledb_post_restore();" | sudo -u postgres psql zabbix systemctl restart postgresql-12.service systemctl start zabbix-server.service `

but I've got problem: Database was restored successfully but zabbix won't run and complains with error: [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: table "history" is not a hypertable [select set_integer_now_func('history', 'zbx_ts_unix_now', true)] I've managed to all working in these way:

1. dropdb zabbix
2. createdb -O zabbix zabbix
3. CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
4. REMOVE from backup file made by zabbix-backup ALL statements which creates timescaled btriggers "CREATE TRIGGER ts_insert_blocker"
5. psql zabbix < /var/backup/zabbix_cfg_localhost_MODIFIED_file.sql
6. psq zabbix < /usr/share/doc/zabbix-sql-scripts/postgresql/timescaledb.sql (original script to enable TimeScaleDB)

[ironbishop]

Thanks for the feedback.

For clarification: issue title says Zabbix 6, but filename contains 5.0. Which one is the correct Zabbix version?

[bilbolodz]

Filenames in example took from github README file (as example), its issue with version 6.0

[bilbolodz]

How about my issue? Today I'd to restore from backup to new database and confirm that your solution is not working, Backup was made in custom format (-C) so correct recovery procedure is: 1) createdb -O zabbix zabbix 2) createdb -O zabbix zabbix 3) pg_restore backup_file 4) psql zabbix drop trigger ts_insert_blocker on history; drop trigger ts_insert_blocker on history_log; drop trigger ts_insert_blocker on history_str; drop trigger ts_insert_blocker on history_text; drop trigger ts_insert_blocker on history_unit; drop trigger ts_insert_blocker on history_uint; drop trigger ts_insert_blocker on trends; drop trigger ts_insert_blocker on trends_uint; 5) psql zabbix < /usr/share/doc/zabbix-sql-scripts/postgresql/timescaledb.sql (original script to enable TimeScaleDB)

[jokay]

@ironbishop, @bilbolodz any news on this issue, we use the same setup and would like to know if we can still rely on the backup script for disaster recovery?

[bilbolodz]

@jokay I are barking the wrong tree, I'm just a customer as you not author of these script.

[jokay]

@bilbolodz I know but may be you got some news (or experience) about the problem since Sep 22, 2022? :wink:

[bilbolodz]

@jokay sorry no more news

[jokay]

Tried it now myself and everything works until the start of the Zabbix server :wink:

My test setup: Zabbix server 6.0.15, TimeScaleDB 2.10.1 (PostgreSQL 15.2)

2023-04-07 08:25:28.587 CEST [114] ERROR:  invalid INSERT on the root table of hypertable "history_uint"
2023-04-07 08:25:28.587 CEST [114] HINT:  Make sure the TimescaleDB extension has been preloaded.
2023-04-07 08:25:28.587 CEST [114] STATEMENT:  insert into history_uint (itemid,clock,ns,value) values (2838946,1680848728,582759709,2642159916),(2838947,1680848728,582759709,3912239469),(2838955,1680848728,582759709,3);

But this may be related to the still not updated primary keys on my database.

[invisibleninja06]

So in my case this is what actually resolved my errors

Perform the steps as described in the main readme

systemctl stop zabbix-server.service sudo -u postgres dropdb zabbix sudo -u postgres createdb -O zabbix zabbix

echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix

echo "SELECT timescaledb_pre_restore();" | sudo -u postgres psql zabbix

gunzip /var/backup/zabbix_cfg_localhost_20200730-1810_db-psql-5.0.1.sql.gz

sudo -u postgres psql zabbix < /var/backup/zabbix_cfg_localhost_20200730-1810_db-psql-5.0.1.sql

echo "SELECT timescaledb_post_restore();" | sudo -u postgres psql zabbix systemctl restart postgresql-12.service systemctl start zabbix-server.service

Which resulted in the same error as @jokay had, namely

invalid INSERT on the root table of hypertable "history_uint"

Looking at the tables seems like the timescaledb script still needed to be run so i tried but than was met with the an error due to trigger ts_insert_blocker

Used the commands to drop the triggers as @bilbolodz described for my case

drop trigger ts_insert_blocker on history;
drop trigger ts_insert_blocker on history_log;
drop trigger ts_insert_blocker on history_str;
drop trigger ts_insert_blocker on history_text;
drop trigger ts_insert_blocker on history_unit;
drop trigger ts_insert_blocker on history_uint;
drop trigger ts_insert_blocker on trends;
drop trigger ts_insert_blocker on trends_uint;

After this i ran the timescaledb.sql script and voila no more errors.

Since the example does mention its for timescaledb maybe the inclusion of the steps to drop the triggers and run the timescaledb.sql script from zabbix need to be added?