PGRES_FATAL_ERROR: invalid INSERT on the root table of hypertable

[mckinnonsc]

Issue Description
I've recently used pg_dump and pg_restore to move my database to a new server and I'm now seeing these errors spammed (over and over) in zabbix_server.log

1808616:20210222:210113.853 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: invalid INSERT on the root table of hypertable "_hyper_7_619_chunk"
HINT: Make sure the TimescaleDB extension has been preloaded.
[insert into trends_uint (itemid,clock,num,value_min,value_avg,value_max) values (214873,1614024000,1,0,0,0),(96373,1614024000,1,0, 0,0);

Versions
Ubuntu Server 20.04
Zabbix Server 5.2.4
Nginx 1.18.0 for the frontend
PostgreSQL 12.6 (Ubuntu 12.6-1.pgdg20.04+1)
Timescaledb extversion is 1.7.4

I ran another pg_dump & pg_restore to a new database, following these instructions and using

SELECT timescaledb_pre_restore();
SELECT timescaledb_post_restore();

but I'm still getting the errors.

Here are my housekeeping settings

[mckinnonsc]

Bump, anybody? I'm really not sure what to do here.

[E2ESystems]

On Zabbix 5.4, have PostgreSQL 12 and TS extension 1.7.5

Didn't do a recent restore of the DB, but noticed the errors in the server.log

 [insert into history (itemid,clock,ns,value) values (36451,1628029702,364664,16.622533000000004),(3644 2,1628029702,364664,83.377466999999996),(38245,162 8029705,372438,86.972707999999997),(38246,16280297 06,370155,52.847599000000002),(36146,1628029706,58 0653,0.204175),(37706,1628029706,732947329,0),(361 47,1628029707,424579,277.89985192569554),(35547,16 28029707,546750,0.0041660000000000004),(40888,1628 029708,297769422,0);
]
23800:20210803:152828.715 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: invalid INSERT on the root table of hypertable "history_uint"
HINT: Make sure the TimescaleDB extension has been preloaded.
[insert into history_uint (itemid,clock,ns,value) values (38247,1628029707,367880,15426048000);
]
23802:20210803:152829.716 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: invalid INSERT on the root table of hypertable "history"
HINT: Make sure the TimescaleDB extension has been preloaded.

Much of my google searches are refer to: The timescaledb-backup tool | Timescale Docs

Wondering if this is correct or not, or if others are seeing this in their Zabbix databases:

select * from information_schema.triggers where trigger_name like 'ts_insert_blocker';
trigger_catalog | trigger_schema | trigger_name | event_manipulation | event_object_catalog | event_object_schema | event_object_table | action_order | action_condition | action_statement | action_orientation | action_timing | action_reference_old_table | action_reference_new_table | action_reference_old_row | action_reference_new_row | created
-----------------+----------------+-------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+---------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------
zabbix | public | ts_insert_blocker | INSERT | zabbix | public | history | 1 | | EXECUTE FUNCTION _timescaledb_internal.insert_blocker() | ROW | BEFORE | | | | |
zabbix | public | ts_insert_blocker | INSERT | zabbix | public | history_log | 1 | | EXECUTE FUNCTION _timescaledb_internal.insert_blocker() | ROW | BEFORE | | | | |
zabbix | public | ts_insert_blocker | INSERT | zabbix | public | history_str | 1 | | EXECUTE FUNCTION _timescaledb_internal.insert_blocker() | ROW | BEFORE | | | | |
zabbix | public | ts_insert_blocker | INSERT | zabbix | public | history_text | 1 | | EXECUTE FUNCTION _timescaledb_internal.insert_blocker() | ROW | BEFORE | | | | |
zabbix | public | ts_insert_blocker | INSERT | zabbix | public | history_uint | 1 | | EXECUTE FUNCTION _timescaledb_internal.insert_blocker() | ROW | BEFORE | | | | |
zabbix | public | ts_insert_blocker | INSERT | zabbix | public | trends | 1 | | EXECUTE FUNCTION _timescaledb_internal.insert_blocker() | ROW | BEFORE | | | | |
zabbix | public | ts_insert_blocker | INSERT | zabbix | public | trends_uint | 1 | | EXECUTE FUNCTION _timescaledb_internal.insert_blocker() | ROW | BEFORE | | | | |
(7 rows)

I could really use some help, much appreciated.

[tfineberg]

Did you come up with a solution to the errors above? I am testing out a backup and restore in a LAB environment and getting the same errors.

[cyber]

https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/#backup-hypertable

Export your data to CSV and import it from CSV, then you get you timescale stuff inserted correctly. At least that's my experience...

[dmcken]

Just in case someone is still looking for this:

https://catonrug.blogspot.com/2022/05/invalid-insert-on-root-table-of.html

Just in case the site goes missing:

CREATE TABLE old_history (LIKE history INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
DROP TABLE history; alter table old_history RENAME TO history;
SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);​

ALTER TABLE history OWNER TO zabbix;​

Rinse, lather repeat across the history*, trends and trends_uint.