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?

5

Hey Jonatas, apologies for the super late response, had some other things take priority over this, unfortunately.

In my _timescaledb_catalog.hypertable table, there is no entries for the tables which are problematic for me, the only entries are for new test tables which I’ve created to compare the differences between a newly defined hypertable, and the ones which seem to have lost that property.

When you say go directly into chunks are rebuilding the hypertable from it, do you mean use the create hypertable function on a single chunk of a table which isn’t a hypertable?

Thank you so much for the response!

6

I mean get the list of chunks and export the data to a csv or just do some query union data from all chunks like:

CREATE TABLE new_hypertable AS
SELECT * from old_chunk_1
UNION ALL SELECT * from old_chunk_2
-- UNION ALL all other chunks here...

Then select create_hypertable('new_hypertable', migrate_data => true).

7

Yes I was able to do this, however, I was looking for ways to avoid having to re-create the tables.
The main reasons being large amounts of data which would need to be migrated, having to migrate relationships, and requiring downtime.

If there’s no other way (removing existing inheritance on the “corrupted” tables, or somehow de-partitioning them), I’ll go with what you suggested.

Thanks

8

That’s sad Marco. We still don’t have a good way to fix it. You’d need to keep a good ton of union all and other stuff. I’m not familiar enough with the internals to get you do exactly all the steps to rebuild the metadata. Probably it should be possible to reconcile the data but I don’t have expertise enough to share all steps here.

If you come up with any script that could be reusable share it on timescaledb-extras.

Also, let’s not make it die here, if you think more people could have the same, suggest a “repair script” as an issue on the timescaledb repo. Maybe more people have the same issue and it could be upvoted and go to development at some point.

9

Providing some more potentially useful information.
One idea I had was to move all data from child tables(those timescale chunks which exist) to the parent, and then dropping the child tables, perhaps this would mean that the table isn’t partitioned and I would be able to create a hypertable from it.

I copied all data from a child table to the parent, I run a select statement for an ID of a row which was/is in that chunk, I get two rows with identical data (despite the primary key and constraints being duplicates), I drop the child table (expecting to have the data retained in the parent table), however, when I re-run the select statement for that same ID I get no results.