History table postgres DB

[luissuez]

Hello all

I see that my zabbix postgres database backup is about 920Gb... So I check the tables and I see this:

 SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "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;

         Table         |    Size    | External Size
-----------------------+------------+---------------
 history_uint_20200127 | 11 GB      | 5695 MB
 history_uint_20200210 | 11 GB      | 5695 MB
 history_uint_20200203 | 11 GB      | 5695 MB
 history_uint_20200120 | 10 GB      | 5686 MB

So, my question is..Can I just remove this table from the database safely ?
If not, the way I have is to EXCLUDE this tables from my BARMAN backup script?
Let me know
Many thanks

[Hamardaban]

Making a conclusion by the table name, you may have partitioning enabled. If this is exactly the case (make sure to check), then these tables are children and can be deleted by the «drop table history_uint_blablabla» expression .

[luissuez]

Hello Hamardaban
yes, seem to be a partitionned table:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;


 history
 history_20180319
 history_20180326

Triggers:
    history_t01 BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE generic_partition_tp01()
Child tables: history_20180319,
              history_20180326,
              history_20180402,
              history_20180409,
              history_20180416,
              history_20180423,
              history_20180430,
              history_20180507,

So... I can just drop them? Ok I'll try tomorrow
Many thanks !

[Hamardaban]

yes you can.

[luissuez]

Hello Hamardaban

well, this is what happens: I drop using this command:

 drop table history_uint_20190923;

And just after droping, zabbix start to send a lot of alarm mails... All the triggers I receive 350 mails in one minute. So I restart zabbix server, the emails stops and ... the partitions have been created again !!
and the DB size is =

zabbix=# SELECT pg_size_pretty( pg_database_size('zabbix') );
 pg_size_pretty
----------------
 985 GB

Do you ever seen this before?

[Hamardaban]

I've never seen anything like it! My system uses partitioning and nothing like this has ever happened when deleting child tables

[luissuez]

Hello
mmmm yes, I can imamgine.

Just tell me, does this triggers are zabbix default trigger ? I'm pretty sure this is local code, and as I can see, this trigger create partitions automatically, so when you drop one or truncate one, this trigger fill it again...

[Hamardaban]

You can view the entire db schema in the server distribution file schema.sql. There are no specified triggers in it. You need to look at the text of triggers to understand what they do. But in any case, the trigger itself should not generate data! Perhaps you have set up some kind of data replication or applied some solution to ensure data backup / recovery?