Drop measurement that belong to a specific retention policy

Question

Recently, we migrated our data from measurement 'users' with retention policy autogen (default) to retention policy sixty_days (sixty_days.users). So we don't need anymore the data in autogen.users.

How can we remove it without harming the data in sixty_days.users?

This is a strange behavior because when we insert data to sixty_days.users - it's not applied on autogen.users, but when we try to delete / drop - it removes it from both of them.

We tried to use DROP, and DELETE by time, but without success.

We expect to remove only data in a measurement that belongs to a specific RT.

score 3 · Answer 1 · answered Jul 14 '19 at 11:47

As Nikolay Manolov mentioned in his answer, you cannot specify retention policy as part of DELETE OR DROP command. But do we really need it?

After tested I confirmed, your issue is related to using same measurement name along with different retention policies. So a better hack may be using a temporary measurement name other than 'user' until you delete the old copy with default retention policy.

select * into "sixty_days".temp_user from "autogen".user group by *;
delete from user;
select * into "sixty_days".user from "sixty_days".temp_user group by *;
delete temp_user;

You need to group by clause as part of above queries to avoid tags converted to fields by default.

An almost classic swap question in interviews :) Thanks! – Aladin Jul 14 '19 at 16:58 — Aladin, Jul 14 '19 at 16:58

Nikolay Hüttenberend · Answer 2 · 2019-07-12T06:16:36.973

This was not supported and it seems it still is not.

It is the intended behavior that DELETE affects all retention policies. And, yes, it is inconsistent for example with a SELECT where you can select from a particular retention policy only. See the docs on this https://docs.influxdata.com/influxdb/v1.7/query_language/database_management/#retention-policy-management

I suppose a not very elegant workaround could be to apply a retention_policy_tag to the data which would then allow you to delete data from particular retention policies.

EDIT: in your case, since the data is already there, you can't apply a tag. You could do a migration via copying the data into a temporary measurement, cleaning up, and then copying the data back. Alternatively, wait 60 days and then delete everything that is older, until which time your data will be duplicated.

I think I'll stick to your advice with the removal of last 60 days. Thanks! — Aladin, Jul 14 '19 at 07:35

Drop measurement that belong to a specific retention policy

2 Answers2