12

I have some hard to compute materialized views, which I want to be able to backup and restore using the actual stored data, and not by recomputing.

Is it possible in PostgreSQL 9.4?

(the alternative is to create actual tables, but they are harder to "refresh")

Ophir Yoktan
  • 223
  • 2
  • 6

1 Answers1

9

Not really. You have to remember that pg_dump command creates simple CREATE TABLE and INSERT statements etc. So effectively when running pg_restore you're just running CREATE and INSERT statements on the server and inserting the data would require a "INSERT INTO MATERIALIZED VIEW"-command. That wouldn't make sense as getting the data by a shortcut would also danger the "integrity" of the view - you could insert invalid data on the manual restore process that the view wouldn't return otherwise. So I doubt that PostgreSQL would ever support manual restoring of materialized views data straight into database.

If you need to backup the actual data I would recommend using a table instead of materialized view and running a scheduled DELETE FROM/INSERT INTO statements to the table.

Simo Kivistö
  • 895
  • 1
  • 8
  • 14