Documentation on tiered storage does not work

[pavanfhw]

Describe the bug I'm trying to follow this page to automate moving old data to different storage in Prometheus. But it is not working, I define the procedure and set the job, but data is never moved. Although postgres says the jobs ran successfully. My guess is that the procedure gets no chunk in the loop, so it actually does not run anything.

I'm not the most experienced on postgres and sql, so if I say something very wrong I'll count on your patience.

I think it does not work in my case because of the following:

SELECT show.oid AS chunk_oid, pgc.oid AS pgc_oid, pgc.relname, pgc.reltablespace
FROM show_chunks('<table to move data from>', older_than => interval '1 day')
SHOW (oid)
    INNER JOIN pg_class AS pgc ON pgc.oid = show.oid;

-[ RECORD 1 ]-+-----------------------------------------
chunk_oid     | _timescaledb_internal._hyper_9_12_chunk
pgc_oid       | 20784
relname       | _hyper_9_12_chunk
reltablespace | 0

This is the result of lines 19-23 of the procedure (small additions for clarification). In this example, returns 1 chunk I want to move.

The next inner join (line 24) in the procedure is INNER JOIN pg_tablespace pgts ON pgts.oid = pgc.reltablespace But it will never match anything because the reltablespcae of my chunk is 0 and the existing tablespaces have the following oids:

select * from pg_tablespace;
-[ RECORD 1 ]----------
oid        | 1663
spcname    | pg_default
spcowner   | 10
spcacl     | 
spcoptions | 
-[ RECORD 2 ]----------
oid        | 1664
spcname    | pg_global
spcowner   | 10
spcacl     | 
spcoptions | 
-[ RECORD 3 ]----------
oid        | 128564
spcname    | cold
spcowner   | 10
spcacl     | 
spcoptions |

By this information I think the problem is my chunks have a null/invalid tablespace in the pg_class table so the procedure does not work, because the inner join results in nothing. So I have questions like: All tables are in some tablespace right? So how is the reltablespace on pg_class not one of the exsting tablesapces oids? Am I missing some step to be ale to use the move_chunks procedure from the documentation?

To Reproduce Install helm chart timescaledb-single Deploy promscale and prometheus

Expected behavior Chunk to be moved by move_chunks procedure

[pavanfhw]

To resolve this I used this procedure instead of the one in the documentation

CREATE OR REPLACE PROCEDURE move_chunks (job_id int, config jsonb)
LANGUAGE PLPGSQL
AS $$
DECLARE
  ht REGCLASS;
  lag interval;
  destination name;
  destination_oid integer;
  chunk REGCLASS;
  tmp_name name;
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, 'tablespace') INTO STRICT destination;
  SELECT jsonb_object_field_text (config, 'tablespace_oid') INTO STRICT destination_oid;

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

  FOR chunk IN
  SELECT show.oid
  FROM show_chunks(ht, older_than => lag)
  SHOW (oid)
    INNER JOIN pg_class pgc ON pgc.oid = show.oid and pgc.reltablespace != destination_oid
  LOOP
    RAISE NOTICE 'Moving chunk: %', chunk::text;
    EXECUTE format('ALTER TABLE %s SET TABLESPACE %I;', chunk, destination);
  END LOOP;
END
$$;

By passing the target tablespace oid and changing the inner join I could get it working. To add the job now would be something like:

SELECT add_job('move_chunks','1d', config => '{"hypertable":"metrics","lag":"12 month","tablespace":"old_chunks","tablespace_oid":"<oid>"}');

[paulfantom]

This issue seems to not be related to helm charts. Please reach out to timescale support either via slack or email.