search

timescale / timescaledb-backup

Other
33 stars 9 forks source link

Restore doesn't work for triggers, indexes, hypertable #52

Open jeanlst opened 2 years ago

jeanlst commented 2 years ago

Hi,

I have been using the following to backup and restore my db:

ts-dump --db-URI=postgresql://postgres:pass@localhost/db --dump-dir=/backups/db_backup
psql -U postgres -h localhost -d postgres -c "CREATE DATABASE db;"
ts-restore --db-URI=postgresql://postgres:pass@localhost/db_restore --dump-dir=/backups/db_backup

I have a couple questions regarding the restore.. So does it only work for a newly created instance of postgre? That is the db that I'm trying to restore is empty (no tables, nothing)? Or can I do it on a live DB, that is it has the tables and data inside it and the restore would just drop everything and restore from the backup?

Second thing, I get these warnings when doing the backup. Is that normal? If so, which ones?

2022/02/03 05:00:01 pg_dump: warning: there are circular foreign-key constraints on this table:
2022/02/03 05:00:01 pg_dump:   hypertable
2022/02/03 05:00:01 pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
2022/02/03 05:00:01 pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
2022/02/03 05:00:01 pg_dump: warning: there are circular foreign-key constraints on this table:
2022/02/03 05:00:01 pg_dump:   chunk
2022/02/03 05:00:01 pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
2022/02/03 05:00:01 pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
2022/02/03 05:00:01 pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
2022/02/03 05:00:01 DETAIL:  Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data.
2022/02/03 05:00:01 HINT:  Use "COPY (SELECT * FROM <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.
2022/02/03 05:00:01 pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
2022/02/03 05:00:01 DETAIL:  Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data.
2022/02/03 05:00:01 HINT:  Use "COPY (SELECT * FROM <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.

Finally, I might be wrong but I just tested a restore on a newly created postgre db and it restored all the data just fine and all. However, it did not restore any of the structure of my database except the tables schemas. Tables constraints, indexes, triggers etc where not restored, tbh I'm not even sure if the hypertables were restored. Is there no way around this or did I restore it wrong?