5

I've been trying out TimescaleDB and I can't understand how backups work with the following system?

timeseries hypertable -> aggregated by a MATERIALIZED VIEW continuous aggregate

Now if I have a data retention policy on the timeseries table that drops anything older than, say, a week. If I make a backup with pg_dump (as recommended by [3]), since it doesn't backup materialized views[1], which is what continuous aggregates are. Then I lose all data from the timeseries hypertable older than a week and the corresponding continuous aggregate data.

Is this the intended behaviour?

References:

[1] pg_dump utility

data retention

[3] backup and restore

foldone
  • 143
  • 9
  • That's also my understanding. I think you would have to export the data from the materialized views that is older than the retention policy to separate tables, and then define a view to join the (truncated) restored aggregate to the older data. IMHO if you really care about the historical data it makes sense to not rely on the mat views only. – glep Dec 16 '21 at 17:55

1 Answers1

2

TimescaleDB Continuous Aggregates are not really materialized views, they are more complicated than this. The data is stored in a hypertable internally, called the materialized hypertable. If you run pg_dump to dump the continuous aggregate, it will write out the contents of the chunks of this hypertable to the dump.

Mats Kindahl
  • 1,863
  • 14
  • 25