Hello Community,
I have recently ‘upgraded’ from postgres to timescaleDB (even though its an extension, it feels like an upgrade), and the time has come that I need to work on backup and restore functionality.
Previously, in postgres, I use pg_dump ... as cronjob to periodically backup the database (something simple), and always, I was able to restore it directly using pg_restore .... Even through PGAdmin or DBeaver, it was convenient to create backups and restore them from the UI.
Enter TimescaleDB, PGAdmin or DBeaver backups and restores do not work out-of-the-box. with errors like below during backup with pg_dump --verbose --host=192.0.1.200 --port=5432 --username=postgres --format=c --file ./xyz.sql db_1
pg_dump: NOTICE: hypertable data are in the chunks, no data will be copied
DETAIL: Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data.
HINT: Use "COPY (SELECT * FROM <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.
But I believe chunks are already copied separately as different tables. When restoring, that where it gets complicated.
We can not use --disable-triggers for hypertables as its not supported. and most of the times, it gives me an error / warning like below
pg_restore: error: could not execute query: ERROR: hypertables do not support enabling or disabling triggers.
Command was: ALTER TABLE public.external_data DISABLE TRIGGER ALL;
pg_restore: error: could not execute query: ERROR: hypertables do not support enabling or disabling triggers.
Command was: ALTER TABLE public.external_data ENABLE TRIGGER ALL;
which is fine, as it doesnot effect the restore process. But inside the toc list, the chunks are at the top of the list (my hypertables have foreign keys) and this creates foreignkey not found issues.
My question is, is there any specific method to to restore from a dump file? I am thinking of trying to edit the TOC list to add all the ‘normal’ tables first and then the ‘hypertables’ but I am not 100% sure that it would work. Any suggestions in this regards?
P.s. I checked the Timescale version (2.14.2) and postgres version (15.6) and is always same in both the databases