Update continuous aggregate to hierarchal aggregate

Currently, I have a set of continuous aggregates, which are as below:

5 mins
1 hour
1 day
1 week

I want to migrate them to hierarchical aggregates, such that data in the underlying hypertable is not lost. At present, I see an option to do as below:

  1. Create a hierarchical aggregate with a new name.
  2. Copy data from old hypertable to the new hypertable.
  3. Drop old aggregate.

However, I do see this as a cumbersome process. Is there a better way to handle this?

Hi Amit, we don’t have any tool to migrate it yet. If you end up with any scripts, please, open source it and let us know.
Probably we could build some plpgsql functions to support and help the migrations.

You can also suggest it as a feature to the development team as it would be a great add-on to the extension or could live on timescaledb-extras repo.