Use a user-defined action to schedule regular backfilling

TimescaleDB provides a stored procedure for backfilling data into a compressed hypertable. To regularly backfill data, you can schedule this stored procedure to run periodically.

note

This action requires the decompress_backfill function from the TimescaleDB extras repository.

Using a user-defined action to schedule regular backfilling

  1. Create a procedure that calls the decompress_backfill procedure. Use the procedure to backfill data from a source table into your hypertable.

``` 1. CREATE OR REPLACE PROCEDURE backfill_on_schedule (job_id int, config jsonb)

  1. LANGUAGE PLPGSQL

  2. AS $$

  3. DECLARE

  4. source_table regclass;

  5. destination_table regclass;

  6. BEGIN

  7. SELECT jsonb_object_field_text (

  8. config, 'staging_table'

  9. )::text INTO STRICT source_table;

  10. SELECT jsonb_object_field_text (

  11. config, 'destination_hypertable'

  12. )::text INTO STRICT destination_table;

  13. IF source_table IS NULL THEN

  14. RAISE EXCEPTION 'Config must provide the source table';

  15. END IF;

  16. CALL decompress_backfill(

  17. staging_table=>source_table,

  18. destination_hypertable=>destination_table

  19. );

  20. END

  21. $$; ```

  22. Register the job to run every 10 days. Set staging_table to the name of the table that contains your source data. Set destination_hypertable to the hypertable you want to backfill. Start the first run immediately.

``` 1. SELECT add_job(

  1. ‘backfill_on_schedule’,

  2. ‘10 days’,

  3. ’{“staging_table”:“backfill_event_data”, “destination_hypertable”:“event_data”}’,

  4. NOW()

  5. ); ```