Closed FANHIDE closed 2 years ago
Same issue here, it was working yesterday and today, everything inserts started to fail.
Same issue with me as well.
What's the recommended way to bootstrap the timescaldb with the hypertables within a docker container?
+1
I started experiencing this as well after performing pg_dump/pg_restore per the instructionms on the site.
CREATE DATABASE tutorial;
\c tutorial --connect to the db where we'll perform the restore
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();
-- execute the restore (or from a shell)
\! pg_restore -Fc -d tutorial tutorial.bak
SELECT timescaledb_post_restore();
Yet no inserts can successfully write to that table. I did get a fair number warnings in both executing the backup and executing the restore. About circular references, and that I should not be using --data-only (even though I was using Fc.
I've got concerns about timescaledb if I can't reliably backup/restore the db.
I started experiencing this as well after performing pg_dump/pg_restore per the instructionms on the site.
CREATE DATABASE tutorial; \c tutorial --connect to the db where we'll perform the restore CREATE EXTENSION IF NOT EXISTS timescaledb; SELECT timescaledb_pre_restore(); -- execute the restore (or from a shell) \! pg_restore -Fc -d tutorial tutorial.bak SELECT timescaledb_post_restore();
Yet no inserts can successfully write to that table. I did get a fair number warnings in both executing the backup and executing the restore. About circular references, and that I should not be using --data-only (even though I was using Fc.
I've got concerns about timescaledb if I can't reliably backup/restore the db.
In my case the reason was that the backup was version 1.7, and restored to 2.0
Same issue here with PG 11 and timescale 1.5.1. Source DB and target DB for backup and restore respectively have the same timescale extension on public schema. Restoring using psql instead of pg_restore as per the instructions here.
Closing this since those are very old versions. Problems with backup/restore should better be reported to the main repo as they will get more visibility there.
This problem stil exists on postgres 14
I encountered the same issue. The solution is https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/ as https://github.com/timescale/timescaledb-docker/issues/86#issuecomment-851769851.
This issue still happens on the current version. I had it yesterday after doing a database migration with Timescale their hypershift tool (which is basically a faster way of dumping and restoring)
Some notes on this:
I made sure that both Postgres and Timescale were running matching versions on the source and target database:
I performed a test run under the exact same conditions, and the issue did not occur
No new data was being inserted during the migration process
Timescale's hypershift tool reported no errors to me:
After I switched, this started happening:
As a result, I had to roll back the production migration and revert to the source database, which was quite painful. While I should probably have checked if inserts were working before fully switching over, I find it concerning that I'm unable to reliably migrate a production database even when utilizing Timescale's own tools. My only solution right now seems to move the data away from Timescale and try again.
@svenklemm can you please re-open this ticket?
I have exactly the same issue as @th0mk
Migrated with hypershift from Timescale Managed to Timescale Cloud
Migration result:
Tables @ Timescale Cloud
and yet I'm getting
ERROR: invalid INSERT on the root table of hypertable "_hyper_1_76_chunk"
HINT: Make sure the TimescaleDB extension has been preloaded.
Chunk is here
Context: PostgreSQL: v15.3 TimescaleDB: v2.11.1
Just leaving here the steps that, apparently, solved my issue
1) psql "postgres://tsdbadmin:<TARGET-PASSWORD>@<TARGET-URL>:<TARGET-PORT>/tsdb?sslmode=require"
2) DROP SCHEMA public CASCADE;
3) CREATE SCHEMA public;
4) psql -X "postgres://tsdbadmin:<TARGET-PASSWORD>@<TARGET-URL>:<TARGET-PORT>/tsdb?sslmode=require"
// Don't forget to add -X
5) CREATE EXTENSION IF NOT EXISTS timescaledb;
6) docker run -v $PWD/config.yml:/config.yml -ti timescale/hypershift:0.6 clone -s='host=<SOURCE-URL> dbname=<SOURCE-DB-NAME> port=<SOURCE-PORT> user=tsdbadmin password=<SOURCE-PASSWORD>' \ -t='host=<TARGET-URL> dbname=tsdb port=<TARGET-PORT> user=tsdbadmin password=<TARGET-PASSWORD>' -c='/config.yml'
YAML config for hypershift:
verify: true
create_db: false
Note: I'm not sure why I had to create the timescaledb
extension. Running the \dx timescaledb
showed that timescaledb v2.11.1 was enabled. It might be a bug in hypershift
Thanks for the extra info. I had already installed the timescaledb
extension in the target database prior to running the hypershift migration.
That's the strange part because I also enabled timescaledb
before migration.
I called CREATE EXTENSION IF NOT EXISTS timescaledb;
and got this:
NOTICE: extension "timescaledb" already exists, skipping
then called \dx timescaledb
and got this
List of installed extensions
Name | Version | Schema | Description
-------------+---------+--------+---------------------------------------------------------------------------------------
timescaledb | 2.11.1 | public | Enables scalable inserts and complex queries for time-series data (Community Edition)
I'm not sure what's going on but something isn't right
psql (12.6 (Ubuntu 12.6-1.pgdg18.04+1), server 12.15 (Ubuntu 12.15-1.pgdg18.04+1)) You are now connected to database "pushnami_stats" as user "postgres". pushnami_stats=# insert into public.hourly_delivered_new (select * from public.hourly_delivered); ERROR: invalid INSERT on the root table of hypertable "_materialized_hypertable_600" HINT: Make sure the TimescaleDB extension has been preloaded. pushnami_stats=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------- pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language timescaledb | 2.10.0 | public | Enables scalable inserts and complex queries for time-series data (3 rows)
I am having this issue also and pgadmin shows no inserts when I have been expecting many inserts. Am getting the following: (psycopg2.errors.FeatureNotSupported) invalid INSERT on the root table of hypertable "_hyper_4_228_chunk" HINT: Make sure the TimescaleDB extension has been preloaded.
Just in case someone needs this. After migrating another DB I started to see the same error. Did some more reading and found this: https://github.com/timescale/timescaledb/issues/1298#issuecomment-505865516
Then checked my log messages again and found this
ERROR: invalid INSERT on the root table of hypertable "_hyper_1_88_chunk"
Then checked the constrains of the _timescaledb_internal._hyper_1_88_chunk
and saw that it had the ts_insert_blocker
, which is something that it's not supposed to have according to the comment above.
Solution: I manually removed the ts_insert_blocker
and things started to work again.
Just faced this, thanks for the notes even on closed issue 🤓
For those who wouldn't know, the query to execute was
DROP TRIGGER ts_insert_blocker ON <name_of_your_table>;
You can run it in rails db
directly
(Remember to replace <name_of_your_table>
with name of the concerned table 😇)
Just in case someone needs this. After migrating another DB I started to see the same error. Did some more reading and found this: timescale/timescaledb#1298 (comment)
Then checked my log messages again and found this
ERROR: invalid INSERT on the root table of hypertable "_hyper_1_88_chunk"
Then checked the constrains of the
_timescaledb_internal._hyper_1_88_chunk
and saw that it had thets_insert_blocker
, which is something that it's not supposed to have according to the comment above.Solution: I manually removed the
ts_insert_blocker
and things started to work again.
Thanks @AndreMaz , that was the issue for me as well !
I use the docker image : timescale/timescaledb:latest-pg10 but when i insert into table.the error is![image](https://user-images.githubusercontent.com/29531967/74219959-36e09400-4ce9-11ea-8c0f-36eefc8753cf.png)