-
Notifications
You must be signed in to change notification settings - Fork 916
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Using pg_dumpall cause adding ts_insert_blocker trigger to chunk tables #2304
Comments
We had a same issue, a we were able to remove it by using same solution. But then, when I tried to create new view, this error happens: Does this sounds familiar? Googling for answers yields zero useful results. |
@kodi I didn't face this issue but after moving tables with pg dump I noticed bad performance. I moved all data to newly created table. |
@kgonia can you please try agains our latest TimescaleDB release (2.4.2) and get back to us? |
@NunoFilipeSantos Not really. I don't have environment to replicate. |
That's ok. 👍 Since this affects an old version we're closing the issue. |
I ran into this same error:
The database was just migrated from PG 10 / Timescale 1.7.4 to PG 13 / Timescale 2.4.2 (multiple dumps/restores to get both postgres and timescale upgraded successfully) During the final restore there were 3 errors, although I only have the last one:
The solution was the same: drop the ts_insert_blocker trigger on all the chunks. |
We are facing the same issue on attempting to restore a schema dump generated using
Docker image used: Command used for schema dump: Trigger generated in the schema dump:
Temporarily resolved the issue by manually removing the |
Are you following the procedure outlined in https://docs.timescale.com/timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/#restore-your-entire-database-from-backup |
Hi @prathamesh0 and @treyenelson ! 👋 Did you have a chance to check @svenklemm 's suggestion? |
For those who have already restored the data and don't want to destroy the database and do it all again using It should look like this: DROP TRIGGER ts_insert_blocker ON device_battery;
DROP TRIGGER ts_insert_blocker ON device_temperature;
# ...
SELECT timescaledb_pre_restore();
CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON device_battery FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();
CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON device_temperature FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();
# ...
SELECT timescaledb_post_restore(); |
And if you are running an even newer version change the trigger recreation by this: |
Relevant system information:
Describe the bug
I migrate from PG 11, timescale 1.7.1 to PG 12, timescale 1.7.3
I've done that by running two containers and run:
docker exec postgres-old pg_dumpall -U postgres | docker exec -i postgres-new psql -U postgres
All thing seems to work correctly. Inserts also worked. After few days I got error during inserts:
I solve problem based on this issue and just deleted trigger on chunk tables.
Maybe is worth to look closer on this behavior?
The text was updated successfully, but these errors were encountered: