10

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")

  • 3
    You could use copy to create an plain text dump of the data (or any other export utility). Not sure how to restore it though. – a_horse_with_no_name Feb 18 '15 at 7:58
  • You can "pipe" the CSV from COPY to/from a remote server using psql -c "\COPY ...." -h some_IP database though obviously, you have to have created the table structure on the place you are restoring to. – John Powell Oct 3 at 15:00
7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.