Postgres + Timescale: Add primary keys to history tables

[einsibjani]

We're running Zabbix 7.0.3 with Postgres 14 and Timescale 2.16. It's been running for a couple of years, with upgrades and migrations along the way.

We never upgraded to using primary keys for the history tables, but I really want to. Our compressed DB is 111 GB so running the scripts, which dump the tables to .csv files, create temp table, import data from csv file and finally INSERT .. SELECT from temp table will take *a long* time.

My question is, is there a reason why the migration script doesn't just create the new table and INSERT .. SELECT from the old table? Why the \copy to and from file?

[einsibjani]

Any reason I can't just run the prepare scipt (renames tables to *_old), create the new tables, start Zabbix so new data flows into the new tables and then backfill from *_old tables into the new tables? Minimum downtime and historical data will eventually be available?

[cyber]

TBH, I don't know why... But docs say you even have to stop whole Zabbix server for that if using PG+TS... Might be something with converting all those hypertables... Probably a trained DBA can explain..

111G is not that long..:P 1.5T took a bit of time..