Migrate a continuous aggregate to the new form

Starting in TimescaleDB v2.7, continuous aggregates use a newer format that performs better and supports more SQL. Continuous aggregates created before that release, or created with timescaledb.finalized set to false, still use the legacy layout until you migrate.

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.

This procedure applies to TimescaleDB v2.24 and earlier. In v2.25, the old format has been fully removed. See the release notes.

Connect to your database and run:

CALL cagg_migrate('<CONTINUOUS_AGGREGATE_NAME>');

Warning

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

Configure continuous aggregate migration

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.

Check on continuous aggregate migration status

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

SELECT * FROM _timescaledb_catalog.continuous_agg_migrate_plan_step;

Troubleshooting

Permissions error when migrating a continuous aggregate

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>;