Delete all data except few basic tables (like users, templates, hosts)

[rycu]

Hello,

I would like to ask if there is a procedure for completely deleting all data from the database, including problems (both acknowledged and unacknowledged), history, trends, and all other data collected by the Zabbix Server. However, I want to retain any data that has been created manually by the administrator (i.e., dashboards, users, templates, hosts). Zabbix should be empty with collected data.

Would using the Housekeeper be an appropriate solution for this (I want to remove that data in short term)?
Alternatively, since I am using PostgreSQL, could TimescaleDB be used for this purpose?

It is important to note that all collected data must be removed. From my understanding, Housekeeper does not delete acknowledged events, but I need to remove those as well.

Could you advise on the best approach to achieve this?

Regards,
r

[cyber]

If you dont need any data, then ignore all the history* tables, trend* tables, event*, problem*, alert... You should test it a bit, what can you leave out and what not...

[rycu]

From my understanding, Housekeeper does not delete acknowledged events, but I need to remove those as well.

I meant that housekeeper won't delete unacknowledged and unresolved problems.

It will be good idea to export hosts, remove all of them and after that import them again? How organize after that operation dashboards, this host will disappear from all of them?


The biggest problem is with the hugest two tables:

table_schema | table_name | total_size | data_size | external_size
-----------------------+--------------------+------------+-----------+---------------
public | event_tag | 129 GB | 96 GB | 33 GB
public | events | 26 GB | 22 GB | 3989 MB​

Did someone handle with that problem?

Regards,
r.

[cyber]

