I am migrating a database from PostgreSQL 15.5 with TimescaleDB 2.11 to PostgreSQL 17 with TimescaleDB 2.17 using pg_dump and pg_restore.
The database restores successfully, including tables, views, and data.
However, after migration, the previously defined hypertable is no longer recognized as a hypertable in PostgreSQL 17.
The following queries return no results:
SELECT hypertable_name
FROM timescaledb_information.hypertables;
SELECT *
FROM timescaledb_information.chunks
WHERE hypertable_name = ‘my_table’;
I attempted to drop the hypertable, but dependencies (e.g., chunks and views) prevented this. To work around the issue, I performed the following steps:
Created a temporary table and copied data from the original table.
Dropped the hypertable using CASCADE.
Recreated the table
Tried to convert the table into a hypertable using SELECT create_hypertable(‘my_table’, ‘time_column’, …);.
Reinserted the data from the temporary table.
SELECT hypertable_name
FROM timescaledb_information.hypertables; - return my_table
However, the timescaledb_information.chunks query still returns no results for the recreated hypertable.
Questions:
Why is the restored hypertable not recognized in the new setup?
Are there specific steps I missed during the migration to ensure the hypertable is recognized?
How can I confirm the integrity of TimescaleDB metadata post-migration to avoid such issues in the future?
Additional Information:
Views involved:
_timescaledb_internal._direct_view_2, _timescaledb_internal._direct_view_3, etc.
Chunk tables:
_timescaledb_internal._hyper_1_17_chunk, _hyper_1_18_chunk, etc.