Doctorbal/zabbix-postgres-partitioning

Partition maintenance routines

Closed this issue · 3 comments

postilion commented

Thanks for the great write up. Very useful. What's not clear to me, however, is just what sort of ongoing maintenance is required for partitions. For example, do I need to occasionally undertake the steps in Partition Maintenance: Creating Future Partitions or Deleting Old Partitions? Or if I do those steps, will that take care of automating it?

I apologize if these are silly questions, but I haven't done anything with partitions in ages, and have never used pg_partman.

Thanks again,
-nic

Doctorbal commented

Excellent question Nic. And yes I can definitely see how the guide doesn't make it crystal clear that the pg_partman background worker process ensures that maintenance is ongoing. In the following step I mention to create a config file for pg_partman. Within that config file you will see the following pg_partman settings:

enable_partition_pruning = on
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = 'zabbix'
pg_partman_bgw.dbname = 'zabbix'
pg_partman_bgw.analyze = off
pg_partman_bgw.jobmon = on

These are the settings necessary (along with the shared_preload_libraries including pg_partman_bgw being there) that create and delete partitions.
Thus it is automated!
I hope this answers your question.
Regards,
Andreas

  • 👍1
postilion commented

Andreas,
Thanks! Just what I wanted to hear. BTW, I'm interested in your plans for PostgreSQL 12 and Timebase and such, as remarked in the other open issue. Good news!
-nic

Doctorbal commented

@postilion you bet. Hopefully Azure supports version 12 with the TimescaleDB extension soon!