Tables lost hypertable properties after migrating, how to re-partition them?

TimescaleDB and PostgreSQL
1

Hey everyone, I have a self hosted deployment of Timescale version pg13.8-ts2.8.0.

In July, I had to do some data migration, re-instate the database and move existing data into it etc. It was a mess, but I managed to do it eventually.

I’ve recently noticed that since then queries on my hypertables aren’t as smooth as they used to be.

My assumption is that in that process my existing hypertables exist in a sort of limbo, where they have old chunks when they were actually hypertables, but no new chunks have been generated since then.

When I select * from timescaledb_information.hypertables I get 0 rows.
When I try to create a hypertable from an existing table table_a (which used to be a hypertable) like so:
SELECT create_hypertable('table_a, 'timestamp', migrate_data => TRUE);
I get the following error message:
SQL Error [0A000]: ERROR: table "table_a" is already partitioned Detail: It is not possible to turn tables that use inheritance into hypertables.

When I manually check the child tables of that table, I see a bunch of chunks, last of which is dated in July.

Adding more context: the newly created hypertables don’t have child tables at all, however, they both dependencies. (I’m checking this using dbeaver)

As I’ve already mentioned, this table, which should already be a hypertable and used to be a hypertable, no longer is one.

One idea I’ve had is to:

  • create a new table_b like table_a
  • make table_b a hypertable
  • move data from table_a to table_b
  • move all the constraints and foreign keys as well (this part I’m trying to avoid)
  • rename table_a to table_a_backup
  • rename table_b to table_a

Is there a more elegant way to go about this? I really dislike my current approach seems to be a bit too risky and cumbersome to preform.

Would appreciate any advice and guidance to handle this situation.

2

Hi @Marco_Rocco ! It seems there’s no easy way if we don’t get exactly what type of metadata was lost. If you don’t have a huge production workload, it would worth to just recreate and reinsert the data.

Fix small things can led to an incomplete scenario as we don’t have a repair mode for hypertables. Do you have any logs or at least know exactly what type of metadata is missing?

3

Hey Jonatas,
thanks for the response.

If there’s a way or documentation for me to check which metadata is missing, I can find the time to do that. For now, I’ve described all the differences that I was able to notice, such as the differences in child tables and dependencies. Other than that, the hypertables had that property removed, as I described above, the hypertables query returns no tables.

Things I could try to do as well: somehow de-partition the tables (not sure how to do this) and then try again to re-create the hypertables. (using just the tsdb create_hypertable command, not re-create them from scratch.

4

Hi Marco, I was checking if any of our timescaledb-extras would cover it but it seems not.

I’d start walking through the sources of timescale catalog kicking off the search/dump from _timescaledb_catalog.hypertable from a hypertable exactly as yours.
Have you tried to go directly into chunks and then rebuild the hypertable from it?