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.
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 PLPGSQLAS $$DECLAREsource_table regclass;destination_table regclass;BEGINSELECT 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 THENRAISE 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. Setdestination_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());
Keywords
Found an issue on this page?
Report an issue!