3

(Postgres 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).

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

  • I call pg_dump to backup the database.

pg_dump -Fc my_db

  • While pg_dump is running, I try to refresh the view using REFRESH MATERIALIZED VIEW.

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!

gilad905
  • 2,842
  • 2
  • 16
  • 23

1 Answers1

0

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.

gilad905
  • 2,842
  • 2
  • 16
  • 23