Backfill historical data on compressed chunks

1. Temporarily turning off any existing compression policy. This stops the policy from trying to compress chunks that you are currently working on.
2. Decompressing chunks.
3. Performing the insertion or backfill.

note

This section shows you how to bulk backfill data using a temporary table. Temporary tables only exist for the duration of the database session, and then are automatically dropped. If you backfill regularly, you might prefer to use a regular table instead, so that multiple writers can insert into the table at the same time. In this case, after you are done backfilling the data, clean up by truncating your table in preparation for the next backfill.

To make backfilling easier, you can use the backfilling functions in the GitHub repository. In particular, the procedure automates many of the backfilling steps for you.

1. At the psql prompt, create a temporary table with the same schema as the hypertable you want to backfill into. In this example, the table is named example, and the temporary table is named cpu_temp:

3. Call the decompress_backfill procedure. This procedure halts the compression policy, identifies the compressed chunks that the backfilled data corresponds to, decompresses the chunks, inserts data from the backfill table into the main hypertable, and then re-enables the compression policy:

   CALL decompress_backfill(
   );

Backfill manually

If you don’t want to use a supplied function, you can perform the steps manually.

Backfilling manually

1. SELECT alter_job(<job_id>, scheduled => false);

2. Decompress the chunks that you want to modify.

   Repeat for each chunk. Alternatively, you can decompress a set of chunks based on a time range using show_chunks:

       FROM show_chunks('conditions', newer_than, older_than) i;

3. When you have decompressed all the chunks you want to modify, perform the INSERT or UPDATE commands to backfill the data.

4. Alternatively, to recompress chunks immediately, use the run_job command: