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:
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,
drop_old. By default, the name of your new continuous
aggregate is the name of your old continuous aggregate, with the suffix
override to true to rename your new continuous aggregate with the
original name. The old continuous aggregate is renamed with the suffix
To both rename and drop the old continuous aggregate entirely, set both
parameters to true. Note that
drop_old must be used together with
To check the progress of the continuous aggregate migration, query the migration planning table:
SELECT * FROM _timescaledb_catalog.continuous_agg_migrate_plan_step;
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
- Usage permissions on the sequence
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>;
Found an issue on this page?Report an issue!