(Version 9.3)

Asking this after searching around without any solution:

  • I create a simple materialized view (in practice, I have a few of them).

  • I call pg_dump to backup the database.

  • While pg_dump is running, I try to refresh the view using REFRESH MATERIALIZED 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 also wouldn't want to complicate my dump command to an unreasonable amount just to make this work.

Thanks in advance!

Complete list of commands:

psql my_db -c "CREATE MATERIALIZED VIEW my_view AS (SELECT * FROM my_table);"
pg_dump -Fc my_db
(in a different process, while pg_dump is active:)
psql my_db -c "REFRESH MATERIALIZED VIEW my_view;"

Your Answer

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

Browse other questions tagged or ask your own question.