TimescaleDB procedure which moves (AKA archives) chunks to history tablespace

gistfile1.sql

CREATE OR REPLACE PROCEDURE "public"."archive"("job_id" int4, "config" jsonb)
 AS $BODY$
 DECLARE
  ht REGCLASS;
	lag interval;
	destination name;
	index_postfix name;
	chunk_record RECORD;
	result RECORD;
BEGIN
		SELECT jsonb_object_field_text (config, 'hypertable')::regclass INTO STRICT ht;
		SELECT jsonb_object_field_text (config, 'lag')::interval INTO STRICT lag;
		SELECT jsonb_object_field_text (config, 'destination') INTO STRICT destination;
		SELECT jsonb_object_field_text (config, 'index_postfix') INTO STRICT index_postfix;

		IF ht IS NULL OR lag IS NULL OR destination IS NULL OR index_postfix IS NULL THEN
			RAISE EXCEPTION 'Config must have hypertable, lag, destination and index_postfix';
		END IF;

		FOR chunk_record IN SELECT
			pgc.oid,
			relname,
			pgi.indexname,
			pgc.reltablespace,
			pgts.spcname 
	  FROM
			( SELECT split_part( show_chunks ( ht, older_than => lag ) :: TEXT, '.', 2 ) AS chunk ) AS chunks
			INNER JOIN pg_class pgc ON pgc.relname = chunk
			FULL JOIN pg_tablespace pgts ON pgts.oid = pgc.reltablespace
			INNER JOIN pg_indexes pgi ON relname = tablename 
	  WHERE
		  (spcname IS NULL OR spcname != destination) AND indexname LIKE CONCAT('%', index_postfix)
		LOOP
		  RAISE NOTICE 'Moving chunk: %, reorder_index: %', chunk_record.relname, chunk_record.indexname;
			SELECT move_chunk(
				chunk => CONCAT('_timescaledb_internal.', chunk_record.relname),
				destination_tablespace => destination,
				index_destination_tablespace => destination,
				reorder_index => CONCAT('_timescaledb_internal.', chunk_record.indexname),
				verbose => TRUE
			) INTO result;
	  END LOOP;

END $BODY$
  LANGUAGE plpgsql

This is based on an example from https://docs.timescale.com/timescaledb/latest/how-to-guides/user-defined-actions/example-tiered-storage/#tiered-storage that unfortunately didn't work for me. The above uses TimescaleDB move_chunk method instead of the standard ALTER TABLE. Test it with SELECT or add as job:

SELECT add_job('archive','1d', config => '{"hypertable": "data_plc", "lag": "1 month", "destination": "history", "index_postfix": "_time_idx"}');

In this example data_plc has defined index: data_plc_time_idx and each chunk is using the same sufix. You can see if this also fits your case by performing a query:

SELECT
	pgc.oid,
	relname,
	pgi.indexname,
	pgc.reltablespace,
	pgts.spcname 
FROM
	( SELECT split_part( show_chunks ( 'data_plc', older_than => INTERVAL '1 hour' ) :: TEXT, '.', 2 ) AS chunk ) AS chunks
	INNER JOIN pg_class pgc ON pgc.relname = chunk
	FULL JOIN pg_tablespace pgts ON pgts.oid = pgc.reltablespace
	INNER JOIN pg_indexes pgi ON relname = tablename

Note the result can be huge so limit it just in case :)