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.

  1. 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
    $$;
  2. 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()
    );

Keywords

Found an issue on this page?

Report an issue!