Use a user-defined action to schedule regular backfilling

Timescale 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

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


CREATE OR REPLACE PROCEDURE backfill_on_schedule (job_id int, config jsonb)
LANGUAGE PLPGSQL
AS $$
DECLARE
  source_table regclass;
  destination_table regclass;
BEGIN
  SELECT jsonb_object_field_text (
    config, 'staging_table'
  )::text INTO STRICT source_table;
  SELECT jsonb_object_field_text (
    config, 'destination_hypertable'
  )::text INTO STRICT destination_table;

  IF source_table IS NULL THEN
    RAISE EXCEPTION 'Config must provide the source table';
  END IF;

  CALL decompress_backfill(
    staging_table=>source_table,
    destination_hypertable=>destination_table
  );
END
$$;

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.
```
SELECT add_job(
  ‘backfill_on_schedule’,
  ‘10 days’,
  ’{“staging_table”:“backfill_event_data”, “destination_hypertable”:“event_data”}’,
  NOW()
);
```

Use a user-defined action to schedule regular backfilling

Note

Using a user-defined action to schedule regular backfilling

Related Content