Timescaledb: No INSERTs after restore from backup

[Dan-RAI]

Relevant system information:

• OS: Ubuntu 16.04
• PostgreSQL version 10.7
• TimescaleDB version 1.2.2

Describe the bug
After restoring from a pg_dump backup I am getting the following error message on INSERTs:

psycopg2.NotSupportedError: invalid INSERT on the root table of hypertable "_hyper_22_3885_chunk"
HINT: Make sure the TimescaleDB extension has been preloaded.

The extension is loaded, the indices and triggers are restored. SELECT queries appear to work.

The above chunk is the last one, and the failed query tries to append new data.

[Dan-RAI]

ok, it seems that all the internal chunks ended up with a ts_insert_blocker trigger after restoring the db.

Manually dropping the triggers seem to solve the problem.

[dianasaur323]

@oOoDKoOo just to make sure this isn't a bug, did you use the pre_restore and post_restore functions when you were restoring? Was the trigger on the hypertable, or on every single chunk?

[Dan-RAI]

Actually no. Was not aware of the need to do that. So likely not a bug, but I would also not call that a feature ...

Is there some other possible adverse effect of not calling these functions besides the trigger insertions, like for example possible loss of data ?

[dianasaur323]

I believe that it protects you from making breaking changes to how we manage hypertables. So it's important to run those flags.

https://docs.timescale.com/latest/using-timescaledb/backup#pg_dump-pg_restore Basically, we block inserts when restoring for a variety of reasons, so you have to allow inserts after you're done restoring.

[Dan-RAI]

So assuming that no other INSERTS besides the import happened, the imported tables should be fine without those flags ?

[dianasaur323]

@oOoDKoOo Yes, you should be okay. I'm a little worried about you removing those triggers though... the triggers protect you from accidentally breaking the root table.

[Dan-RAI]

Not sure that I understand why the tables may get broken after the important is finished and the flags removed. Would appreciate some clarification: During the import it seems a good idea to lock the tables via the flags so that some random independent insertion does not mess up things. But after import is finished without interference, the table structure should be fine, or not ? Or do the _restore functions do something else than just blocking ?

[dianasaur323]

@oOoDKoOo The triggers are there to protect you from updating the root table on accident - this may happen if (for example during an upgrade), you don't complete it fully, and end up inserting into the root table instead of the child tables which represent the hypertable. Thus, it is just safer to keep those triggers in there, since we put them in there to protect you from getting into a bad state. What you did sounds like it's fine, but I wouldn't keep running TimescaleDb without that trigger in there.

[mfreed]

@oOoDKoOo One of the issues is that a user can restore the data from a dump and not actually load the timescaleDB extension -- we have encountered this. In this case, when a user attempts to insert into a hypertable, rather than the TimescaleDB code kicking in and properly routing the tuple to the proper chunk, the data can be inserted into the root table.

[mathiasose]

Leaving a note here for future reference since this was the top result when I was googling for error messages:

We followed an outdated guide to do a pg_restore that didn't mention timescaledb_pre_restore() and timescaledb_post_restore(). This lead to being unable to do some INSERTs on the restored DB. We solved the problem by discarding the restored database and re-restoring from the same dump but following https://docs.timescale.com/latest/using-timescaledb/backup#pg_dump-pg_restore

[MarkusBansky]

Just to push this ticket up in the search. I was moving my database to another docker container and did not know about pre/post restore, so I had to remove the created blocking triggers manually on my tables to make it working again.
I would like to signify that my database was more or less simple compared to others, so this did no harm to me.