CODEWITHSUNDEEP
postgresql-9.3materialized-viewspg-dumppostgresql-10
gilad905
gilad905

Reputation: 3177

PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

(Postgres version: 9.3)

Asking this after searching around without any solution:

psql my_db -c "CREATE MATERIALIZED VIEW my_view AS (SELECT * FROM my_table);"

pg_dump -Fc my_db

psql my_db -c "REFRESH MATERIALIZED VIEW my_view;"

The REFRESH statement hangs (remains 'active') as long as pg_dump is active (this makes things even worse as while the view hangs on REFRESH, I cannot SELECT from it as well). My question is: how can I REFRESH materialized views while performing a dump?

I don't mind excluding the data of the view from the dump if this will help, I don't need to save the data. But I do need to dump the view schema (CREATE statement).

I have set-up a script to exclude all m.views from the dump, but it also excludes their schemas, and it complicates my dump command to an unreasonable amount just to make this work, so I'm looking for a simpler solution.

Thanks in advance!

Upvotes: 3

Views: 1661

Answers (1)

gilad905
gilad905

Reputation: 3177

So, to answer my question :)

I moved to pg_basebackup instead of pg_dump. Of course they're not the same, mostly because the former dumps the entire server data, but for me that works, is much faster anyway, and no problem to refresh m.views while it's running.

Upvotes: 0

Related Questions