Migrate a continuous aggregate to the new form

In Timescale 2.7 and later, continuous aggregates use a new format that improves performance and makes them compatible with more SQL queries. Continuous aggregates created in older versions of TimescaleDB, or created in a new version with the option timescaledb.finalized set to false, use the old format.

To migrate a continuous aggregate from the old format to the new format, you can use this procedure. It automatically copies over your data and policies. You can continue to use the continuous aggregate while the migration is happening.

Connect to your database and run:

CALL cagg_migrate('<CONTINUOUS_AGGREGATE_NAME>');

Copy

Warning

There are known issues with cagg_migrate() in version 2.8.0. Upgrade to version 2.8.1 or later before using it.

The migration procedure provides two boolean configuration parameters, override and drop_old. By default, the name of your new continuous aggregate is the name of your old continuous aggregate, with the suffix _new.

Set override to true to rename your new continuous aggregate with the original name. The old continuous aggregate is renamed with the suffix _old.

To both rename and drop the old continuous aggregate entirely, set both parameters to true. Note that drop_old must be used together with override.

To check the progress of the continuous aggregate migration, query the migration planning table:

SELECT * FROM _timescaledb_catalog.continuous_agg_migrate_plan_step;

Copy

You might get a permissions error when migrating a continuous aggregate from old to new format using cagg_migrate. The user performing the migration must have the following permissions:

• Select, insert, and update permissions on the tables _timescale_catalog.continuous_agg_migrate_plan and _timescale_catalog.continuous_agg_migrate_plan_step
• Usage permissions on the sequence _timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq

To solve the problem, change to a user capable of granting permissions, and grant the following permissions to the user performing the migration:

GRANT SELECT, INSERT, UPDATE ON TABLE _timescaledb_catalog.continuous_agg_migrate_plan TO <USER>;
GRANT SELECT, INSERT, UPDATE ON TABLE _timescaledb_catalog.continuous_agg_migrate_plan_step TO <USER>;
GRANT USAGE ON SEQUENCE _timescaledb_catalog.continuous_agg_migrate_plan_step_step_id_seq TO <USER>;

Copy