This week I came across an interesting problem. We are hosting several PostgreSQL clusters for our departments, and some databases are using the TimescaleDB extension.
A couple of days before Christmas, an unlucky coworker stumbled over his keyboard and deleted several hundred gigabytes of data from a TimescaleDB hypertable. Thank God for backups!
Since this database is shared between different owners I couldn’t just overwrite it from backup, so I fired up a new VM, restored the database to a known good point in time (pgBackRest is awesome!), exported the affected database and imported it back into the original host.
This took a day or two, but everything seemed good, and the coworker was very relieved that I was able to save Christmas.
Now, after the holiday break, he contacted me again and said that although I restored everything he is missing all the old records from one of the tables. Skeptical and suspecting PEBKAC, I logged into the database and indeed only found records that were clearly inserted after the database was restored. This did not match the fact that the database was using as much diskspace as it was before his mishap.
So I started digging. The suspicious table is a TimescaleDB hypertable, which means that through clever use of PostgreSQL Inheritance and some magic, the TimescaleDB extension puts the actual data of the table into appropriately sized “chunk” tables hidden behind the hypertable.
Now, psql showed that this hypertable had hundreds of child tables, and
sure enough I could query them directly for all the missing data, but
for some mysterious reason (or bug) these children were not “linked”
correctly to their parent hypertable; they were orphaned.
I tried to understand what had happened there, but honestly didn’t get very far. I also explored various directions for an elegant solution to make the data accessible again, but couldn’t find anything - seems that this is not a very common problem.
So finally I decided to simply export the chunk tables directly and re-import them into the hypertable (dropping the old chunk tables afterwards). Raw, but effective.
If it’s stupid, and it works, then is is not stupid.