Very big sizes for those... Are you sure you are not missing something that should remove not needed ones. There should be plenty of references to events table...
Referenced by:
TABLE "acknowledges" CONSTRAINT "c_acknowledges_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "alerts" CONSTRAINT "c_alerts_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "alerts" CONSTRAINT "c_alerts_5" FOREIGN KEY (p_eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "event_recovery" CONSTRAINT "c_event_recovery_1" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "event_recovery" CONSTRAINT "c_event_recovery_2" FOREIGN KEY (r_eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "event_recovery" CONSTRAINT "c_event_recovery_3" FOREIGN KEY (c_eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "event_suppress" CONSTRAINT "c_event_suppress_1" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "event_tag" CONSTRAINT "c_event_tag_1" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "problem" CONSTRAINT "c_problem_1" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
TABLE "problem" CONSTRAINT "c_problem_2" FOREIGN KEY (r_eventid) REFERENCES events(eventid) ON DELETE CASCADE

[rycu]

Hello,

thank you for your response.

The size of these two tables is caused by the fact that the rest of them have been chunked by TimescaleDB.

The size of 10 biggest tables look like this:

    
table_schema      |     table_name     | total_size | data_size | external_size
-----------------------+--------------------+------------+-----------+---------------
 public                | event_tag          | 129 GB     | 96 GB     | 33 GB
 public                | events             | 26 GB      | 22 GB     | 3989 MB
 _timescaledb_internal | _hyper_9_798_chunk | 7247 MB    | 4521 MB   | 2727 MB
 public                | event_recovery     | 5929 MB    | 3933 MB   | 1996 MB
 _timescaledb_internal | _hyper_8_783_chunk | 4693 MB    | 3012 MB   | 1681 MB
 public                | alerts             | 1197 MB    | 1144 MB   | 53 MB
 _timescaledb_internal | _hyper_7_751_chunk | 311 MB     | 284 MB    | 27 MB
 public                | items              | 302 MB     | 281 MB    | 20 MB
 _timescaledb_internal | _hyper_7_754_chunk | 204 MB     | 165 MB    | 39 MB
 public                | triggers           | 151 MB     | 136 MB    | 15 MB
(10 rows)

Also, I have to add that, in the GUI, in the housekeeper section, all fields were changed to 1 day.



What options can you suggest? Should we truncate all the tables you mentioned one by one?

Regards,
r.

[cyber]

Whats the query you use for getting the data? I just want to look what it shows for my instance...
Still it seems too big, as events table should stil be cleaned up..

[rycu]

Here you go. The query looks like this:

zabbix=> select schemaname as table_schema,
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
pg_size_pretty(pg_relation_size(relid)) as data_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
pg_relation_size(relid) desc
limit 10;

This database comes from a large Zabbix instance (about 4k NVPS).

[cyber]

I get

     table_schema      |                    table_name                    | total_size | data_size  | external_size
-----------------------+--------------------------------------------------+------------+------------+---------------
 _timescaledb_internal | _hyper_2_9081_chunk                              | 56 GB      | 20 GB      | 36 GB
 _timescaledb_internal | _hyper_2_9111_chunk                              | 56 GB      | 20 GB      | 36 GB
 _timescaledb_internal | _hyper_2_9071_chunk                              | 56 GB      | 20 GB      | 36 GB
 _timescaledb_internal | _hyper_2_9131_chunk                              | 56 GB      | 20 GB      | 36 GB
 _timescaledb_internal | _hyper_2_9121_chunk                              | 56 GB      | 20 GB      | 36 GB
 _timescaledb_internal | _hyper_2_9091_chunk                              | 56 GB      | 20 GB      | 36 GB
 _timescaledb_internal | _hyper_2_9101_chunk                              | 56 GB      | 20 GB      | 36 GB
 _timescaledb_internal | _hyper_7_7171_chunk                              | 54 GB      | 29 GB      | 25 GB

those event related tables in you example

     table_schema      |                    table_name                    | total_size | data_size  | external_size
 public                | events                                           | 2424 MB    | 886 MB     | 1538 MB
 public                | event_tag                                        | 2264 MB    | 1556 MB    | 708 MB
 public                | event_recovery                                   | 365 MB     | 135 MB     | 229 MB​

instance with 7.6k NVPS...

Either you have astronomical amount of events or it is not cleaned up properly...

[tim.mooney]

Wasn't there some bug in an early version of an LTS where something was generating an enormous amount of spurious database entries? I might be thinking of an auditing bug, not an event bug. I don't remember the details, I just remember it was an unpleasant surprise that some sites got for being an early adopter of a particular LTS series.

[rycu]

Thank you for checking that on your site.
I think this is probably caused by the number of unresolved problems and the age of the Zabbix instance (the first installed version was 3.0).

So, what would be the best option to delete all old data?
I want to keep only static data, such as created users, hosts, privileges, dashboards, media types, and all other user-created items, etc.

Should I remove the old data from database or should I export the configuration specific data as I mentioned above from old database and import that into the newest one?
Zabbix should look like empty, without any collected data but with all previously created hosts and other static data.

[cyber]

Config export would be cleanest I think... I really do not know how to query those events that are not referenced any more and could be removed..

[rycu]

Does any procedure exist to easily export that data from Zabbix?
I am worried that dashboards, users, and all assigned privileges, including passwords, might not be easy or even not possible to export (using the GUI or the API).

Maybe it would be a good idea to use the --ignore-table in mysqldump (or --exclude-table in pg_dump) parameter during the export process to another database instance?
But which tables should be excluded in this process? The data from the event or event.tag tables might be linked to other existing tables.

[cyber]

If you dont need any data, then ignore all the history* tables, trend* tables, event*, problem*, alert... You should test it a bit, what can you leave out and what not...

[rycu]

Thank you for your suggestion.
I also think it would be a good idea to delete data from the 'acknowledgments' table.

Firstly I will try with all these tables:

• acknowledges
• alerts
• event_recovery
• event_suppress
• event_symptom
• event_tag
• events
• history
• history_bin
• history_log
• history_str
• history_text
• history_uint
• problem
• problem_tag
• trends
• trends_uint

[rycu]

Hello,
Apologies for the delay in my response — I needed some time to verify how Zabbix would behave after deleting all the tables. I can now confirm that everything is working correctly.

Additionally, I should mention that I had to add the service_problem table as well.

So, these are all the tables I had to purge:
- acknowledges
- alerts
- event_recovery
- event_suppress
- event_symptom
- event_tag
- events
- history
- history_bin
- history_log
- history_str
- history_text
- history_uint
- problem
- problem_tag
- service_problem
- trends
- trends_uint

Thanks for your support and the quick solution.