Old chunks are not dropped after restore

[1000101]

Relevant system information:

• OS: NixOS
• PostgreSQL version (output of postgres --version): 14.0
• TimescaleDB version (output of \dx in psql): 2.5.0
• Installation method: nix

Describe the bug
Executing SELECT drop_chunks('mytable', INTERVAL '7 days'); on a db recreated from a backup does not drop chunks older than 7 days.

Data (~14 days of chunks) have been exported from pg 12.7, tsdb 2.2.1 using this procedure.

Then restored using this procedure.

To Reproduce
Steps to reproduce the behavior:

1. Export 14d of data from old db (pg12, tsdb 2.2.1)
2. Import 14d data into new db (pg14, tsdb 2.5.0)
3. SELECT drop_chunks('mytable', INTERVAL '7 days');
4. No chunks are being dropped on the new db. However, executing this command on the old db will work properly (chunks are dropped).

Expected behavior

original_db (pg12, tsdb 2.2.1) => SELECT drop_chunks('mytable', INTERVAL '7 days');
               drop_chunks
------------------------------------------
 _timescaledb_internal._hyper_1_520_chunk
 _timescaledb_internal._hyper_1_521_chunk
 _timescaledb_internal._hyper_1_522_chunk
 _timescaledb_internal._hyper_1_523_chunk
 _timescaledb_internal._hyper_1_524_chunk
(5 rows)

new_db (pg14, tsdb 2.5.0) => SELECT drop_chunks('mytable', INTERVAL '7 days');
               drop_chunks
------------------------------------------
 _timescaledb_internal._hyper_1_520_chunk
 _timescaledb_internal._hyper_1_521_chunk
 _timescaledb_internal._hyper_1_522_chunk
 _timescaledb_internal._hyper_1_523_chunk
 _timescaledb_internal._hyper_1_524_chunk
(5 rows)

Additional context

original_db (pg12, tsdb 2.2.1) => SELECT drop_chunks('mytable', INTERVAL '7 days');
               drop_chunks
------------------------------------------
 _timescaledb_internal._hyper_1_520_chunk
 _timescaledb_internal._hyper_1_521_chunk
 _timescaledb_internal._hyper_1_522_chunk
 _timescaledb_internal._hyper_1_523_chunk
 _timescaledb_internal._hyper_1_524_chunk
(5 rows)

new_db (pg14, tsdb 2.5.0) => SELECT drop_chunks('mytable', INTERVAL '7 days');
 drop_chunks 
-------------
(0 rows)

Additional info
However I can see the chunks present in the new db (via autocomplete):

new_db => SELECT * from _timescaledb_internal._hyper_1_
_timescaledb_internal._hyper_1_520_chunk   
_timescaledb_internal._hyper_1_521_chunk   
_timescaledb_internal._hyper_1_522_chunk   
_timescaledb_internal._hyper_1_523_chunk   
_timescaledb_internal._hyper_1_524_chunk   
_timescaledb_internal._hyper_1_525_chunk   
_timescaledb_internal._hyper_1_526_chunk   
_timescaledb_internal._hyper_1_530_chunk   
_timescaledb_internal._hyper_1_541_chunk   
_timescaledb_internal._hyper_1_542_chunk
_timescaledb_internal._hyper_1_543_chunk
_timescaledb_internal._hyper_1_544_chunk

[NunoFilipeSantos]

Hi @1000101! What you need to do is upgrade to TimescaleDB 2.5.0 (the first version where we support PG14) and then upgrade to PostgreSQL 14.

[1000101]

Hi @NunoFilipeSantos, thanks for your quick answer! Could you please clarify what you mean by that?
I've created a clean install with PG14 and TSDB 2.5.0 and just imported the data from the older db (PG12). Everything's working fine except this function.

So the proper way would be first to upgrade the old db with PG12 to TSDB 2.5.0 first and just then backup the data for the import into PG14?

[NunoFilipeSantos]

@1000101 Thank you! 😊
Yes, that is exactly what I mean.

1. upgrade the timescaledb extension from 2.2.1 to 2.5.0
2. upgrade PostgreSQL from 12 to 14

Let us know if this goes as expected.

[1000101]

@1000101 Thank you! blush Yes, that is exactly what I mean.

1. upgrade the timescaledb extension from 2.2.1 to 2.5.0

2. upgrade PostgreSQL from 12 to 14

Let us know if this goes as expected.

Oh right, so I did just that and these chunks don't get identified as old for some reason:

new_db =# SELECT show_chunks('mytable', older_than => INTERVAL '3 days');
 show_chunks 
-------------
(0 rows)

However the oldest entry is from 2021-10-29.

And I've noticed a very strange thing in my testing environment (with no new inserts made to hypertable mytable in the meantime):

 SELECT * FROM mytable ORDER BY time ASC LIMIT 1;
 time | ...
------+ ...
(0 rows)

This was all done in a testing environment.

However the current active DB (with imported data) is working fine with the old data, it's just these chunks that didn't get dropped. Do you think it's safe to use DROP TABLE ... on them?

[1000101]

I've just noticed that I can't even query caggs by bucket older than the time when I did restore. They are present (I can see their chunks SELECT * FROM _timescaledb_internal.) but I can't query them, i.e. SELECT from cagg will return only the newly added data.

[gayyappan]

@1000101 It sounds like the metadata for the hypertables etc. is not set correctly i.e. the db was not restored correctly. Did you set timescaledb.restoring on before restoring the db?

Could you list the exact sequence of steps that you used to dump and restore the db?

[1000101]

@1000101 It sounds like the metadata for the hypertables etc. is not set correctly i.e. the db was not restored correctly. Did you set timescaledb.restoring on before restoring the db?

Could you list the exact sequence of steps that you used to dump and restore the db?

Yes, I was using the sequence as described in docs (except creating the blank db with tsdb extension, script did that):

host1# pg_dump -Fc -f exampledb.bak exampledb
host2# psql -U exampledb
SELECT timescaledb_pre_restore();
\! pg_restore -Fc -d exampledb exampledb.bak
SELECT timescaledb_post_restore();

The commands produced "normal" output.

[nikkhils]

@1000101 will it be possible for you to share the pg_dump output here? We can then try it with PG12.x first and then retrace your steps to upgrading to PG14.x and go ahead from there.

[1000101]

@1000101 will it be possible for you to share the pg_dump output here? We can then try it with PG12.x first and then retrace your steps to upgrading to PG14.x and go ahead from there.

Sorry, the issue was stale for so long, that I discarded the database and just moved on. Unfortunately, it seems I can't downgrade to the tsdb version in question as I've already upgraded to 2.5.0.

But I think this was it, which I disregarded as stated in #2566:

pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump:   hypertable
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump:   chunk
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied

[nikkhils]

@1000101 I am closing this issue for now for lack of reproducible scenarios. Please feel free to open a new one in case you face any other issues in this specific area.